Revoking public schema access not working as expected

[gwkunze]

Copied from hashicorp/terraform-provider-postgresql#165

Terraform Version

Terraform v0.12.24
+ provider.postgresql v1.7.0

Affected Resource(s)

Please list the resources as a list, for example:

• postgresql_schema

Terraform Configuration Files

resource "postgresql_database" "database" {
  for_each = var.databases
  name = each.key
  template = "template1"
  lc_collate = "en_US.UTF-8"
  lc_ctype = "en_US.UTF-8"
}

// Don't allow the public role to create in the public schema
resource "postgresql_schema" "public" {
  for_each = var.databases

  database = each.key
  name = "public"
  policy {
    role = "public"
    create = false
    create_with_grant = false
    usage = false
    usage_with_grant = false
  }
}

Expected Behavior

I'd expect the public role not to have the (default) usage and create permissions on the public schema

Actual Behavior

The public schema still allows any user (with the public role) to use and create tables. Note that setting create and usage to true and applying, followed by setting them back to false and applying again does remove the permissions as expected.

Important Factoids

Running on RDS created Postgres 12.3 instance

---

Just tested with both role = "public" and role = "" (since postgres seems to store ACLS for public as "") but the same behaviour is shown for both

---

Reading the code, I think there are two problems:

1. When creating a resource, it is only granting permissions, and the code does not seem to have any functionality related to revoking implicitly granted permissions
2. When refreshing the schema resource, while the code reads and parses the policies, it doesn't actually do anything with them. Meaning Terraform will only compare previously set state to desired state, with the current state being completely ignored.

[icterine]

I can confirm. did not work for me as well

[cyrilgdn]

Hi @gwkunze ,

Thanks for opening this issue, I also discovered this bug recently.
As I mentioned here, schema policies are a bit buggy but we won't fix it as it has been deprecated in v1.11.0 (which has just been released).

To grant/revoke access on schema, you can now use the postgresql_grant resource.
e.g., for this specific use case:

resource "postgresql_grant" "revoke_public" {
  database    = "test_db"
  role        = "public"
  schema      = "public"
  object_type = "schema"
  privileges  = []
}

I close this issue but feel free to answer/open it back if needed.

[firestrtur]

Hi,

The "policy" is Deprecated but it provided for a "*_with_grant" option. I don't see that as a privilege in the postgresql_grant resource, is that option available?

privileges - (Required) The list of privileges to grant. There are different kinds of privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE.

[cyrilgdn]

@firestrtur There's the option but not in the documentation apparently 🤦‍♂️ (I'll add it)

Resource postgresql_grant has a with_grant_option boolean.

resource "postgresql_grant" "revoke_public" {
  database    = "test_db"
  role        = "test_role"
  schema      = "test_schema"
  object_type = "schema"
  privileges  = []

  with_grant_option = true
}

[firestrtur]

That worked. Thanks!