Revoking public schema access not working as expected

[gwkunze]

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

[gwkunze]

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

[gwkunze]

Reading the code, I think there are two problem:

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.

[SharpEdgeMarshall]

This is a big roadblock for us also because we can't revoke PUBLIC from connecting to the DB