Manage database users & grants with Terraform

Terraform manages users and privileges declaratively through database providers. Because it tracks state, terraform plan shows exactly which users and grants will change before you apply — and destroying a user is as simple as removing its block.

MySQL / MariaDB

Use the community petoju/mysql provider (the maintained successor to the archived HashiCorp one) with its mysql_user and mysql_grant resources.

terraform {
  required_providers {
    mysql = {
      source  = "petoju/mysql"
      version = "~> 3.0"
    }
  }
}

provider "mysql" {
  endpoint = "127.0.0.1:3306"
  username = "root"
  password = var.mysql_root_password
}

resource "mysql_user" "app" {
  user               = "app_user"
  host               = "%"
  plaintext_password = var.app_password
}

resource "mysql_grant" "app" {
  user       = mysql_user.app.user
  host       = mysql_user.app.host
  database   = "app_db"
  privileges = ["SELECT", "INSERT", "UPDATE", "DELETE"]
}

PostgreSQL

Use the cyrilgdn/postgresql provider with postgresql_role and postgresql_grant.

resource "postgresql_role" "app" {
  name     = "app_user"
  login    = true
  password = var.app_password
}

resource "postgresql_grant" "app" {
  role        = postgresql_role.app.name
  database    = "app_db"
  schema      = "public"
  object_type = "table"
  privileges  = ["SELECT", "INSERT", "UPDATE", "DELETE"]
}

Why Terraform for database users & grants?

Terraform's plan/apply workflow gives you a reviewable diff of every access change, a single source of truth in state, and the same tooling you already use for the rest of your infrastructure. It's especially natural when the database itself is provisioned by Terraform — for example an AWS RDS or Cloud SQL instance — so the instance and its users live in one codebase. Keep passwords in variables sourced from a secrets manager, and remember that secrets end up in Terraform state, so encrypt and lock down your state backend.

Prefer credentials that don't live anywhere? Vault dynamic secrets

Static users are only one option. HashiCorp Vault's database secrets engine generates short-lived, on-demand database users: an app requests credentials, Vault runs your CREATE USER/GRANT statements, hands back a username and password with a TTL, and automatically drops the user when the lease expires. That removes long-lived passwords from config entirely.

Other approaches worth knowing

Flyway / Liquibase — versioned SQL migrations that can include GRANT statements alongside schema changes.
Kubernetes operators — e.g. the Percona, CloudNativePG or Zalando Postgres operators reconcile users from custom resources.
Salt / Bolt — additional configuration-management options with MySQL and PostgreSQL modules.
Cloud IAM database auth — AWS RDS IAM authentication and Cloud SQL IAM let you skip passwords and authenticate with cloud identities.

Generate the raw SQL instead →