AWS: RDS IAM database authentication, EKS Pod Identities, and Terraform

We’re preparing to migrate our Backend API database from DynamoDB to AWS RDS with PostgreSQL, and finally decided to try out AWS RDS IAM database authentication, which appeared in 2021.

IAM database authentication, as the name implies, allows us to authenticate to RDS using AWS IAM instead of the login-password from the database server itself.

However, authorization — that is, checking what access the user has to the database(s) — remains with the database server itself, because IAM will only give us access to the RDS instance itself.

So what are we going to do today?

• first, let’s try how RDS IAM database authentication works in general, and how it is configured
• then we’ll move on to automation with Terraform, and will recall how AWS EKS Pod Identities works
• we will write a Python code that will run in a Kubernetes Pod with ServiceAccount attached and will connect to an RDS instance using RDS IAM database authentication
• and finally, will discuss the challenges of using RDS IAM database authentication and automation with Terraform

I’m testing on RDS, which is created for Grafana, so sometimes there will be names with “monitoring”/”grafana”.

How RDS IAM database authentication is working?

Documentation — IAM database authentication for MariaDB, MySQL, and PostgreSQL.

The general idea is that instead of using a common password to RDS, an IAM token is used for an IAM Role or IAM User which has an IAM Policy connected, and that IAM Policy describes a username and an ID of an Aurora cluster or RDS instance.

But, unfortunately, this is where the role of IAM ends, because accesses and permissions in the database server itself are created and managed as before, that is, through CREATE USER and GRANT PERMISSIONS.

IAM database authentication and Kubernetes ServiceAccount

As for Kubernetes Pod, I honestly expected a little more, because I thought that just using the IAM Role and Kubernetes ServiceAccount, it would be possible to connect to RDS without a password at all — as we do with access to other resources in AWS through the AWS API.

But with RDS, the scheme looks a bit different:

• we create an RDS instance with the IAM authentication parameter == true
• then create an IAM Role with an IAM Policy
• create a corresponding user in PostgreSQL/MariaDB, enable authentication via IAM
• in Kubernetes, create a ServiceAccount with this role
• connect this ServiceAccount to the Kubernetes Pod
• in the Pod, using the IAM Role from that ServiceAccount, we’ll have to generate an IAM RDS Token for RDS access
• and with that token, we can connect to the RDS server

Let’s try it manually first, and then we’ll see how to do it with Terraform, because there are some nuances.

RDS IAM authentication: testing

So, we have an already created RDS PostgreSQL with Password and IAM database authentication:

For the server, we already have a default master user and password in the AWS Secrets Manager — you will need it to add a new user in the RDS.

Find the instance ID — it will be needed in the IAM Policy:

Creating IAM Policy

Next, we need an IAM Policy that will allow our future user access to this RDS instance.

Go to IAM > Policy, create a new policy, see the documentation Creating and using an IAM policy for IAM database access:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "rds-db:connect",
            "Resource": "arn:aws:rds-db:us-east-1:492 ***148:dbuser:db-UZM*** 3SA/db_test"
        }
    ]
}

Here, we Allow the rds-db:connect action to the database server in Resource using the db_test username. Later, we will add the same db_test user by using the CREATE USER command on the database server itself.

Note that there is not the RDS instance name set, but its ID  —  db-XXXYYYZZZ.

Save the Policy:

You can connect this Policy directly to your AWS IAM User or use an IAM Role.

We’ll try with an IAM Role later when we’ll connect a Kubernetes Pod, but for now, let’s use a regular IAM User to test the mechanism in general.

Find the required IAM User and add permissions:

Select Attach policies directly, find our IAM Policy:

The next step is to add the user to RDS.

PostgreSQL: creating a database user

Documentation — Creating a database account using IAM authentication.

Note : Make sure the specified database user name is the same as a resource in the IAM policy for IAM database access

That is, when running the CREATE USER, we must specify the same db_test user's name that is specified in the "Resource" of our IAM Policy:

...
"Resource": "arn:aws:rds-db:us-east-1:492 ***148:dbuser:db-UZM*** 3SA/db_test"
...

Connect with the default user and password that you received when created the RDS instance:

$ psql -h ops-monitoring-rds.***.us-east-1.rds.amazonaws.com -U master_user -d ops_monitoring_db

Create a new user db_test, and set his authentication through the rds_iam PostgreSQL role:

ops_grafana_db=> CREATE USER db_test;
CREATE ROLE
ops_grafana_db=> GRANT rds_iam TO db_test;
GRANT ROLE

For MariaDB, it will be the AWSAuthenticationPlugin.

Connecting with psql

Documentation — Connecting to your DB instance using IAM authentication from the command line: AWS CLI and psql client.

Note : You cannot use a custom Route 53 DNS record instead of the DB instance endpoint to generate the authentication token.

Find the URL of the server’s endpoint:

Set a variable with the address:

$ export RDSHOST="ops-monitoring-rds.***.us-east-1.rds.amazonaws.com"

Using the AWS CLI and the aws rds generate-db-auth-token command, get a token - this will be our password:

$ export PGPASSWORD="$(aws --profile work rds generate-db-auth-token --hostname $RDSHOST --port 5432 --region us-east-1 --username db_test)"

Check it’s content:

$ echo $PGPASSWORD
ops-monitoring-rds. ***.us-east-1.rds.amazonaws.com:5432/?Action=connect&DBUser=db_test&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=*** %2F20240624%2Fus-east-1%2Frds-db%2Faws4_request&X-Amz-Date=20240624T142442Z&X-Amz-Expires=900&X-Amz-SignedHeaders=host&X-Amz-Security-Token=IQo***942

And connect to the RDS:

$ psql "host=$RDSHOST sslmode=require dbname=ops_grafana_db user=db_test password=$PGPASSWORD"
psql: error: connection to server at "ops-monitoring-rds.***.us-east-1.rds.amazonaws.com" (10.0.66.79), port 5432 failed: FATAL: PAM authentication failed for user "db_test"

FATAL: PAM authentication failed for user “db_test”

In my case, the error occurred because I first generated the token with the “--region us-west-2", and the RDS server is located in the us-east-1 (hello, copy-paste from the documentation :-) ).

That is, the error occurs precisely because of errors in the access settings — either a different username is specified in the IAM Policy, or a different name was used during the CREATE USER, or a token is generated for a different IAM role.

Let’s regenerate the token and try again:

$ psql "host=$RDSHOST sslmode=require dbname=ops_grafana_db user=db_test password=$PGPASSWORD"
psql (16.2, server 16.3)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

ops_grafana_db=> 
ops_grafana_db=> \dt
                        List of relations
 Schema | Name | Type | Owner       
--------+-----------------------------+-------+------------------
 public | alert | table | ops_grafana_user
 public | alert_configuration | table | ops_grafana_user
 public | alert_configuration_history | table | ops_grafana_user
...

Moreover, the password=$PGPASSWORD part can be omitted - psql will read the $PGPASSWORD variable itself, see Environment Variables.

dbname=ops_grafana_db is here because the server was created for Grafana, and this is its database.

Okay — we checked it, it works.

Now it’s time for Kubernetes and automation with Terraform — and our adventures are just beginning.

Terraform, AWS EKS Pod Identity, and IAM database authentication

Let’s see how this mechanism will work with Kubernetes Pods and ServiceAccounts.

I wrote more about the new scheme of working with Pod ServiceAccounts and IAM in AWS: EKS Pod Identities — a replacement for IRSA? Simplifying IAM Access Management, but I haven’t used it in production yet.

So, what do we need?

• an IAM Role with IAM Policy
• in the Trusted Policy of this IAM Role we will have pods.eks.amazonaws.com
• will add the IAM Role to an EKS cluster via the EKS IAM API
• will create a Kubernetes Pod and a ServiceAccount
• in the Pod, we’ll have a Python code that will connect to RDS

That is, the Kubernetes Pod will use the IAM Role from the Kubernetes ServiceAccount to authenticate to the AWS API, then, using this role, it will receive an AWS RDS Token from the AWS API, and with this token, it will connect to RDS.

Creating AWS EKS Pod Identity with Terraform

There is a module for AWS EKS Pod Identity eks-pod-identity, let's use it.

In Terraform, describe an aws_iam_policy_document with access to RDS:

data "aws_iam_policy_document" "monitoring_rds_policy" {
  statement {
    effect = "Allow"

    actions = [
      "rds-db:connect"
    ]
    resources = [
      "arn:aws:rds-db:us-east-1:${data.aws_caller_identity.current.account_id}:dbuser:${module.monitoring_rds.db_instance_resource_id}/test_user"
    ]
  }
}

The IAM Policy is new, and we’ll use a new user —  test_user.

In the ${data.aws_caller_identity.current.account_id} we have our AWS account ID:

data "aws_caller_identity" "current" {}

And in the ${module.monitoring_rds.db_instance_resource_id} - the ID of our RDS instance, which was created using the terraform-aws-modules/rds/aws module with the iam_database_authentication_enabled = true parameter:

module "monitoring_rds" {
  source = "terraform-aws-modules/rds/aws"
  version = "~> 6.7.0"

  identifier = "${var.environment}-monitoring-rds"
  ...
  # DBName must begin with a letter and contain only alphanumeric characters
  db_name = "${var.environment}_grafana_db"
  username = "${var.environment}_grafana_user"
  port = 5432

  manage_master_user_password = true
  manage_master_user_password_rotation = false

  iam_database_authentication_enabled = true
  ...
}

Next, with terraform-aws-modules/eks-pod-identity/aws we describe an EKS Pod Identity Association, where we use the aws_iam_policy_document.monitoring_rds_policy that we made above:

module "grafana_pod_identity" {
  source = "terraform-aws-modules/eks-pod-identity/aws"
  version = "~> 1.2.1"

  name = "${var.environment}-monitoring-rds-role"

  attach_custom_policy = true
  source_policy_documents = [data.aws_iam_policy_document.monitoring_rds_policy.json]

  associations = {
    atlas-eks = {
      cluster_name = data.aws_eks_cluster.eks.name
      namespace = "${var.environment}-monitoring-ns"
      service_account = "eks-test-sa"
    }
  }
}

In the namespace we specify a Namespace name in which the ServiceAccount for the Pod will be created, and in the service_account - the actual name of the ServiceAccount.

data.aws_eks_cluster.eks.name is retrieved from the data "aws_eks_cluster":

# get info about a cluster
data "aws_eks_cluster" "eks" {
  name = local.eks_name
}

Deploy it and check the IAM:

And the Pod Identity associations in the AWS EKS cluster:

Now we have an IAM Role with the IAM Policy attached which grants access to the test_user user to the RDS instance with the ID db-UZM***3SA, and we have an established relationship between the ServiceAccount named eks-test-sa in the Kubernetes cluster and this IAM role.

Python, PostgreSQL, and IAM database authentication

What should happen next:

• we’ll create a Kubernetes Pod
• create a ServiceAccount with the name eks-test-sa
• will write a Python code that will:
• connect to the AWS API using the ServiceAccount and the associated IAM Role
• receive an AWS RDS Token
• use this token to connect to RDS

Log in to RDS with the master user again, and create a new user test_user (as specified in the IAM Policy) with the role rds_iam:

ops_grafana_db=> CREATE USER test_user;
CREATE ROLE
ops_grafana_db=> GRANT rds_iam TO test_user;
GRANT ROLE
ops_grafana_db=> GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO test_user;
GRANT

Create a Kubernetes manifest with the eks-test-sa ServiceAccount and a Kubernetes Pod that will use that ServiceAccount in the namespace=ops-monitoring-ns:

apiVersion: v1
kind: ServiceAccount
metadata:
  name: eks-test-sa
  namespace: ops-monitoring-ns
---
apiVersion: v1
kind: Pod
metadata:
  name: eks-test-pod
  namespace: ops-monitoring-ns
spec:
  containers:
    - name: ubuntu
      image: ubuntu
      command: ['sleep', '36000']
  restartPolicy: Never
  serviceAccountName: eks-test-sa

Deploy:

$ kk apply -f eks-test-rds-irsa.yaml
serviceaccount/eks-test-sa created
pod/eks-test-pod created

Connect to the Pod:

$ kk exec -ti eks-test-pod -- bash
root@eks-test-pod:/#

Install the python-boto3 library to get a token from the code, and the python3-psycopg2 library to work with PostgreSQL:

root@eks-test-pod:/# apt update && apt -y install vim python3-boto3

Write the code:

#!/usr/bin/python3

import boto3
import psycopg2

DB_HOST="ops-monitoring-rds.***.us-east-1.rds.amazonaws.com"
DB_USER="test_user"
DB_REGION="us-east-1"
DB_NAME="ops_grafana_db"

client = boto3.client('rds')

# using Kubernetes Pod ServiceAccount's IAM Role generate another AWS IAM Token to access RDS
db_token = client.generate_db_auth_token(DBHostname=DB_HOST, Port=5432, DBUsername=DB_USER, Region=DB_REGION)

# connect to RDS using the token as a password
conn = psycopg2.connect(database=DB_NAME,
                        host=DB_HOST,
                        user=DB_USER,
                        password=db_token,
                        port="5432")

cursor = conn.cursor()

cursor.execute("SELECT * FROM dashboard_provisioning")

print(cursor.fetchone())

conn.close()

Basically, it’s quite simple: connect to AWS, get a token, and connect to RDS.

Run it and check the result:

root@eks-test-pod:/# ./test-rds.py 
(1, 1, 'default', '/var/lib/grafana/dashboards/default/nodeexporter.json', 1719234200, 'c2ef5344baf3389f5238679cd1b0ca68')

A bit about what exactly happens “under the hood”:

• the Kubernetes Pod has a ServiceAccount
• the ServiceAccount is associated with the ops-monitoring-rds-role IAM Role via Pod Identity associations
• the ops-monitoring-rds-role IAM Role has an IAM Policy with the Allow on rds-db:connect
• the Kubernetes Pod uses that IAM Role from the ServiceAccount for authentication and authorization in AWS
• and then the Python gets an RDS Token with the boto3 and client.generate_db_auth_token
• and uses it to connect to PostgreSQL

On the RDS itself, we already have the test_user user created with the rds_iam and permissions to the databases.

For more information on how Kubernetes ServiceAccounts and tokens work at the Kubernetes Pod level, see AWS: EKS, OpenID Connect, and ServiceAccounts (just it was written without Pod Identity associations, but the mechanism is the same).

So, the solution we just tried looks like a good option, but there is one more thing.

Terraform and IAM RDS Authentication: the problems

In general, the idea with the Terraform described above seems to be working, but we manually created test_user and gave it permissions.

And here’s another drawback of the RDS and IAM database authentication scheme, because we still need to create a user in the database server.

And this leads to another problem: how to do this with Terraform?

I didn’t spend any more time on it, because it’s not really relevant to us as in my case, there will be only a few users, and they can be made manually, and it doesn’t block the current automation.

But over time, when the project grows, this issue will still have to be addressed.

So, what problems and solutions do we have?

• we can create PostgreSQL (or MariaDB) users directly from Terraform code using the PostgreSQL provider, and by running local-exec or using resource "postgresql_grant"
• see examples in AWS RDS IAM Authentication with Terraform and grant privileges and permissions to a role via terrafrom is not working
• but this requires network access to the RDS instance itself, which is running in the private network of the VPC, and therefore CI/CD requires network access to the VPC, which is possible if you run GitHub Runners (in our case) in Kubernetes, whose WorkerNodes have access to private subnets — but now we are using GitHub hosted Runners, and they don’t have this access
• the second option is to use an AWS Lambda function, which will run in the VPC with network access to the RDS, and this function will run PostgreSQL commands to create users
• see examples in Securing AWS RDS with Fine-Grained Access Control using IAM Authentication, Terraform and Serverless and Automate post-database creation scripts or steps in an Amazon RDS for Oracle database
• seems to be a working option, also instead of AWS Lambda we can use Terraform to run a Kubernetes Pod, which will perform the necessary actions — connect to RDS and CREATE USER

Both solutions are working, and someday I may describe the implementation of one of them (most likely the second one, using Lambda or EKS Pod).

But currently, I don’t see any point in spending time on it.

Final conclusions

And the conclusions are actually a bit ambiguous.

The idea of RDS IAM database authentication looks very interesting, but the fact that the RDS token and the regular authentication token in the AWS API for IAM Roles are different entities makes it a bit difficult to implement. If you could connect to RDS just using ServiceAccount and IAM Role, it would be much easier to use.

In addition, for some reason, I expected that authorization would be done at the IAM level, i.e. that in the IAM Policy we could specify at least the databases to which we want to grant access. But it remains at the database server level.

The second problem is that we still have to create a user in an RDS instance, and set their permissions there, and that again creates additional difficulties in automation.

However, in general, RDS IAM database authentication fulfills its task — we really don’t need to create a Kubernetes Secret with a password for the database and mount it to the Kubernetes Pod, but rather we can connect a ServiceAccount to the Pod, and “pass the buck” to the developers, i.e., to perform the authentication it at the code’s level, not Kubernetes.

Originally published at RTFM: Linux, DevOps, and system administration.