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

[karunateam]

I am unable to grant schema privileges to a custom database role.Attempting to grant privileges to to the default "public" role also does not work.

In PostgresSQL 14+, the default privileges of the "public" schema were modified to just "UPDATE" and the "CREATE" privilege was removed by default.

I am trying to grant the "CREATE" privilege ro the public schema for the 'api' role as follows.

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 = postgresql_role.db_role_api.name schema = "public" object_type = "schema" privileges = ["CREATE", "USAGE"] }

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 executing the postgresql_role 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":

[karunateam]

Replaced with properly formatted ticket