Improved postgresql_grant

[els-higginsd]

Terraform Version

v0.11.14

Affected Resource(s)

Please list the resources as a list, for example:

• postgresql_grant

Expected Behavior

Should be able to execute various GRANT statements

Actual Behavior

Only able to execute GRANT on ALL tables or schemas

I have a use-case where I'd like to grant a role the ability to create schemas, but am not able to do so with the current postgresql_grant resource as it is limited to granting permissions to only tables and schemas.

As far as I understand it, I need to run GRANT CREATE ON DATABASE db TO user; but I'm unable to find a way to do this with the current provider.

I think a quick fix to this might be to allow object_type to either be empty, or support a "db|database" value ... then in the postgresql_grant.go the statement would be generated more like this:

// "pseudo" code, as I don't know Golang
object_type := strings.toUpper(d.get("object_type").(string))
if object_type != "DATABASE" && object_type != "" {
  object_type := fmt.Sprintf("ALL %sS IN SCHEMA %s",
    strings.ToUpper(d.Get("object_type").(string)),
    pq.QuoteIdentifier(d.Get("schema").(string))
  )
}
query := fmt.Sprintf(
		"GRANT %s ON %s TO %s",
		strings.Join(privileges, ","),
		object_type,
		pq.QuoteIdentifier(d.Get("role").(string)),
	)

This would then allow us to generate GRANT CREATE ON database TO role; statements, as well as a few more other options most likely.

[cyrilgdn]

Hi @els-higginsd ,

The grant resource supports indeed only table and sequence for now but it was a first step and I plan to add more object types. As you are requesting this one, I'll see how to that (and if your quick fix makes sense, apply it in the code).

I'll let you know here as soon I'll be able to work on it.

[aliasmee]

+1

GRANT CONNECT ON DATABASE YourDatabaseName TO Read_Only_User;

Ref: ansible-postgres-pri

[glerma]

Hi @cyrilgdn ,

The grant resource supports indeed only table and sequence for now but it was a first step and I plan to add more object types. As you are requesting this one, I'll see how to that (and if your quick fix makes sense, apply it in the code).

I'll let you know here as soon I'll be able to work on it.

I would like to see support for granting 'execute' on functions.

[lsowen]

@glerma are there any plans (or interest) in being able to specify grants for specific tables/sequences? For example, RoleA is read-only on all tables in the schema, except it should have INSERT on Table1.

[alephyud]

One more use case for a more generic postgresql_grant related to AWS RDS: it appears that due to rds superuser role limitations, a superuser cannot grant privileges on tables that are owned by other users.

Thus, to enable one user (call it readonly_user) to access tables in a schema that is owned by another user (call it schema_owner) in AWS RDS, we need to run the following:

As superuser:

GRANT USAGE ON SCHEMA schema TO readonly_user;

Then, as schema_owner:

GRANT SELECT ON ALL TABLES IN SCHEMA schema TO readonly_user;

The second statement can be generated by the currently existing postgresql_grant resource, but for the first statement (GRANT USAGE ON SCHEMA) one will need to extend postgresql_grant or create a new resource type.

What do you think about expanding postgresql_grant in the following way?

• make object_type optional; if omitted, the privileges will be granted on schema;
• the possible privileges on schema are CREATE and USAGE.

[robertvanhoesel]

+1 for this

What do you think about expanding postgresql_grant in the following way?

• make object_type optional; if omitted, the privileges will be granted on schema;
• the possible privileges on schema are CREATE and USAGE.

[solarmosaic-kflorence]

Is it possible to revoke the ability for a read-only user to create database tables in the public schema? I tried the following but it didn't work:

resource postgresql_schema public {
  name = "public"
  if_not_exists = true

  # This doesn't seem to work. Read-only user can still create tables in 'public' schema.
  policy {
    create = false
    usage = true
    role = postgresql_role.read_only.name
  }

  policy {
    create = true
    usage = true
    role = postgresql_role.admin.name
  }
}

EDIT: ah, this is because these changes are being applied to the wrong database. See https://github.com/terraform-providers/terraform-provider-postgresql/issues/102

[KursLabIgor]

for me it's really pain in ass for providing read only access for AWS RDS. trying solve it via null_resource and running manual scripts

[mdgreenwald]

@solarmosaic-kflorence Did you get the public schema altered so that a read-only role couldn't create tables in it?

[tgermain]

@cyrilgdn I will try to work on adding support for databases in grants. My use case is narrow for now : being able to grant CREATE on a given database.

(1) Would you advice to keep a single terraform resource for grants (postgresql_grant ) or (2) would it be simpler to split the code in different resource (postgresql_grant_schema, postgresql_grant_database and so on) ?

Having different resources (2) for grant allows to have more specific parameters for some grants. For instance postgresql_grant_schema could only grant CREATE and USAGE privileges.

Keeping a single resource (1) is completely fine, I only want your opinion before going either way.

[cyrilgdn]

@tgermain Many thanks in advance :pray:

I had in mind to have one resource for all types (it already manages tables&sequences), there's already a "allowed privileges per resource map" than can be completed with new types (note that there's a bug with the ALL one so you may encounter it during your tests)

So you can try like that but it may not be that simple with potential corner cases. So if you find out that having only one resource is way too complex, feel free to propose another solution.

[solarmosaic-kflorence]

@mdgreenwald I found out that this is actually default behavior for the public schema in Postgres:

Note that by default, everyone has CREATE and USAGE privileges on the schema public. Ref: https://www.postgresql.org/docs/9.1/ddl-schemas.html

This can be revoked with REVOKE CREATE ON SCHEMA public FROM PUBLIC; -- but I'm not sure how to do that with this provider, so as a workaround you can just create a separate schema to use which gives you full control over the privileges.

[jeromepin]

@cyrilgdn I did some work on #123 based on @tgermain's comment. I hope it will fit with your standards.

[cyrilgdn]

@jeromepin Thanks a lot, I'll take a look as soon as I can :+1:

[dmazhar-cogniance]

Hello, I'm lack the USAGE privilege for SCHEMA object type too :( My use case is creating RO users in AWS RDS, the same as @alephyud

[lcalvarez]

+1 for GRANT USAGE ON SCHEMA

[cyrilgdn]

@dmazhar-cogniance @lcalvarez

We indeed need to implement grant on schema in postgresql_grant but it's currently possible to grant USAGE to roles directly in postgresql_schema with the policy blocks. Something like:

resource "postgresql_schema" "foo" {
  name  = "foo"
  owner = "XXX"

  policy {
    role  = "YYY"
    usage = true
  }
}

Eventually I would like to deprecate this block in favor of postgresql_grant for schema privileges but it can help you meanwhile.

[lcalvarez]

@cyrilgdn yeah that's what I have right now. It works but it makes terraform destroy attempt to destroy the associated schema as well, which is not ideal. I have to do a few manual steps after running destroy and then manually remove tfstate in order to rollback.

[jospint]

Hi @cyrilgdn I am working in a project where we have the requirement of giving grants to roles for specific tables. I have noticed a pull request opened in July where @jmks has worked on making this possible (here). Do you have an ETA on when could it be reviewed? And is there any way that I could help speed this up? Thanks.