resource postgresql_grant not working on postgresql v15

[luismacosta]

Hi there,

Thank you for opening an issue. Please provide the following information:

Terraform Version

Terraform v1.4.0 on darwin_amd64

• provider registry.terraform.io/cyrilgdn/postgresql v1.19.0

Affected Resource(s)

postgresql_grant

Terraform Configuration Files

resource "postgresql_grant" "su_schema" {
  database    = test
  role        = test
  schema      = public
  object_type = "schema"
  privileges = [
    "CREATE",
    "USAGE",
  ]
}

Actual Behavior

│ Error: Error granting role pg_database_owner to masteruser: pq: role "pg_database_owner" cannot have explicit members │ │ with module.postgresql_roles.postgresql_grant.su_schema, │ on .terraform/modules/postgresql_roles/postgresql/role/roles.su.tf line 5, in resource "postgresql_grant" "su_schema": │ 5: resource "postgresql_grant" "su_schema" {

Steps to Reproduce

1. db version 15.2
2. terraform apply

[kylejohnson]

I'm also seeing this issue.

• provider registry.terraform.io/cyrilgdn/postgresql v1.19.0
• terraform v1.4.0

[matthewh]

I'm also seeing this issue.

• provider registry.terraform.io/cyrilgdn/postgresql v1.18.0
• terraform v1.4.0

[cyrilgdn]

Hi, Thanks you for opening this issue.

Do you have any other element to mention? I've just tested your simple example but it does not trigger this error.

Are you on a cloud managed database?

[luismacosta]

Hi,

Yes, I'm using AWS RDS postgresql 15.2 With any postgresql_grant resource, I'm getting this error

[selbstereg]

Hi, also having this issue.

Terraform v1.3.7 cyrilgdn/postgresql 1.19.0 Postgres instance created with aws provider 3.65.0. Resource: aws_db_instance (engine=postgres, engine_version=15.2.)

[matthewh]

Hello @cyrilgdn,

I'm also using AWS RDS Postgres 15.2.

[sandangel]

I have this error instead, but same situation which is after upgrading to postgres 15.2

Error: error detecting capabilities: error PostgreSQL version: pq: permission denied for database

[sandangel]

I think it's more like an issue with postgres 15: https://www.postgresql.org/docs/15/ddl-schemas.html#DDL-SCHEMAS-PATTERNS

[sandangel]

We found the fix. After connecting to Aurora RDS with postgres user, you will need to grant all permissions on your database to postgres. Somehow the permissions were removed after migration. So if you are using postgres user to config this provider, it might not work.

GRANT ALL PRIVILEGES ON DATABASE <your-database> TO postgres;

[mfournier]

There's indeed a hint in https://www.postgresql.org/about/news/postgresql-15-released-2526/ : PostgreSQL 15 also revokes the CREATE permission from all users except a database owner from the public (or default) schema.

So nothing specific to RDS or the terraform provider.

[luismacosta]

Hi @mfournier

Indeed, thanks. Looking into v15 release notes, there are some changes related to schema public. I've created a new schema using resource postgresql_schema After that, changed resource postgresql_grant to use the new schema and all resources were successfully created. Hint.

[dmoll1974]

Hi @luismacosta, thanks for providing a solution. Would it be possible to show a short example on how you roll your own schema? When we apply our own schema we get a new class of problems, but we are not very familiar with the postgres permission system. More specific, when we apply successfully a schema we cannot use it in a postgres_grant. We get this error:

could not find schema 'perfana' while looking for owner

While the schema does exist:

postgres=> SELECT rolname
  FROM pg_namespace
  JOIN pg_roles ON nspowner = pg_roles.oid
  WHERE nspname = 'perfana';
    rolname    
---------------
 rds_superuser
(1 row)

It would be much appreciated if you could provide a short example.

[luismacosta]

Hi @dmoll1974

db_name = schema_name = test owner is the role that will be created with privileges defined using postgres_grant

locals {
  schemas = [
    {
      name    = test
      owner   = "su-test-test"
      db_name = test
    },
  ]
} 

resource "postgresql_schema" "test" {
  for_each = {
    for schema in local.schemas :
    schema.name => schema
  }

  name     = each.value.name
  database = each.value.db_name
  owner    = each.value.owner
}

[dmoll1974]

@luismacosta Thanks that worked for us!