Introduction

Managing database users in Amazon Aurora Serverless v2 for PostgreSQL  involves provisioning and managing user roles effectively, and can e challenging. In this blog, we’ll explore the best practices for setting up and managing users efficiently using Terraform.

Understanding User Management in Aurora Serverless v2

1. Traditional PostgreSQL User Management

  • Creating users and roles in a standard PostgreSQL setup
  • Granting permissions and assigning roles
  • Managing authentication using passwords

Automating User Management with Terraform

2. Terraform-Based User Management Approach

Using a configuration file in Terraform simplifies the process of provisioning and managing users and roles. In our project, we have automated database user management using Terraform with the following steps:

Define users and roles in Terraform local files
Maintain a structured list of users and their corresponding roles.

Create the Aurora Serverless v2 DB cluster
Define the necessary Terraform resources to provision the Aurora database.

Store user credentials in AWS Secrets Manager
Generate and manage secrets for individual users securely.

Create roles and users dynamically
Use stored secrets to create database users and attach appropriate roles.

Implement automatic secret rotation
Deploy an AWS Lambda function to rotate secrets daily and update user passwords in PostgreSQL.

3. Managing Users with AWS Secrets Manager

  • Securely storing database credentials in Secrets Manager
  • Automating user credential retrieval in applications
  • Rotating database credentials securely using AWS Lambda

4. Role-Based Access Control (RBAC) in Aurora Serverless v2

  • Creating different roles (e.g., read-only, read-write, admin)
  • Assigning users to roles dynamically
  • Best practices for access control in production environments

Infrastructure as Code: Managing Users with Terraform

5. Automating User Creation with Terraform

  • Using Terraform to create users and roles
  • Integrating IAM authentication with Terraform
  • Managing Secrets Manager Resources in Terraform

6. Sample Terraform Configuration

Amazon Aurora Serverless v2 PostgreSQL with Terraform

Define Users and Roles in a Terraform Local File

locals {
  # To attach a specific role in the specific environment
  env_roles = {
    dev = { dev_users = "ro", devops_users = "rw", admin_users = "admin" }
    stg = { dev_users = "ro", devops_users = "rw", admin_users = "admin" }
    prd = { dev_users = "ro", devops_users = "ro", admin_users = "admin" }
  }

  # List of application user identities
  app_users = {
    dev_users = [
      "dev_user_a",
      "dev_user_b",
      "dev_user_c",
      "dev_user_d"
    ]
    devops_users = [
      "devops_user_a",
      "devops_user_b"
    ]
    admin_users = [
      "admin_user_a",
      "admin_user_b"
    ]
  }
}

Create Secrets for Individual Users

resource "aws_secretsmanager_secret" "db_user_secrets" {
  for_each    = local.db_users
  name        = "rds-db-credentials/${module.psql_app_serverless_v2_db.this.cluster_resource_id}/${each.key}"
  description = "RDS database ${module.psql_app_serverless_v2_db.this.cluster_id} credentials for ${each.key}"
  kms_key_id  = data.aws_kms_key.secretsmanager.id
}
resource "aws_secretsmanager_secret_version" "db_user_secrets" {
  for_each  = aws_secretsmanager_secret.db_user_secrets
  secret_id = each.value.id
  secret_string = jsonencode({
    engine   = "postgres"
    host     = data.aws_rds_cluster.psql_app_serverless_v2_db.endpoint
    username = each.key
    password = random_password.db_user_secrets[each.key].result
    dbname   = data.aws_rds_cluster.psql_app_serverless_v2_db.database_name
    port     = "${data.aws_rds_cluster.psql_app_serverless_v2_db.port}"
  })
}

Create Users and Assign Roles Based on Secrets

module "create_postgres_user" {
  for_each = {
    for user, user_info in local.sql_users_map :
    user => user_info
    if var.env_name != "localstack"
  }

  source  = "digitickets/cli/aws"
  version = "7.0.0"

  role_session_name = "CreatePostgresUser"
  aws_cli_commands = [
    "rds-data", "execute-statement",
    format("--resource-arn=%s", module.psql_app_serverless_v2_db.this.cluster_arn),
    format("--secret-arn=%s", data.aws_secretsmanager_secret.rds_cluster.arn),
    format("--database=%s", local.rds_database_name),
    format("--sql=\"%s\"", local.sql_create_user[each.key].sql)
  ]
}

Deploy AWS Lambda for Secret Rotation

module "lambda_rotate_db_secret" {
  source = "../modules/lambda"

  function_name      = "lbda-rotate-db-secret"
  description        = "Rotate Aurora Serverless PostgreSQL DB secret"
  handler            = "lambda_function.lambda_handler"
  source_path        = "./lambda_function.py"
  create_package     = true
  package_type       = "Zip"
  runtime            = "python3.9"
  timeout            = 30
  memory_size        = 128
  layers             = [aws_lambda_layer_version.psycopg2_layer.arn]
  create_role        = true
}

Best Practices for Secure User Management

  • Enforce least privilege access
  • Regularly rotate credentials using AWS Secrets Manager
  • Use IAM authentication where possible for enhanced security
  • Automate user creation and permission management using Terraform

Conclusion

Managing users in Aurora Serverless v2 PostgreSQL involves multiple layers, including IAM authentication, role management, Secrets Manager, and automation with Terraform. By following these best practices, and using the right cybersecurity approach, you can ensure a secure and scalable database user management approach.

At PALO IT, we specialize in partnering with clients to implement these kinds of strategies, ensuring seamless integration of AWS services and Terraform in your projects. If you're eager to unlock the full potential of your database management, give us a shout!

Or, if you'd like to learn more, check out our related PALO IT services:
✅ Data Strategy
✅ AI & Gen AI Strategy
✅ Cybersecurity Services

References:

Terraform Scripts:
Ready to kickstart your next big project?
Let's innovate together.
Work with Us