Postgresql 16 compatibility (with new roles management)

[hargut]

Affected Resource(s)

• postgresql_database
• postgresql_role

provider version: 1.21.0

The issue is likely related to Postgres 16 Permission/Grant changes.

Terraform Configuration Files

resource "postgresql_role" "dbs" {
  for_each = var.dbs
  name     = each.key
  login    = true
  password = random_password.dbs[each.key].result
}

resource "postgresql_database" "dbs" {
  for_each = var.dbs
  name              = each.key
  owner             = postgresql_role.dbs[each.key].name
  lc_collate        = "en_US.utf8"
  allow_connections = true
}

Expected Behavior

Database should be created.

Actual Behavior

Error: Error creating database "<name>": pq: must be able to SET ROLE "<name>"

Postgres 16 Release and Documentation References

Details related to permission in Postgres 16 Changelog: https://www.postgresql.org/docs/16/release-16.html

Add GRANT to control permission to use SET ROLE (Robert Haas)

This is controlled by a new GRANT ... SET option.

https://www.postgresql.fastware.com/blog/discussing-postgresql-what-changes-in-version-16

The GRANT … SET option was added

The SET option, if set to TRUE, allows the member to change to the granted role using the SET ROLE command. To create an object owned by another role or to give ownership of an existing object to another role, you must have the ability to SET ROLE to that role. Otherwise, commands such as ALTER ... OWNER TO or CREATE DATABASE ... OWNER will fail.

https://www.postgresql.org/docs/current/sql-grant.html Section:

GRANT on Roles

It looks like that this permission is not applied to the db admin user when creating the role. Assume changes would be needed in the postgresql_role to be able to grant this option to the according user or implicitly always grant it to the creating user (provider connection).

Important Factoids

Azure PostgreSQL flexible server version 16

[cyrilgdn]

Hey @hargut ,

Thanks for opening the issue. I'm able to reproduce this problem. Indeed, apparently there's many changes in PG 16 about roles.

It's mainly linked to: https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-CREATEROLE-SELF-GRANT

Even with this option disable (default value), the role will now appear in anyway pg_auth_members that the provider is checking but with all flag, but the admin one (because as mentioned by the doc above Since a CREATEROLE user is always implicitly granted ADMIN OPTION on created roles, disabled)

So as a quick fix we need to adapt the requests that check the members, but these new features will probably imply more changes :scream: (it could simplify a lot some part of the provider though, as managing the case of non-superuser admin like in AWS/Azure/GCP is the biggest pain point in this provider :sweat_smile: )

I allow myself to rename the issue to Postgres v16 compatibility so I can pin it on top.

[chelseadole]

Hey, is there any movement on this issue?

[leonrob]

also interested in this issue

[manneymc]

just ran into this this morning... when i run my terraform with a <v16 Azure Database for PostgreSQL flexible server, i can create a postgresql_role and assign it as the owner of a postgresql_database

if i run it with a v16 database service i get the following terraform error:

postgresql_database.test_db: Creating...

 Error: Error creating database "test": pq: must be able to SET ROLE "testDbo"

[cuongquach]

Hi @cyrilgdn ,

Would be nice if we know some plan about provider postgresql will support Postgresql 16 compatible version ?

Thanks so much.

[maybedino]

We also run into this issue. We use a non-superuser admin (on Azure). As a workaround, until the provider is updated, you can manually set default grant options for new roles:

ALTER ROLE "admin_role" SET createrole_self_grant = 'set, inherit';

I could not find a way to do this with the terraform provider, but you only need to do it once per server / admin user. You can also set this a server parameter, if you have access to that. Azure does not let you do this yes, so the next best thing for us was to set it on the admin user.

admin_role will then automatically be granted permissions to alter roles it has created, and inherit permissions of that role. If you have roles that where aready created, you might have to re-grant them:

GRANT "user_role" TO "admin_role";

But new roles will work fine without this.

See also: http://rhaas.blogspot.com/2023/01/surviving-without-superuser-coming-to.html