Managing Amazon RDS databases in multi-account environments with the AWS CLI

AWS provides AWS organizations and AWS Landing Zone solutions to manage and automate new account creation. This helps you to create multiple AWS accounts separated based on applications, development, production, or organizations within a company. This approach helps with resource isolation and separating development from production, but makes it complex for some of the teams that support the whole organization. For example, a database operations team helps manage Amazon Relational Database Service (Amazon RDS) databases in your AWS accounts. Supporting AWS resources in multiple AWS accounts can increase your management activities because each AWS account can have its own AWS Identity and Access Management (IAM) users, groups, and roles. Sometimes teams like database support write common scripts to query information about RDS databases or check the status or configuration of database resources in all AWS accounts from a centralized location. Multiple users across AWS accounts also can pose a challenge because more users can result in maintaining more AWS access keys. Furthermore, it’s important that you protect users.

Solution Overview

One way of reducing the number of credentials to manage is to use temporary AWS security credentials. You can do this by using AWS Security Token Service (AWS STS) and IAM roles. To use an IAM role, you have to make an API call to STS:AssumeRole, which returns a temporary access key ID, secret key, and security token that can then be used to sign future API calls. Formerly, secure cross-account, role-based access from the AWS Command Line Interface (CLI) required an explicit call to STS:AssumeRole, and used your long-term credentials. The resulting temporary credentials were captured and stored in your profile, and that profile was used for subsequent AWS API calls. This process had to be repeated when the temporary credentials expired (after 1 hour, by default).

Currently, even though the actual chain of API calls is still necessary, the AWS API automates this workflow for you. With a simple setup, you can achieve secure cross-account access with the AWS CLI by simply adding different profiles to the AWS CLI config file or even setting up additional environment variables to your session. Database management or bastion host uses credential_source as Ec2InstanceMetadata to avoid each user storing AWS credentials.

In this post, we show how to use an IAM role with the AWS CLI to access all your AWS accounts from a DB management or bastion host without using AWS account credentials. We also provide tips to automate the RDS database management with AWS CLI configurations and settings for all database administrators.

Prerequisites

Before you get started, make sure you have the following prerequisites:

• You need at least two AWS accounts to simulate the scenario:
  • Assume 11111111111 is one of the application accounts where we have Amazon RDS running.
  • Assume 99999999999 is the management account where we have a bastion or DB management host (EC2).
• Optionally, you can configure Active Directory authentication for users to access the DB management host. SSH login to either host also works.
• Install the AWS CLI utility on the DB management host (you can use AWS CLI version 2 and refer to specific instructions for your operating system).
• For this post, we use the AWS CLI to configure IAM roles, polices, and profiles. You need appropriate IAM permissions in your AWS accounts to create resources such as IAM polices and roles to complete this setup. You can achieve the same steps via the AWS Management Console, but it’s not covered in this post.

Creating an IAM role and configuring DB management or bastion host (EC2)

To start configuring your IAM credentials, complete the following steps in the management AWS account 99999999999, where a specific Amazon Elastic Compute Cloud (Amazon EC2) instance is running as a DB management or Bastion host.

1. Create the JSON file that defines the trust relationship:

   vi DB-Management-Host-Assume-Role-Trust-Policy.json

The contents of the DB-Management-Host-Assume-Role-Trust-Policy.json file are:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "ec2.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

2. Create the IAM role using the trust policy created in step 1 and take a note of the Amazon Resource Name (ARN) available in the output for the role being created:

   aws iam create-role --role-name DB-Management-Host-CrossAccountAccess-Role --assume-role-policy-document file://DB-Management-Host-Assume-Role-Trust-Policy.json

The aws iam create-role command outputs several pieces of information, including the Amazon Resource Name (ARN) of the IAM policy:

arn:aws:iam::99999999999:role/DB-Management-Host-CrossAccountAccess-Role

3. Create an IAM policy that grants the permissions to users who are in the DB management host running from the management account to run the sts:AssumeRole action using the AWS CLI for each application account:

   vi DB-Management-Host-ApplicationAccountAccess-Policy.json

The contents of the DB-Management-Host-ApplicationAccountAccess-Policy.json file should be similar to the following (the ARN is the ARN of the role you create on each application in the next section):

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "sts:AssumeRole",
            "Resource": [
              "arn:aws:iam::*:role/DB-Management-Host-Access-Role"
            ]
        }
    ]
}

You can use * instead of a specific AWS account name or number. This way, you avoid updating this policy in the future if you add more AWS accounts for different applications to your organization however it depends on your security/infosec guidelines.

4. Create the IAM policy using the JSON document created earlier and take a note of the Amazon Resource Name (ARN) available in the output for the policy being created:

   aws iam create-policy --policy-name DB-Management-Host-ApplicationAccountAccess-Policy --policy-document file://DB-Management-Host-ApplicationAccountAccess-Policy.json

The command outputs several pieces of information, including the ARN of the IAM policy:

arn:aws:iam::99999999999:policy/DB-Management-Host-ApplicationAccountAccess-Policy

5. Attach the IAM policy created from the previous step to the role DB-Management-Host-CrossAccountAccess-Role created in step 2:

   aws iam attach-role-policy --role-name DB-Management-Host-CrossAccountAccess-Role --policy-arn "arn:aws:iam::99999999999:policy/DB-Management-Host-ApplicationAccountAccess-Policy"

6. Create an instance profile named DB-Management-Host-Profile:

   aws iam create-instance-profile --instance-profile-name DB-Management-Host-Profile

The command outputs several pieces of information, including the ARN of the IAM policy:

aws iam create-instance-profile --instance-profile-name DB-Management-Host-Profile

7. Add the DB-Management-Host-CrossAccountAccess-Role role created from step 2 to the DB-Management-Host-Profile instance profile created in the previous step:

   aws iam add-role-to-instance-profile --instance-profile-name DB-Management-Host-Profile --role-name DB-Management-Host-CrossAccountAccess-Role

8. Use the following associate-iam-instance-profile command to associate the instance profile created from the step 6 to the EC2 instance (for this post, DB management or Bastion host) by specifying the appropriate instance-id and iam-instance-profile. You can use the ARN of the instance profile, or you can use its name. You can get the instance-id of the DB management host from the console as well.

   aws ec2 associate-iam-instance-profile --instance-id i-04f7b9c00166da011 --iam-instance-profile Name="DB-Management-Host-Profile"

The command outputs several pieces of information, including the ARN of the IAM policy:

arn:aws:iam::99999999999:instance-profile/DB-Management-Host-Profile

At this point, we have created the following in the centralized AWS account from where we want to manage all the databases running on different application accounts:

• An IAM role with the assumed role as a trust policy
• A policy that allows users to assume a role in application accounts
• An instance profile with an added IAM role
• An instance profile associated to the DB management or Bastion host (EC2 instance)

Creating an IAM role on AWS Accounts where Amazon RDS Instance is running

Complete the following policy and role creation steps on each application account (1111111111111, in this case). This gives access to run commands from the AWS CLI on the DB management host by assuming roles.

1. Create the JSON file that defines the trust relationship:

   vi DB-Management-Host-Access-Role-Trust-Policy.json

The contents of the DB-Management-Host-Access-Role-Trust-Policy.json file should be similar to the following:

{

"Version": "2012-10-17",

"Statement": {

"Effect": "Allow",

"Principal": { "AWS": "arn:aws:iam::99999999999:role/DB-Management-Host-CrossAccountAccess-Role" },

"Action": "sts:AssumeRole"

}

}

For this post, the principal attribute states who has the DB-Management-Host-CrossAccountAccess-Role role (Created in Step 2) from the 99999999999 account (which is an AWS management account where you run the AWS CLI in the DB management or Bastion host) can assume the role being created in the application accounts. You need to use the ARN in the trust policy document when you created the IAM role earlier.

2. Create the IAM role and define the trust relationship according to the contents of the JSON file:

   aws iam create-role --role-name DB-Management-Host-Access-Role --assume-role-policy-document file://DB-Management-Host-Access-Role-Trust-Policy.json

The command outputs several pieces of information, including the ARN of the IAM role. Take a note of the Amazon Resource Name (ARN) available in the output for the role being created:

arn:aws:iam::11111111111:role/DB-Management-Host-Access-Role

You’re now ready to create the IAM customer managed policy. This policy grants the permissions to run specific commands on AWS application accounts by users who are coming from a specific EC2 instance (for this post, the DB management host in a centralized AWS account) using the AWS CLI. For this post, we allow users to run Amazon RDS-specific commands, but you can add more permissions related to other services where database administrators want to query from the AWS CLI as per your requirements.

You can use an AWS managed policy instead of creating customer managed polices if that suits your requirements, such as AmazonRDSReadOnlyAccess. 

You can replace DB-Cross-Account-RDS-Describe-Policy.json with your own policy name, action, and resource as per your requirements. For actions, consider what kind of commands the user can run on specific services or resources. The following code lists all actions for Amazon RDS. You can add other permissions, such as retrieving secrets, as per your requirements. Regarding resources, consider what specific resources are included in this access. It can be specific to a database by providing the resource ARN or all of that service type.

3. Create your customer managed policy with the following code:

   vi DB-Management-Host-RDS-Describe-Policy.json
   
   {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Sid": "VisualEditor0",
               "Effect": "Allow",
               "Action": [
                   "rds:DescribeDBProxyTargetGroups",
                   "rds:DescribeDBInstanceAutomatedBackups",
                   "rds:DescribeDBEngineVersions",
                   "rds:DescribeDBSubnetGroups",
                   "rds:DescribeGlobalClusters",
                   "rds:DescribeExportTasks",
                   "rds:DescribePendingMaintenanceActions",
                   "rds:DescribeEngineDefaultParameters",
                   "rds:DescribeDBParameterGroups",
                   "rds:DescribeDBClusterBacktracks",
                   "rds:DescribeReservedDBInstancesOfferings",
                   "rds:DescribeDBProxyTargets",
                   "rds:DescribeDBInstances",
                   "rds:DescribeSourceRegions",
                   "rds:DescribeEngineDefaultClusterParameters",
                   "rds:DescribeDBProxies",
                   "rds:DescribeDBParameters",
                   "rds:DescribeEventCategories",
                   "rds:DescribeEvents",
                   "rds:DescribeDBClusterSnapshotAttributes",
                   "rds:DescribeDBClusterParameters",
                   "rds:DescribeEventSubscriptions",
                   "rds:DescribeDBSnapshots",
                   "rds:DescribeDBLogFiles",
                   "rds:DescribeDBSecurityGroups",
                   "rds:DescribeDBSnapshotAttributes",
                   "rds:DescribeReservedDBInstances",
                   "rds:DescribeValidDBInstanceModifications",
                   "rds:DescribeDBClusterSnapshots",
                   "rds:DescribeOrderableDBInstanceOptions",
                   "rds:DescribeOptionGroupOptions",
                   "rds:DescribeDBClusterEndpoints",
                   "rds:DescribeCertificates",
                   "rds:DescribeDBClusters",
                   "rds:DescribeAccountAttributes",
                   "rds:DescribeOptionGroups",
                   "rds:DescribeDBClusterParameterGroups"
               ],
               "Resource": "*"
           }
       ]
   }

You can add specific conditions on who can use this policy, such as MFA and Source IP range, by adding a condition class to the policy document:

            "Condition": {
                "IpAddress": {
                    "aws:SourceIp": "210.75.12.75/16"
                },
                "BoolIfExists": {
                    "aws:MultiFactorAuthPresent": "true"
                }
            }
        }

4. Create the IAM policy using the JSON document created earlier:

   aws iam create-policy --policy-name DB-Management-Host-RDS-Describe-Policy --policy-document file://DB-Management-Host-RDS-Describe-Policy.json

The command outputs several pieces of information, including the ARN of the IAM policy:

arn:aws:iam::11111111111:policy/DB-Management-Host-RDS-Describe-Policy

5. Attach the customer managed policy DB-Management-Host-RDS-Describe-Policy created in previous step to the role that created in step 2 (you can also attach additional policies based on your needs of what AWS cli should have permission to execute):

   aws iam attach-role-policy --role-name DB-Management-Host-Access-Role --policy-arn "arn:aws:iam::11111111111:policy/DB-Management-Host-RDS-Describe-Policy"

6. Repeat these steps on each application account by creating the role and policy to allow users to run AWS CLI commands from the DB management or Bastion host running from the DB management account.

Installing and configuring the AWS CLI on an EC2 instance (DB management host)

For instructions on installing the AWS CLI, see Installing, updating, and uninstalling the AWS CLI version 2.

To avoid AWS CLI configuration for each user or database administrator who has access to the DB management or bastion host, we install the AWS CLI in the centralized location and update the config files with appropriate information. See the following code for Linux based EC2 instance running as a DB management or bastion host:

sudo <awscliunziplocation>/aws/install --install-dir
<nondefaultcentralizedlocation> --bin-dir /usr/local/bin

You can install the AWS CLI in a non-default location and have the aws command path added to /usr/local/bin. For example, see the following code:

./aws/install -i /u01/app/aws-cli -b /usr/local/bin

Make sure /usr/local/bin is in your path to run aws commands.

Note: Please ensure that, you have AWS CLI installed on windows based EC2 instance running as a DB management or bastion host if you are using windows-based operating system. You can also install AWS CLI in a common location for all users and make sure they are in the PATH variable

For Linux, You can export or set the AWS_CONFIG_FILE=<PATH> variable in the user session to specify the non-default and common location. This way, you’re not required to add profiles in the AWS config file for each user in that DB management or bastion host. You can automate this environment non-default location setup for all users by adding a script under /etc/profile.d for Linux OS. This sets up the environment variables during each user login process. For example, see the following code:

vi /etc/profile.d/awsclienvironment.sh

Add the following lines to the /etc/profile.d/awsclienvironment.sh file and save it:

AWS_CONFIG_FILE=/u01/aws/aws-cli/config

Also, you can set up a system environment variable for AWS_CONFIG_FILE in Windows OS to point to centralized location.

Add the following lines in your AWS_CONFIG_FILE (/u01/aws/aws-cli/config) and you can query an RDS instance by specifying the --profile option to DB1 from the DB management host. You add the profile section for each application account by getting the role ARN you created earlier. See the following code:

[profile DB1]
role_arn = arn:aws:iam::11111111111:role/DB-Management-Host-Access-Role
credential_source=Ec2InstanceMetadata
[profile DB2]
role_arn = arn:aws:iam::811351823455:role/DB-Management-Host-Access-Role
credential_source=Ec2InstanceMetadata

You can use the AWS CLI to query profiles configured in the AWS_CONFIG_FILE with the following code:

aws configure list-profiles

Query each account with the following code:

aws rds describe-db-instances --profile DB1

You can set up an environment variable in .bash_profile for Linux-based operating systems or use the SET command in Windows if you want to avoid passing the --profile option in your AWS CLI commands:

Linux or macOS

$ export AWS_PROFILE=DB1

Windows

C:\> set AWS_PROFILE=DB1

Further automation for Oracle Databases

You can further automate this if you’re using the oraenv script from an Oracle client or Oracle database installations on the DB management or Bastion host running with Linux OS. You can open the default oraenv script located in $ORACLE_HOME/bin/oraenv and add the following lines to the script to set the AWS_PROFILE environment variable based on the SID you pick during environment setup:

# Install any "custom" code here
#

if [ ${ORACLE_SID} = " " ]; then
unset AWS_PROFILE
else
AWS_PROFILE="$ORACLE_SID";
export AWS_PROFILE
fi

You can add all the database information in /etc/oratab from all the accounts and configure your profile names in the AWS_CONFIG_FILE to match with SID names that are in /etc/oratab. For example, see the following code:

vi /etc/oratab
DB1:/u01/app/oracle/product/19.0:Y
DB2:/u01/app/oracle/product/19.0:Y

. oraenv DB1

env | grep ORACLE_SID
env | grep AWS_CONFIG_FILE
env | grep AWS_PROFILE

Conclusion

In this post, we demonstrated how to use a cross-account IAM role with AWS CLI to access all your AWS accounts from a bastion or DB management host without using AWS account credentials. We also provided tips to automate Amazon RDS database management with AWS CLI configurations and settings for all database administrators. Please try this walkthrough for yourself, and leave any comments!

About the Authors

Siva Subramaniam is a Database Consultant with the AWS Professional Services team at Amazon Web Services. He has experience in technical leadership, application and database design, infrastructure and application support, DBA including database architecture, design, build, data modeling, infrastructure automation, migrations and upgrades, performance tuning, monitoring, backup and recovery, and disaster recovery. Siva holds a master’s degree in Computer Applications and is a certified professional in various database and cloud technologies including AWS, Azure, OCI, Oracle, MSSQL, MySQL, Cassandra, and VMware.

Sharath Lingareddy is a Database Architect with the AWS Professional Services team at Amazon Web Services. He has provided solutions using Oracle, PostgreSQL, and Amazon RDS. His focus area is homogeneous and heterogeneous migrations of on-premises databases to Amazon RDS and Amazon Aurora PostgreSQL.

John Young is a Sr Cloud Infrastructure Architect with the AWS Professional Services team at Amazon Web Services. He has experience designing and implementing multi-account AWS environments utilizing landing zone frameworks and infrastructure-as-code. John works with customers on designing, building and migrating applications to AWS. John holds eight active AWS Certifications.