If you are monitoring PostgreSQL instances on Amazon RDS or Aurora clusters, then you can set up Redgate Monitor to authenticate via AWS Identity and Access Management (IAM).

This can provide enhanced security when using IAM roles, as there is no need to store long-lived credentials such a password or secret key to connect to an instance.

Setting up your RDS instance or Aurora cluster for IAM authentication

In order to enable IAM authentication for PostgreSQL instances in Redgate Monitor there are a few pre-requisite steps to go through within AWS.

1. Make sure that your Aurora cluster or RDS instance has IAM authentication enabled, as it is disabled by default.
   You can enable it by navigating to your instance or cluster's overview page in the AWS portal and selecting Modify and then selecting Password and IAM database authentication for RDS instances, or checking the IAM Database authentication checkbox on Aurora cluster
   
   
   
   
2. You must grant the rds_iam role to the Redgate Monitor database user that you have previously configured for monitoring the PostgreSQL instance. To grant IAM permissions you can run the following SQL on either an Aurora cluster or RDS instance as the master user, replacing redgatemonitor with the relevant user.
   

   GRANT rds_iam TO redgatemonitor;

   
   

   Granting the IAM authentication role to a database user will limit that user from logging in via methods other than IAM. If you still wish to login normally with the existing user you should create a new user.

   Please see the AWS documentation for other limitations of IAM database users.

3. Create an IAM policy that will grant permission to connect to an RDS instance or Aurora cluster via IAM.
   1. When specifying permissions for the new policy, select RDS IAM Authentication for the service. This applies to both Aurora clusters and RDS instances.
      
   2. After selecting the service, you should check the connect checkbox under Permissions management.
      
   3. In the Resources section you should click Add ARNs in order to restrict the access of this permission to the specific RDS instance or Aurora cluster that you wish to monitor.
      
   4. You should then fill in the details of the instance or cluster that you wish to monitor, making sure to use the user name that you granted the IAM permissions to in step 2. The dbi resource can be found in the overview page for your resource under the Configuration tab with the heading Resource ID.
      
4. At this point, the guide to set up IAM authentication outlined in Enabling advanced monitoring for RDS instances provides a step-by-step guide to how you can set up an IAM role or user that Redgate Monitor's Base Monitor can use.
   You should follow this guide, but instead of adding the AmazonRdsReadOnlyAccess and CloudWatchReadOnlyAccess permission policies in places where that is required, you should add the policy you have created above.
   
   • If you wish to use the same IAM authentication for advanced monitoring as you have for your instance you should include the above permission policies alongside the RDS IAM Authentication policy, you can then select "Use instance credentials" when enabling advanced monitoring.
     
     
     

Using your IAM authentication credentials in Redgate Monitor

After following the steps outlined above to enable IAM authentication on the AWS side, you can use those credentials to connect to the relevant PostgreSQL or Aurora instance by selecting IAM Role or IAM User in the Instance authentication method dropdown and filling in the relevant details.

• For IAM roles that are attached to your EC2 instance running the Base Monitor service, you should leave the Role identifier empty as it will default to the instance profile.
• For IAM roles that exist on a separate AWS account to be assumed by the Base Monitor service, you should use the ARN of the role that has permission to connect to the instance or cluster as the Role identifier.
• For IAM users you can generate the Access key and Secret key as described in the Creating an AWS IAM user section of the Enabling advanced monitoring for RDS instances guide.

Troubleshooting common errors

28000: pg_hba.conf rejects connection for host "<hostname>", user "iam_user", database "redgatemonitor", no encryption

In the context of connecting via IAM, PostgreSQL returns this error when the login password given for a user with the rds_iam role is incorrect.

The typical cause for this is that the resource is not correctly defined in the policy to allow database connections through IAM, as set up in the steps above. An incorrect resource will mean that the password generated by AWS will be invalid for the user.

You should double check that the ARN you are using in the Resources section of your policy refers to the correct instance on the correct account in the correct region, and specifies the exact user that has the rds_iam role granted to it in the PostgreSQL instance.

For example, a policy that includes targeting a PostgreSQL instance on account ID 277913760595, targeting a database with a resource ID db-5SDJGFVS3EUTLOHTMILMTL5GHI on eu-west-2, with the rds_iam user set to redgatemonitor should look like the following JSON:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "rds-db:connect",
            "Resource": [
                "arn:aws:rds-db:eu-west-2:277913760595:dbuser:db-5SDJGFVS3EUTLOHTMILMTL5GHI/redgatemonitor"
            ]
        }
    ]
}

28P01: password authentication failed for user "iam_user"

In the context of connecting via IAM, this error typically suggests that the rds_iam role has not been granted to the user.

You should grant the role by executing the following command on the instance and try again.

GRANT rds_iam TO redgatemonitor;

User: <arn> is not authorized to perform: sts:AssumeRole on resource: <arn>

This error means that the AWS profile attached to your machine does not have permission to assume the role that contains the database connection policy.

If you are using an EC2 instance as your Base Monitor, you can find which role is attached by visiting the EC2 dashboard and checking the Actions > Security > Modify IAM Role page.

Whichever role you have attached, via the instance profile or an AWS profile on the machine, must have an assume role policy with its resource set to the ARN of the role that has your database connection policy set up in the steps above, and that role must trust the role attached to your EC2 instance to allow role assumption.