Grant permissions to all except one table with postgresql_grant

[rigen-panoai]

I have a use case where I would like to grant SELECT privileges to all of my tables except for one. I looked through the documentation, but could not find a clean way to do this. My hack was to grant privileges to all tables in one postgresql_grant and then in a separate grant, revoke all privileges from my one specific table.

This leads to a few issues with terraform applies, as the inconsistent state of privileges for my table will always be altered on every apply, which causes a resource replacement on every run - not ideal.

If other people have run into this issue, please let me know how you solved it! Much appreciated!

[sheeehy]

Firstly, you would grant SELECT privileges to a user on all tables. Next, you want to ensure that this privilege is granted automatically to any new tables that might be created. For this, you would alter the default privileges in the schema.

You have a table, which we'll call 'sensitive_table', where you do not want users to have the SELECT privilege. For this table, you would revoke the privilege. The SQL command to do this is "REVOKE SELECT ON sensitive_table FROM myuser".

Remember, altering default privileges only affects tables created after the command is issued, not tables that already exist. This is why the first command, to grant privileges to all tables, is necessary.

Also, keep in mind that the REVOKE command only removes privileges granted in the same context. If you've granted privileges directly to a user, you need to revoke it from that user. If you granted privileges to a group and the user is a member of that group, you need to revoke the privileges from the group, not the user.

If you're using Terraform to manage your infrastructure, you would create three different resources for these steps: one for granting privileges to all tables, one for altering default privileges, and one for revoking privileges for the special table. Make sure these resources are executed in the right order by managing their dependencies.

[rigen-panoai]

Hey,

Thank you so much for the detailed response.

Make sure these resources are executed in the right order by managing their dependencies.

What should be the correct order of execution for these 3 resources? I am not having issues achieving my desired configuration, the thing that gives me pause is terraform state. Meaning, even if no code is changed, terraform plan will always enforce a replacement and show changes in state inevitably. An example:

1. I grant SELECT to all tables for my readonly role

resource "postgresql_grant" "readonly_table_permissions" {
  database    = "test"
  role        = "readonly"
  schema      = "public"
  object_type = "table"
  objects     = []
  privileges  = ["SELECT"]

  depends_on = [
    postgresql_role.readonly
  ]
}

2. I set default SELECT privilege for readonly role to all tables

resource "postgresql_default_privileges" "read_only_tables" {
  database = "test"
  role     = "readonly"
  schema   = "public"

  owner       = "postgres"
  object_type = "table"
  privileges  = ["SELECT"]

  depends_on = [
    postgresql_role.readonly,
    postgresql_grant.readonly_table_permissions
  ]
}

3. I revoke sensitive_table access from my readonly role

resource "postgresql_grant" "revoke_account_table_access_from_readonly" {
  database    = "test"
  role        = "readonly"
  schema      = "public"
  object_type = "table"
  objects     = ["sensitive_table"]
  privileges  = []

  depends_on = [
    postgresql_role.readonly,
    postgresql_default_privileges.read_only_tables
  ]
}

Take readonly user's access to sensitive_table through this example: No privileges > SELECT privilege assigned > SELECT revoked. So the end state is no privileges to sensitive_table after terraform has applied these changes. However; even if no code changes are introduced, i.e. I re-run terraform plan immediately after the apply, the resource in #1 above will try to change the state by giving my readonly user SELECT privileges on sensitive_table - therefore always forcing replacement:

Terraform will perform the following actions:
--
  |  
  | # module.database_roles.postgresql_grant.readonly_table_permissions must be replaced
  | -/+ resource "postgresql_grant" "readonly_table_permissions" {
  | ~ id                = "readonly_rd_public_table" -> (known after apply)
  | ~ privileges        = [ # forces replacement
  | + "SELECT",
  | ]
  | # (5 unchanged attributes hidden)
  | }
  |  
  | Plan: 1 to add, 0 to change, 1 to destroy.

Is there a way to avoid forcing replacement on this resource? Something like allow SELECT privilege to all tables except sensitive_table would solve my issue since it will be done in a single resource without ping-ponging the state here.