postgresql_grant failing to grant "public" schema privileges with Postgres 14+

[karunateam]

Terraform Version

v1.4.5

Affected Resource(s)

postgresql_grant

Terraform Configuration Files

provider "postgresql" {
  alias     = "postgres_user"
  host      = var.db.forwarded_host
  port      = var.db.forwarded_port
  username  = 'postgres'
  password  = var.db.master_password
  sslmode   = "require"
  superuser = false
}

resource "postgresql_database" "db_api" {
  provider = postgresql.postgres_user
  name     = "api"
  owner    = postgresql_role.db_role_api.name
  template = "template1"
}

resource "postgresql_role" "db_role_api" {
  provider = postgresql.postgres_user
  name     = "api"
  password = aws_secretsmanager_secret_version.db_password.secret_string
  login    = true
}

resource "postgresql_grant" "db_role_api_all_schema_access" {
  provider    = postgresql.postgres_user
  database    = postgresql_database.db_api.name
  role        = "api"
  schema      = "public"
  object_type = "schema"
  privileges  = ["CREATE", "USAGE"]
}

Expected Behavior

Role 'api' should be granted CREATE and USAGE permissions for the 'public' schema in the 'api' database.

Actual Behavior

When terraform reaches the resource postgresql_grant.db_role_api_all_schema_access, it fails with the error below. Note that Postgres added a new role called pg_database_owner and this appears to be the role that is responsible for creating the postgresql_grant resource.

module.api.postgresql_grant.db_role_api_all_schema_access: Creating...
    Error: Error granting role pg_database_owner to postgres: pq: role "pg_database_owner" cannot have explicit members
    with module.api.postgresql_grant.db_role_api_all_schema_access,
    on api/main.tf line 95, in resource "postgresql_grant" "db_role_api_all_schema_access":

Steps to Reproduce

1. terraform apply

Important Factoids

Attempting to grant permissions to the 'public' role also fails with the same issue above.

In PostgresSQL 14+, the default privileges of the "public" schema for newly created databases were modified to just "UPDATE". With PostgresSQL 13-, the 'public' schema was granted both "UPDATE' and "CREATE" privileges by default.

In PostgreSQL 14+, a new role called pg_database_owner was introduced and this appears to be the role that is responsible for executing the postgresql_role resource above.

References

[seanamos]

This is related to thepg_database_owner role added in Postgres 15+ that has default ownership of the public schema.

What seems to actually be happening is this provider queries the owner of the public schema (pg_database_owner) and tries to temporarily assign that role to whatever role the provider is running as:

terraform-provider-postgresql/postgresql/helpers.go

Lines 125 to 128 in c34742d

	// withRolesGranted temporarily grants, if needed, the roles specified to connected user
	// (i.e.: the admin configure in the provider) and revoke them as soon as the
	// callback func has finished.
	func withRolesGranted(txn *sql.Tx, roles []string, fn func() error) error {

.

pg_database_owner is a "magic" role that refers to the owner of the current database, so it cannot have any members. When the provider tries to assign pg_database_owner to the current role (eg. postgres), this error is thrown.

[klaasdellschaft]

I came across the same problem in my application (instead of granting privileges on the public schema, I wanted to revoke them). Given the example from the issue description, the following workaround was successful in my case:

resource "postgresql_schema" "public" {
  name     = "public"
  database = postgresql_database.db_api.name
  owner    = postgresql_role.db_role_api.name
}

resource "postgresql_grant" "db_role_api_all_schema_access" {
  database    = postgresql_database.db_api.name
  schema      = postgresql_schema.public.name
  ...
}

This replaces the special pg_database_owner role for the public schema with the actual owner role that you also assigned to the database in which the public schema is located.

[seanamos]

@klaasdellschaft That was one of the workarounds I was considering. It does work, however if you try to update ownership of multiple postgresql_schema in parallel, you will run into the "tuple concurrently updated" error.

I do think pg_database_owner should be handled as a special case within the provider.

The concurrent update issue is obviously a different problem that is like playing whack a mole in resolving.

[klaasdellschaft]

@klaasdellschaft That was one of the workarounds I was considering. It does work, however if you try to update ownership of multiple postgresql_schema in parallel, you will run into the "tuple concurrently updated" error.

I do think pg_database_owner should be handled as a special case within the provider.

The concurrent update issue is obviously a different problem that is like playing whack a mole in resolving.

Yes, I agree that pg_database_owner should be recognized by the Terraform provider. I only consider the snippet from above as a workaround, if you do not have the luxury to wait for the final fix of this issue ;-)

[HarshaPradeep]

I am facing the same issue and blocked by this. Any plans to fix this?

[karunateam]

My solution for this was to adhere to the new default security privileges for the public schema. Rather than granting privileges on 'public', I migrated all my tables to my own schema and configured my desired privileges on that schema. I understand some projects may not have this flexibility but I was unsuccessful getting terraform to work with the public schema. Andy

…

On Thu, Jun 22, 2023 at 10:32 AM Harsha Pradeep Rathnayaka < ***@***.***> wrote: I am facing the same issue and blocked by this. Any plans to fix this? — Reply to this email directly, view it on GitHub <#301 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AMDHAMFU26ZFNNAUKUSNZATXMRJO3ANCNFSM6AAAAAAXH4WIUI> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[joshsouza]

I was able to leverage the code in my linked PR: #316 to adjust the outstanding drift my team is experiencing due to this. That said, I'm not sure if there's more complexity to the issue that will need to be addressed. Until we have an answer on what the right path forward is, you might want to take a glance at what I did. It's not tremendously hard to build and run this version of the provider to get around immediate blockages.

[mattthaber]

Running into this issue as well, and is a pretty major blocker now that we upgrading to PG15+

[figaro-smartotum]

I am blocked too...!

[mhubig]

I run into the same problem here, took me some hours the find this issue!

[dstockstad]

Also ran into this. Would be great to get this fixed ASAP. I'm sure this is blocking loads of people now that Postgres 15 is fully out and available on major platforms such as AWS.

[luizbossoi]

same issue here

[hogolestan]

I came across the same problem in my application (instead of granting privileges on the public schema, I wanted to revoke them). Given the example from the issue description, the following workaround was successful in my case:

resource "postgresql_schema" "public" {
  name     = "public"
  database = postgresql_database.db_api.name
  owner    = postgresql_role.db_role_api.name
}

resource "postgresql_grant" "db_role_api_all_schema_access" {
  database    = postgresql_database.db_api.name
  schema      = postgresql_schema.public.name
  ...
}

This replaces the special pg_database_owner role for the public schema with the actual owner role that you also assigned to the database in which the public schema is located.

@klaasdellschaft I've just changed the owner with master user and generally it worked (as expected) but public owner didn't changed and individual users can create their objects into their databases and public schema as default schema, TF provider is 1.20 and db 15.3

resource "postgresql_schema" "public" {
name = "public"
database = postgresql_database.db_api.name
owner = masteruser
}