Managing RDS User Credentials with Secrets Manager

In this post,we’ll see how we can use Secrets manager to store and manager database user credentials. We’ll also implement fine grained access controls so that users can only access their credentials.

Step 1: Create an Aurora Serverless Postgres 13.16 cluster named database-1. Make sure Enable the RDS Data API is selected and master password is managed by secrets manager.

Once created, connect to the database using Query Editor

and run following commands:

CREATE DATABASE dev1;
CREATE USER dev1 WITH PASSWORD 'abc123#';


CREATE DATABASE dev2;
CREATE USER dev2 WITH PASSWORD 'abc123#';

Now change database and connect to dev1 database

and execute following commands:

GRANT ALL ON SCHEMA public TO dev1;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO dev1; 
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO dev1; 
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO dev1

Now connect to dev2 database and execute following commands:

GRANT ALL ON SCHEMA public TO dev2;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO dev2; 
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO dev2; 
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO dev2

Step 2: Create an IAM role named devs with following Trust relationship policy.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::<AWS_ACCOUNT_ID>:root"
            },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                    "aws:username": [
                        "dev1",
                        "dev2"
                    ]
                }
            }
        }
    ]
}

Grant ReadOnlyAccess permission to this role. Also add following permissions:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "rds-data:ExecuteStatement",
                "rds-data:ExecuteSql",
                "rds-data:BatchExecuteStatement"
            ],
            "Resource": "arn:aws:rds:us-east-1:<AWS_ACCOUNT_ID>:cluster:database-1"
        }
    ]
}

and

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "secretsmanager:GetSecretValue",
            "Resource": "arn:aws:secretsmanager:us-east-1:<AWS_ACCOUNT_ID>:secret:rds-db-credentials/database-1/*"
        }
    ]
}

Step 3: Create an IAM user group named dev with following permission

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "sts:AssumeRole",
            "Resource": "arn:aws:iam::<AWS_ACCOUNT_ID>:role/devs"
        }
    ]
}

Step 4: Now create 2 IAM users named dev1 and dev2 and add them to dev group. Grant them console access.

Step 5: Create 2 secret manager secrets named
rds-db-credentials/database-1/dev1 and rds-db-credentials/database-1/dev2 and store database credentials for dev1 and dev2 users which we created in Step 1.

For rds-db-credentials/database-1/dev1 use following resource policy so that only dev1 IAMuser can access it.

{
  "Version" : "2012-10-17",
  "Statement" : [ {
    "Effect" : "Deny",
    "Principal" : "*",
    "Action" : "secretsmanager:GetSecretValue",
    "Resource" : "<ARN of rds-db-credentials/database-1/dev1 secret>",
    "Condition" : {
      "StringNotEquals" : {
        "aws:userId" : "<DEVS_ROLE_ID>:dev1"
      }
    }
  }, {
    "Effect" : "Allow",
    "Principal" : "*",
    "Action" : "secretsmanager:GetSecretValue",
    "Resource" : "<ARN of rds-db-credentials/database-1/dev1 secret>
  } ]
}

and for rds-db-credentials/database-1/dev2 use following resource policy so that only dev2 IAMuser can access it.

{
  "Version" : "2012-10-17",
  "Statement" : [ {
    "Effect" : "Deny",
    "Principal" : "*",
    "Action" : "secretsmanager:GetSecretValue",
    "Resource" : "<ARN of rds-db-credentials/database-1/dev2 secret>",
    "Condition" : {
      "StringNotEquals" : {
        "aws:userId" : "<DEVS_ROLE_ID>:dev2"
      }
    }
  }, {
    "Effect" : "Allow",
    "Principal" : "*",
    "Action" : "secretsmanager:GetSecretValue",
    "Resource" : "<ARN of rds-db-credentials/database-1/dev2 secret>
  } ]
}

Here <DEVS_ROLE_ID> is the role id of devs IAM role which we created. You can get this by executing

aws iam get-role --role-name devs | grep RoleId

Step 6: Now sign-in to AWS console as dev1 user and switch role to devs. Now go to RDS Query editor and connect to dev1 database using rds-db-credentials/database-1/dev1 secret

Excute a sample query and it should be working fine.

However if dev1 tries to connect using rds-db-credentials/database-1/dev2 secret, it won’t work.