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: https://github.com/cyrilgdn/terraform-provider-postgresql/blob/c34742da8ad861b03e27e8aff4ab613990999aa0/postgresql/helpers.go#L125-L128.

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 https://github.com/cyrilgdn/terraform-provider-postgresql/issues/301#issuecomment-1602747469, 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 }

[klaasdellschaft]

Hi @hogolestan I'm just another user of the Terraform provider who shared his workaround ;-) But as I stated in my original post, my scenario was exactly to prevent other database users from creating tables in the public schema of my database. The following snippet achieved that in my case:

resource "postgresql_database" "main" {
  name     = var.name
  owner    = postgresql_role.owner_role.name
  encoding = "UTF8"
}

resource "postgresql_schema" "public" {
  name     = "public"
  database = postgresql_database.main.name
  owner    = postgresql_role.owner_role.name
}

resource "postgresql_grant" "revoke_public" {
  database    = postgresql_database.main.name
  role        = "public"
  schema      = postgresql_schema.public.name
  object_type = "schema"
  privileges  = []
}

[kylejohnson]

I'm making some time this week to look into this issue.

I've ran into this issue a number of times, but can't seem to recreate it at the moment. Can anyone provide me with an example that reproduces the issue? Here's my example which runs just fine:

terraform {
  required_providers {
    docker = {
      source  = "kreuzwerker/docker"
      version = "~> 3.0.2"
    }
    postgresql = {
      source  = "cyrilgdn/postgresql"
      version = ">= 1.20"
    }
  }
}

variable "postgres_image" {
  description = "Which postgres docker image to use."
  default     = "postgres:15"
  type        = string
  sensitive   = false
}

variable "POSTGRES_PASSWORD" {
  description = "Password for docker POSTGRES_USER"
  default     = "postgres"
  type        = string
  sensitive   = false
}

variable "POSTGRES_PORT" {
  description = "Which port postgres should listen on."
  default     = 5432
  type        = number
  sensitive   = false
}

variable "keep_image" {
  description = "If true, then the Docker image won't be deleted on destroy operation. If this is false, it will delete the image from the docker local storage on destroy operation."
  default     = true
  type        = bool
  sensitive   = false
}

provider "docker" {
  host = "unix:///var/run/docker.sock"
}

resource "docker_image" "postgres" {
  name         = var.postgres_image
  keep_locally = var.keep_image
}

resource "docker_container" "postgres" {
  image = docker_image.postgres.image_id
  name  = "postgres"
  wait  = true
  ports {
    internal = var.POSTGRES_PORT
    external = var.POSTGRES_PORT
  }
  env = [
    "POSTGRES_PASSWORD=${var.POSTGRES_PASSWORD}"
  ]
  healthcheck {
    test         = ["CMD-SHELL", "pg_isready"]
    interval     = "5s"
    timeout      = "5s"
    retries      = 5
    start_period = "2s"
  }
}

provider "postgresql" {
  scheme          = "postgres"
  host            = "127.0.0.1"
  port            = docker_container.postgres.ports[0].external
  database        = var.POSTGRES_PASSWORD
  username        = var.POSTGRES_PASSWORD
  password        = var.POSTGRES_PASSWORD
  sslmode         = "disable"
  connect_timeout = 15
  superuser       = false
}

resource "postgresql_role" "db_role_api" {
  name     = "api"
  password = "fubar"
  login    = true
}

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

resource "postgresql_grant" "db_role_api_all_schema_access" {
  database    = postgresql_database.db_api.name
  role        = postgresql_role.db_role_api.name
  schema      = "public"
  object_type = "schema"
  privileges  = ["CREATE", "USAGE"]
}

issues/301 $ terraform apply -auto-approve  
...
Plan: 5 to add, 0 to change, 0 to destroy.
docker_image.postgres: Creating...
docker_image.postgres: Creation complete after 0s [id=sha256:11a95ab93cf5794c4bb89ae2b7269a4663cc6696756aca8a2ce4860105184f96postgres:15]
docker_container.postgres: Creating...
docker_container.postgres: Creation complete after 6s [id=791046cee6eb554265d05a7bfff3ed0da8378bd035b94d28a4de27a1c6f3432f]
postgresql_role.db_role_api: Creating...
postgresql_role.db_role_api: Creation complete after 0s [id=api]
postgresql_database.db_api: Creating...
postgresql_database.db_api: Creation complete after 0s [id=api]
postgresql_grant.db_role_api_all_schema_access: Creating...
postgresql_grant.db_role_api_all_schema_access: Creation complete after 0s [id=api_api_public_schema]

Apply complete! Resources: 5 added, 0 changed, 0 destroyed.
issues/301 $ 

[klaasdellschaft]

Hi @kylejohnson the "secret ingredient" for reproducing the problem are the privileges of the user that is used for connecting to the database. In your case, you are using the postgres user who has the superuser privileges. However, in case of AWS RDS, you are connecting with a non-superuser admin, see https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.MasterAccounts.html for the background. Having said that, I was able to reproduce the problem locally using the following two scripts:

Create the Postgres container + a non-superuser admin

terraform {
  required_providers {
    docker = {
      source  = "kreuzwerker/docker"
      version = "3.0.2"
    }
    postgresql = {
      source  = "cyrilgdn/postgresql"
      version = "1.20"
    }
  }
}

variable "postgres_image" {
  description = "Which postgres docker image to use."
  default     = "postgres:15"
  type        = string
  sensitive   = false
}

variable "POSTGRES_PASSWORD" {
  description = "Password for docker POSTGRES_USER"
  default     = "postgres"
  type        = string
  sensitive   = true
}

variable "POSTGRES_PORT" {
  description = "Which port postgres should listen on."
  default     = 5432
  type        = number
  sensitive   = false
}

variable "ADMIN_USER" {
  description = "Name of the non-superuser admin"
  default     = "admin"
  type        = string
  sensitive   = false
}

variable "ADMIN_PASSWORD" {
  description = "Password for ADMIN_USER"
  default     = "admin"
  type        = string
  sensitive   = true
}

variable "keep_image" {
  description = "If true, then the Docker image won't be deleted on destroy operation. If this is false, it will delete the image from the docker local storage on destroy operation."
  default     = true
  type        = bool
  sensitive   = false
}

provider "docker" {
  host = "unix:///var/run/docker.sock"
}

resource "docker_image" "postgres" {
  name         = var.postgres_image
  keep_locally = var.keep_image
}

resource "docker_container" "postgres" {
  image = docker_image.postgres.image_id
  name  = "postgres"
  wait  = true
  ports {
    internal = var.POSTGRES_PORT
    external = var.POSTGRES_PORT
  }
  env = [
    "POSTGRES_PASSWORD=${var.POSTGRES_PASSWORD}"
  ]
  healthcheck {
    test         = ["CMD-SHELL", "pg_isready"]
    interval     = "5s"
    timeout      = "5s"
    retries      = 5
    start_period = "2s"
  }
}

provider "postgresql" {
  scheme          = "postgres"
  host            = "127.0.0.1"
  port            = docker_container.postgres.ports[0].external
  database        = var.POSTGRES_PASSWORD
  username        = var.POSTGRES_PASSWORD
  password        = var.POSTGRES_PASSWORD
  sslmode         = "disable"
  connect_timeout = 15
  superuser       = false
}

resource "postgresql_role" "admin" {
  name = var.ADMIN_USER
  password = var.ADMIN_PASSWORD
  superuser = false
  inherit = true
  create_database = true
  create_role = true
  login = true
  replication = false
}

Use the non-superuser admin to connect to Postgres + Create the database + Grant / revoke privileges for the public schema in the database

terraform {
  required_providers {
    docker = {
      source  = "kreuzwerker/docker"
      version = "3.0.2"
    }
    postgresql = {
      source  = "cyrilgdn/postgresql"
      version = "1.20"
    }
  }
}

variable "ADMIN_USER" {
  description = "Name of the non-superuser admin"
  default     = "admin"
  type        = string
  sensitive   = false
}

variable "ADMIN_PASSWORD" {
  description = "Password for ADMIN_USER"
  default     = "admin"
  type        = string
  sensitive   = true
}

variable "POSTGRES_HOST" {
  description = "Which host postgres is listening on."
  default     = "127.0.0.1"
  type        = string
  sensitive   = false
}

variable "POSTGRES_PORT" {
  description = "Which port postgres is listening on."
  default     = 5432
  type        = number
  sensitive   = false
}

provider "postgresql" {
  host            = var.POSTGRES_HOST
  port            = var.POSTGRES_PORT
  username        = var.ADMIN_USER
  password        = var.ADMIN_PASSWORD
  sslmode         = "disable"
  connect_timeout = 15
  superuser       = false
}

resource "postgresql_role" "db_role_api" {
  name     = "api"
  password = "fubar"
  login    = true
}

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

resource "postgresql_grant" "db_role_api_all_schema_access" {
  database    = postgresql_database.db_api.name
  role        = postgresql_role.db_role_api.name
  schema      = "public"
  object_type = "schema"
  privileges  = ["CREATE", "USAGE"]
}

[dstockstad]

Any progress on this?