Manage AWS RDS PostgreSQL roles and schema with Atlas, Terraform, and IAM authentication

This guide shows how to use Atlas and Terraform to manage both roles and application schema on AWS RDS PostgreSQL. It starts by bootstrapping a dedicated migration user in one Terraform state, then uses that user with IAM authentication to manage application schema from a separate state.

The RDS instance, networking, and Secrets Manager setup are intentionally brief. Most teams already manage those pieces in their own infrastructure. For a complete working layout, use the reference repository: https://github.com/ariga/atlas-showcase/tree/master/projects/tf-role

Architecture

Use two Terraform states, or at least two independently applied Terraform roots:

State	Atlas env	Credentials	Owns
Roles	roles	RDS master credentials	atlas_migrator, application roles, RDS built-in roles, schema-level grants
Schema	schema	IAM token for atlas_migrator	Application schema objects and permissions on those objects

This split keeps privileged bootstrap work separate from day-to-day schema changes. The roles state uses master credentials only to create the migration user and grant access. The schema state uses the migration user and an IAM token for normal Atlas runs.

Do not combine role and schema HCL in the same atlas_schema resource. If the schema state sees role definitions, Atlas may plan role SQL while connected as atlas_migrator, and that user should not have permission to create roles.

Prerequisites

Start with an existing RDS PostgreSQL instance that has:

• IAM database authentication enabled.
• Network access from the machine or CI runner that runs Terraform.
• Master credentials available to the roles state, commonly through AWS Secrets Manager as JSON with username and password fields.
• An AWS IAM user or role with rds-db:connect for dbuser:<db-resource-id>/atlas_migrator.

For PostgreSQL, use sslmode=require when connecting with an IAM token.

Configure Atlas environments

Use one atlas.hcl with two environments. The roles environment uses master credentials from Secrets Manager. The schema environment uses data "aws_rds_token".

atlas.hcl

variable "endpoint" {
  type = string
}

variable "aws_profile" {
  type    = string
  default = "tf-sandbox"
}

variable "database_name" {
  type = string
}

variable "region" {
  type    = string
  default = "us-east-1"
}

variable "master_secret_name" {
  type = string
}

variable "migrator_username" {
  type    = string
  default = "atlas_migrator"
}

data "runtimevar" "master_secret" {
  url = "awssecretsmanager://${var.master_secret_name}?region=${var.region}&awssdk=v2&profile=${var.aws_profile}"
}

data "aws_rds_token" "migrator" {
  region   = var.region
  endpoint = var.endpoint
  profile  = var.aws_profile
  username = var.migrator_username
}

env "roles" {
  url = "postgres://${jsondecode(data.runtimevar.master_secret).username}:${urlescape(jsondecode(data.runtimevar.master_secret).password)}@${var.endpoint}/${var.database_name}?sslmode=require"
  schema {
    mode {
      roles       = true
      permissions = true
      tables      = false
      views       = false
      funcs       = false
      types       = false
      objects     = false
      triggers    = false
    }
  }
}

env "schema" {
  url = "postgres://${var.migrator_username}:${urlescape(data.aws_rds_token.migrator)}@${var.endpoint}/${var.database_name}?sslmode=require"
  schema {
    mode {
      permissions = true
    }
  }
}

The roles mode disables table and object inspection so that the roles state does not try to own application schema. The schema mode enables permission inspection so object-level grants can live next to the objects they reference.

This example intentionally does not configure dev_url. The provider may warn that dev_url is unset; that warning is expected for this flow.

Define the roles state

PostgreSQL has predefined pg_* roles, such as pg_read_all_data and pg_signal_backend. Atlas does not manage those roles, so they do not need to be declared in HCL. See the PostgreSQL predefined roles documentation for the full list. If you want a refresher on Atlas's role, user, and member_of model before adapting it to RDS-specific built-in roles, see Defining Roles and Users in the PostgreSQL security declarative guide.

Cloud-managed databases add their own roles. On RDS PostgreSQL, roles such as rds_iam, rds_superuser, and rds_password already exist. Mark these roles as external = true so Atlas knows they are pre-existing roles. Atlas can then generate membership changes, such as GRANT "rds_iam" TO "atlas_migrator", without trying to create the RDS-managed roles.

Inspect the existing roles before writing the HCL. Run this from a psql client that can reach the RDS instance, such as your workstation, a bastion host, or AWS CloudShell when it has network access to the database:

psql "host=mydb.xxxx.us-east-1.rds.amazonaws.com port=5432 dbname=appdb user=postgres sslmode=require" \
  -c "select rolname from pg_roles;"

Output:

           rolname
-----------------------------
 pg_execute_server_program
 ...
 pg_create_subscription
 pg_signal_autovacuum_worker
 rds_superuser
 rds_replication
 rds_iam
 rds_password
 rds_ad
 rds_reserved
 rds_extension
 postgres
 rdsadmin
(25 rows)

You do not need to declare the predefined pg_* roles. Declare the RDS-managed roles and existing database users that appear in the roles state as external.

hcl/roles/roles.pg.hcl

role "rds_ad" {
  external = true
}

role "rds_extension" {
  external = true
}

role "rds_iam" {
  external = true
}

role "rds_password" {
  external = true
}

role "rds_replication" {
  external = true
}

role "rds_reserved" {
  external = true
}

role "rds_superuser" {
  external = true
}

user "rdsadmin" {
  external = true
}

user "postgres" {
  external = true
}

user "atlas_migrator" {
  member_of = [role.rds_iam]
}

user "app_reader" {}

If your RDS master user is not named postgres, replace that block with your master user name. app_reader is an example application role used later for a table grant.

Add schema privileges next. This can live in the same HCL file, but a separate file keeps role declarations and grants easier to scan:

hcl/roles/grants.pg.hcl

schema "public" {}

permission {
  to         = user.atlas_migrator
  for        = schema.public
  privileges = [CREATE, USAGE]
}

permission {
  to         = PUBLIC
  for        = schema.public
  privileges = [USAGE]
}

The PUBLIC permission preserves PostgreSQL's default grant on the public schema. The same pattern applies to other default privileges. For example, when PostgreSQL creates a function, it automatically grants EXECUTE to PUBLIC. If that privilege is not declared in HCL, Atlas may generate a REVOKE after the CREATE statement.

Wire the files to one atlas_schema resource:

terraform/roles/variables.tf

variable "endpoint" { type = string }
variable "aws_profile" { type = string }
variable "database_name" { type = string }
variable "region" { type = string }
variable "master_secret_name" { type = string }

terraform/roles/main.tf

terraform {
  required_providers {
    atlas = {
      source  = "ariga/atlas"
      version = "= 0.10.3"
    }
  }
}

provider "atlas" {}

locals {
  role_files = sort(tolist(fileset("${path.module}/../../hcl/roles", "*.pg.hcl")))
  desired_hcl = join("\n\n", [
    for file_name in local.role_files : file("${path.module}/../../hcl/roles/${file_name}")
  ])
}

resource "atlas_schema" "roles" {
  env_name = "roles"
  config   = file("${path.module}/../../atlas.hcl")
  variables = jsonencode({
    endpoint           = var.endpoint
    aws_profile        = var.aws_profile
    database_name      = var.database_name
    region             = var.region
    master_secret_name = var.master_secret_name
    migrator_username  = "atlas_migrator"
  })
  hcl = local.desired_hcl
}

Apply the roles state

Run the commands from the root of this Terraform layout.

Initialize the roles root:

terraform -chdir=terraform/roles init 

Output:

Initializing provider plugins found in the configuration...
- terraform.io/builtin/terraform is built in to Terraform
- Using previously-installed ariga/atlas v0.10.3

Terraform has been successfully initialized!

Plan the roles change:

terraform -chdir=terraform/roles plan  -out=tfplan

Output:

Terraform will perform the following actions:

  # atlas_schema.roles will be created
  + resource "atlas_schema" "roles" {
      + env_name = "roles"
      ...
    }

Plan: 1 to add, 0 to change, 0 to destroy.

Warning: dev_url is unset

Warning: Atlas Plan

The following SQL statements will be executed:

CREATE ROLE "atlas_migrator" WITH LOGIN;
CREATE ROLE "app_reader" WITH LOGIN;
GRANT "rds_iam" TO "atlas_migrator";
GRANT CREATE, USAGE ON SCHEMA "public" TO "atlas_migrator";

Apply the roles change:

terraform -chdir=terraform/roles apply  tfplan

Output:

atlas_schema.roles: Creating...
atlas_schema.roles: Creation complete after 12s [id=942de62c-2cc2-c3f8-db8f-b924b3b98fac]

Warning: Atlas Plan

The following SQL statements will be executed:

CREATE ROLE "atlas_migrator" WITH LOGIN;
CREATE ROLE "app_reader" WITH LOGIN;
GRANT "rds_iam" TO "atlas_migrator";
GRANT CREATE, USAGE ON SCHEMA "public" TO "atlas_migrator";

Apply complete! Resources: 1 added, 0 changed, 0 destroyed.

Run a second plan to check convergence:

terraform -chdir=terraform/roles plan  -out=tfplan

Output:

atlas_schema.roles: Refreshing state... [id=942de62c-2cc2-c3f8-db8f-b924b3b98fac]

No changes. Your infrastructure matches the configuration.

Define the schema state

The schema state contains schema objects and permissions on those objects. Put all application schema files under a schema-only directory.

hcl/schema/schema.pg.hcl

schema "public" {}

permission {
  to         = PUBLIC
  for        = schema.public
  privileges = [USAGE]
}

table "validation_probe" {
  schema = schema.public

  column "id" {
    null = false
    type = integer
  }

  column "created_at" {
    null    = false
    type    = timestamptz
    default = sql("now()")
  }

  primary_key {
    columns = [column.id]
  }
}

permission {
  to         = "app_reader"
  for        = table.validation_probe
  privileges = [SELECT]
}

app_reader is managed by the roles state, not the schema state. Use the role name as a string when a schema object needs a grant to that role. This lets the schema state generate the table grant without loading role HCL or planning role DDL.

The PUBLIC schema permission preserves PostgreSQL's default grant on the public schema while permission inspection is enabled in the schema env.

Wire the schema files to a separate atlas_schema resource:

terraform/schema/variables.tf

variable "endpoint" { type = string }
variable "aws_profile" { type = string }
variable "database_name" { type = string }
variable "region" { type = string }
variable "master_secret_name" { type = string }

terraform/schema/main.tf

terraform {
  required_providers {
    atlas = {
      source  = "ariga/atlas"
      version = "= 0.10.3"
    }
  }
}

provider "atlas" {}

locals {
  schema_files = sort(tolist(fileset("${path.module}/../../hcl/schema", "*.pg.hcl")))
  desired_hcl = join("\n\n", [
    for file_name in local.schema_files : file("${path.module}/../../hcl/schema/${file_name}")
  ])
}

resource "atlas_schema" "app" {
  env_name = "schema"
  config   = file("${path.module}/../../atlas.hcl")
  variables = jsonencode({
    endpoint           = var.endpoint
    aws_profile        = var.aws_profile
    database_name      = var.database_name
    region             = var.region
    master_secret_name = var.master_secret_name
    migrator_username  = "atlas_migrator"
  })
  hcl = local.desired_hcl
}

If the schema root reads outputs from the roles root, add an explicit dependency so Terraform does not plan schema before the role exists.

Apply the schema state

Run these commands from the same root.

Initialize the schema root:

terraform -chdir=terraform/schema init 

Output:

Initializing provider plugins found in the configuration...
- terraform.io/builtin/terraform is built in to Terraform
- Using previously-installed ariga/atlas v0.10.3

Terraform has been successfully initialized!

Plan the schema change:

terraform -chdir=terraform/schema plan  -out=tfplan

Output:

Terraform will perform the following actions:

  # atlas_schema.app will be created
  + resource "atlas_schema" "app" {
      + env_name = "schema"
      ...
    }

Plan: 1 to add, 0 to change, 0 to destroy.

Warning: Atlas Plan

The following SQL statements will be executed:

CREATE TABLE "public"."validation_probe" (
  "id" integer NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY ("id")
);
GRANT SELECT ON TABLE "public"."validation_probe" TO "app_reader";

Apply the schema change:

terraform -chdir=terraform/schema apply  tfplan

Output:

atlas_schema.app: Creating...
atlas_schema.app: Creation complete after 17s [id=f15fc6c2-6edd-6852-ab14-5ae70cf66f05]

Warning: Atlas Plan

The following SQL statements will be executed:

CREATE TABLE "public"."validation_probe" (
  "id" integer NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY ("id")
);
GRANT SELECT ON TABLE "public"."validation_probe" TO "app_reader";

Apply complete! Resources: 1 added, 0 changed, 0 destroyed.

Run a second schema plan:

terraform -chdir=terraform/schema plan  -out=tfplan

Output:

atlas_schema.app: Refreshing state... [id=f15fc6c2-6edd-6852-ab14-5ae70cf66f05]

No changes. Your infrastructure matches the configuration.

References

• Atlas secrets and IAM authentication
• AWS RDS IAM database authentication
• Creating a PostgreSQL database account for IAM authentication
• Creating an IAM policy for IAM database access
• PostgreSQL predefined roles