# IAM access to PostgreSQL databases Set up IAM database authentication for a Golden Path PostgreSQL database, so applications and developers connect with short-lived IAM tokens instead of passwords. Golden Path database clusters enable IAM database authentication by default (`iam_database_authentication_enabled = true` in the [`terraform-aws-modules/rds-aurora` module](https://registry.terraform.io/modules/terraform-aws-modules/rds-aurora/aws/latest){:target="_blank"}). [The AWS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.html){:target="_blank"} explains the benefits and limitations of IAM database authentication. For background on the database choices, read [the RFC for RDBMS in the Golden Path](https://github.com/oslokommune/golden-path-iac/blob/main/rfc/0001-Database/0001-Database.md){:target="_blank"}. !!! info "Prerequisites" - A PostgreSQL database created with the Golden Path database template. - A working `psql` connection to the database through the `ok forward` tunnel. Set this up by following [Connect to a database from your computer](connect-to-a-database-from-your-computer.md). - The AWS CLI authenticated against the AWS account of your environment. ## Step 1: Find the master credentials The database template stores the cluster endpoint and master username in AWS Parameter Store. The cluster and its parameters follow the naming pattern `-main`: ```bash ENVIRONMENT=my-team-dev aws ssm get-parameter \ --name "/$ENVIRONMENT/database/$ENVIRONMENT-main/db_endpoint" \ --query Parameter.Value \ --output text aws ssm get-parameter \ --name "/$ENVIRONMENT/database/$ENVIRONMENT-main/db_username" \ --query Parameter.Value \ --output text ``` The master username is `root`. AWS manages the master password (`manage_master_user_password = true` in the database template) and stores it in AWS Secrets Manager, not in Parameter Store: ```bash SECRET_ARN=$(aws rds describe-db-clusters \ --db-cluster-identifier "$ENVIRONMENT-main" \ --query "DBClusters[0].MasterUserSecret.SecretArn" \ --output text) aws secretsmanager get-secret-value \ --secret-id "$SECRET_ARN" \ --query SecretString \ --output text | jq -r .password ``` ## Step 2: Create the database user This is a manual, one-time step per database. Connect to the database as `root` through the `ok forward` tunnel, using the credentials from step 1. Create a user and grant it the special `rds_iam` role. Replace `mydbuser` with a name that reflects the intended usage: ```sql CREATE USER mydbuser WITH LOGIN; GRANT rds_iam TO mydbuser; ``` Then grant the user the access it needs. Run one or more of the following statements, or any other grants based on your requirements. Allow the user to access objects in a schema (this doesn't grant access to the tables themselves): ```sql GRANT USAGE ON SCHEMA schema_name TO mydbuser; ``` Allow read access to one table: ```sql GRANT SELECT ON table_name TO mydbuser; ``` Allow read access to all tables in a schema: ```sql GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO mydbuser; ``` !!! tip "Keep the statements in your IaC repository" Store these SQL statements in a file in your IaC repository, for example `environments/dev/databases/users.sql`. The database setup stays documented and repeatable, even though you run the statements manually. ## Step 3: Allow an IAM role to connect The IAM identity that connects to the database needs the `rds-db:connect` permission for the database user. Create the permission with Terraform in your application's stack. Don't create IAM resources manually with the AWS CLI or the AWS console, since the Golden Path manages all infrastructure as code. The example below lets the application's ECS task role connect as `mydbuser`. The locals `environment`, `region`, `account_id`, and `main_container_name` already exist in application stacks generated by the Golden Path templates: ```terraform title="repo-iac/environments/dev/app-my-app/iam_rds_connect.tf" locals { db_user = "mydbuser" } data "aws_rds_cluster" "main" { cluster_identifier = "${local.environment}-main" } data "aws_iam_policy_document" "rds_connect" { statement { effect = "Allow" actions = ["rds-db:connect"] resources = [ "arn:aws:rds-db:${local.region}:${local.account_id}:dbuser:${data.aws_rds_cluster.main.cluster_resource_id}/${local.db_user}" ] } } resource "aws_iam_policy" "rds_connect" { name = "${local.environment}-${local.main_container_name}-rds-connect" description = "Allow rds-db:connect as DB user '${local.db_user}' on cluster '${data.aws_rds_cluster.main.cluster_identifier}'" policy = data.aws_iam_policy_document.rds_connect.json } resource "aws_iam_role_policy_attachment" "rds_connect" { role = module.ecs_service.tasks_iam_role_name policy_arn = aws_iam_policy.rds_connect.arn } ``` The `aws_rds_cluster` data source looks up the cluster resource ID that the `rds-db:connect` ARN requires, so you don't need to find it manually. Apply the configuration: ```bash title="repo-iac/environments/dev/app-my-app/" terraform init terraform apply ``` !!! info "Connecting as a developer" To connect as the database user yourself, the IAM role of your AWS session needs the same `rds-db:connect` permission. Broad roles such as `AdministratorAccess` already include it. ## Step 4: Verify the connection Verify that IAM authentication works by connecting through the `ok forward` tunnel with a generated token instead of a password. 1. Download the certificate bundle for your region: ```bash curl -o eu-west-1-bundle.pem https://truststore.pki.rds.amazonaws.com/eu-west-1/eu-west-1-bundle.pem ``` !!! info "Certificate authority and region" Golden Path clusters pin the `rds-ca-rsa2048-g1` certificate authority (`ca_cert_identifier` in the database template), which the regional bundle includes. If your environment runs in another region, replace `eu-west-1` in the URL and in the commands below. 1. Start `ok forward` as described in [Connect to a database from your computer](connect-to-a-database-from-your-computer.md). Note the database endpoint you select and the local port you choose. 1. Generate a token and connect. Set `RDSHOST` to the endpoint you selected in `ok forward`, and `LOCAL_PORT` to the local port you chose: ```bash RDSHOST=my-team-dev-main-one.abc123xyz.eu-west-1.rds.amazonaws.com LOCAL_PORT=4812 export PGPASSWORD="$(aws rds generate-db-auth-token \ --hostname "$RDSHOST" \ --port 5432 \ --region eu-west-1 \ --username mydbuser)" psql "host=$RDSHOST hostaddr=127.0.0.1 port=$LOCAL_PORT user=mydbuser dbname=postgres sslmode=verify-full sslrootcert=eu-west-1-bundle.pem" # (1)! ``` 1. The `host` and `hostaddr` pair makes `psql` connect to the tunnel at `127.0.0.1` while validating the server certificate against the real endpoint name, so `sslmode=verify-full` works. Generate the token for the database's real endpoint and port `5432`, not for `localhost` or the local port. The token is a signed request tied to that endpoint, and the database rejects tokens generated for other hostnames. Tokens expire after 15 minutes. You should get a `psql` prompt. Confirm that you are connected as the new user: ```sql SELECT current_user; ``` !!! question "Why does certificate validation fail with `host=localhost`?" The server certificate is issued for the database endpoint, not for `localhost`. With `host=localhost` and `sslmode=verify-full`, the hostname check fails. Use the `host` and `hostaddr` pair shown above, or fall back to `sslmode=verify-ca`, which validates the certificate chain but skips the hostname check. !!! question "Why does `psql` report that password authentication failed?" - The token has expired. Tokens are valid for 15 minutes, so generate a new one. - The token was generated for the wrong hostname, port, or region. Use the endpoint you selected in `ok forward`, port `5432`, and the region of your environment. - The IAM identity lacks the `rds-db:connect` permission for this database user, or the database user is missing the `rds_iam` grant.