terraform-provider-postgresql: postgresql_grant failing to grant "public" schema privileges with Postgres 14+

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.

Any progress on this?

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"]
}

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  = []
}