Terraform modules for PostgreSQL RBAC: roles, grants, and users

Published: 2026-02-25

PostgreSQL role management is one of those things that starts as a few manual GRANT statements and ends up as an undocumented mess that nobody dares touch. Terraform with the cyrilgdn/postgresql provider turns it into versioned, reviewable, idempotent infrastructure code.

Why Terraform for database access

The problem with manual grants:

• No audit trail of who added what
• Can't diff "desired vs current state"
• Copy-paste errors between environments
• Off-boarding is guesswork

With Terraform: every user, every role, every grant is in Git. Access changes go through code review. terraform plan shows exactly what will change before applying.

Provider setup

hclcopy# providers.tf
terraform {
  required_providers {
    postgresql = {
      source  = "cyrilgdn/postgresql"
      version = "1.22.0"
    }
  }
  required_version = ">= 0.13"
}

provider "postgresql" {
  host            = var.db_host
  port            = var.db_port
  database        = var.db_database
  username        = var.db_admin_user
  password        = var.db_admin_pass
  sslmode         = var.db_sslmode
  connect_timeout = var.db_connect_timeout
}

Credentials come from a terraform.tfvars file that lives outside the repo, or from environment variables (TF_VAR_db_admin_pass). Never commit passwords.

The admin user needs SUPERUSER or at least CREATEROLE + CREATEDB privileges.

Module: db_role

A reusable module that creates a group role and attaches privileges to it:

hclcopy# modules/db_role/variables.tf
variable "role_name" {}
variable "database" {}
variable "schema" { default = "public" }
variable "privileges" { type = list(string) }
variable "schema_owner" { default = "postgres" }

hclcopy# modules/db_role/main.tf

resource "postgresql_role" "group" {
  name  = var.role_name
  login = false   # group roles don't log in directly
}

resource "postgresql_grant" "connect" {
  database    = var.database
  role        = postgresql_role.group.name
  object_type = "database"
  privileges  = ["CONNECT"]
}

resource "postgresql_grant" "schema_usage" {
  database    = var.database
  role        = postgresql_role.group.name
  schema      = var.schema
  object_type = "schema"
  privileges  = ["USAGE"]
}

resource "postgresql_grant" "tables" {
  database    = var.database
  role        = postgresql_role.group.name
  schema      = var.schema
  object_type = "table"
  objects     = []   # empty = all existing tables
  privileges  = var.privileges
}

resource "postgresql_default_privileges" "future_tables" {
  database    = var.database
  role        = postgresql_role.group.name
  schema      = var.schema
  object_type = "table"
  privileges  = var.privileges
  owner       = var.schema_owner
}

postgresql_grant with objects = [] grants on all existing tables. postgresql_default_privileges covers all future tables created under that schema. Both are needed — forgetting default_privileges is the most common bug.

Without default_privileges, users lose access to newly created tables and developers spend hours debugging "permission denied" errors.

Module: db_user

hclcopy# modules/db_user/variables.tf
variable "username" {}
variable "password" { sensitive = true }
variable "member_of" { type = list(string) }

hclcopy# modules/db_user/main.tf

resource "postgresql_role" "user" {
  name     = var.username
  password = var.password
  login    = true
}

resource "postgresql_grant_role" "membership" {
  for_each   = toset(var.member_of)
  role       = each.value
  grant_role = postgresql_role.user.name
}

Putting it together

hclcopy# main.tf

module "role_rw" {
  source     = "./modules/db_role"
  role_name  = "rw"
  database   = "postgres"
  privileges = ["SELECT", "INSERT", "UPDATE", "DELETE"]
}

module "role_ro" {
  source     = "./modules/db_role"
  role_name  = "ro"
  database   = "postgres"
  privileges = ["SELECT"]
}

module "alice" {
  source    = "./modules/db_user"
  username  = "alice"
  password  = var.alice_password
  member_of = ["rw"]
}

module "bob" {
  source    = "./modules/db_user"
  username  = "bob"
  password  = var.bob_password
  member_of = ["ro"]
}

Adding a new team member is a three-line change: add a module block, set member_of to the appropriate role, add a password variable. Reviewable PR, auditable history.

Role design

Keep roles simple:

Assign DDL rights only to the migration user, never to the application user.

Backend: remote state

For a team setup, store state in an S3-compatible backend:

hclcopy# backend.tf
terraform {
  backend "s3" {
    bucket                      = "terraform-state"
    key                         = "postgresql/terraform.tfstate"
    endpoint                    = "https://storage.yandexcloud.net"
    region                      = "ru-central1"
    skip_credentials_validation = true
    skip_requesting_account_id  = true
    skip_metadata_api_check     = true
    force_path_style            = true
  }
}

Two engineers running terraform apply simultaneously will corrupt the state without locking. Use a state backend that supports locking.

Passwords from Vault

Instead of storing passwords in tfvars, pull them from Vault using the Vault provider:

hclcopydata "vault_generic_secret" "db_users" {
  path = "secret/infra/postgresql"
}

module "alice" {
  source    = "./modules/db_user"
  username  = "alice"
  password  = data.vault_generic_secret.db_users.data["alice_password"]
  member_of = ["rw"]
}

This way no password ever appears in the terraform state file (which is plaintext JSON).

CI integration

yamlcopy# .gitlab-ci.yml
terraform-plan:
  image: hashicorp/terraform:1.8
  script:
    - terraform init
    - terraform plan -out=tfplan
  artifacts:
    paths: [tfplan]

terraform-apply:
  image: hashicorp/terraform:1.8
  script:
    - terraform init
    - terraform apply tfplan
  when: manual
  needs: [terraform-plan]

Plan runs in MR. Apply is manual after review. Database access changes should always go through a human gate.

Troubleshooting

bashcopy# Check current grants in PostgreSQL
psql -U postgres -c "\dp orders.*"

# List role memberships
psql -U postgres -c "\du"

# Check default privileges
psql -U postgres -c "\ddp"

# Import existing user into Terraform state (avoid re-create)
terraform import module.alice.postgresql_role.user alice

Common issue: if you add a table manually without Terraform, the postgresql_grant resource won't automatically grant access to it. Run terraform apply to sync grants, or enable postgresql_default_privileges to handle future tables automatically.