Cloud Experts Documentation

Connect to RDS database with STS from ROSA

Last edited
Published
Authors
Florian Jacquin
Tags: AWS RDS ROSA STS

This content is authored by Red Hat experts, but has not yet been tested on every supported configuration.

The Amazon Web Services Relational Database Service (AWS RDS) can be consumed from Red Hat OpenShift Service on AWS (ROSA) and authenticate to DB with Security Token Service (STS).

This is a guide to quickly connect to RDS Database (Postgres engine) from ROSA.

Amazon Web Services Relational Database Service

Amazon Web Services Relational Database Service (AWS RDS) is a distributed relational database service by Amazon Web Services. It is designed to simplify setup, operation, and scaling of a relational database for use in applications. It supports differents database engines such as Amazon Aurora, MySQL, MariaDB, Oracle, Microsoft SQL Server, and PostgreSQL.

In our example we will use PostgreSQL as engine.

Prerequisites

  • A Red Hat OpenShift on AWS (ROSA) 4.12 cluster
  • The OC CLI
  • The AWS CLI
  • jq command

Set up environment

  1. Export value of your cluster name (rosa list cluster)

    export CLUSTER_NAME=<your_cluster_name>

  2. Export list of environements variables from your cluster

    export AWS_REGION=$(rosa describe cluster -c ${CLUSTER_NAME} -o json | jq -r .region.id)
export OIDC_PROVIDER=$(rosa describe cluster -c ${CLUSTER_NAME} -o json \
 | jq -r .aws.sts.oidc_endpoint_url | sed -e 's/^https:\/\///')
export AWS_ACCOUNT_ID=$(aws sts get-caller-identity --query Account --output text)
export SCRATCH_DIR=/tmp/scratch
export AWS_PAGER=""
export PSQL_PASSWORD=$(openssl rand -base64 12)
export NODE=$(oc get nodes --selector=node-role.kubernetes.io/worker \
 -o jsonpath='{.items[0].metadata.name}')
export VPC_ROSA=$(aws ec2 describe-instances \
 --filters "Name=private-dns-name,Values=$NODE" \
 --query 'Reservations[*].Instances[*].{VpcId:VpcId}' \
 --region $AWS_REGION \
 | jq -r '.[0][0].VpcId')
export ROSA_IP_OUT=$(aws ec2 describe-nat-gateways --filter "Name=vpc-id,Values=${VPC_ROSA}" --region ${AWS_REGION} \
 | jq -r .NatGateways[].NatGatewayAddresses[].PublicIp)
mkdir -p $SCRATCH_DIR

Create database network

  1. VPC + Subnets

    VPC_DB=$(aws ec2 create-vpc --cidr-block 10.23.0.0/16 --region ${AWS_REGION} | jq -r .Vpc.VpcId)
aws ec2 modify-vpc-attribute --vpc-id ${VPC_DB} --enable-dns-hostnames "{\"Value\":true}"
aws ec2 modify-vpc-attribute --vpc-id ${VPC_DB} --enable-dns-support "{\"Value\":true}"
SUBNET_A=$(aws ec2 create-subnet --vpc-id ${VPC_DB} --cidr-block 10.23.1.0/24 --availability-zone ${AWS_REGION}a | jq -r .Subnet.SubnetId)
SUBNET_B=$(aws ec2 create-subnet --vpc-id ${VPC_DB} --cidr-block 10.23.2.0/24 --availability-zone ${AWS_REGION}b | jq -r .Subnet.SubnetId) 
SUBNET_C=$(aws ec2 create-subnet --vpc-id ${VPC_DB} --cidr-block 10.23.3.0/24 --availability-zone ${AWS_REGION}c | jq -r .Subnet.SubnetId)

  2. Internet Gateway

    IGW=$(aws ec2 create-internet-gateway --region ${AWS_REGION} | jq -r .InternetGateway.InternetGatewayId)
aws ec2 attach-internet-gateway --vpc-id ${VPC_DB} --internet-gateway-id ${IGW}
RT_ID=$(aws ec2 describe-route-tables --filters Name=vpc-id,Values=${VPC_DB} --region ${AWS_REGION}  | jq -r .RouteTables[].RouteTableId)
aws ec2 create-route --route-table-id ${RT_ID} --destination-cidr-block 0.0.0.0/0 --gateway-id ${IGW} --region ${AWS_REGION}

  3. DB Subnet group

    aws rds create-db-subnet-group --db-subnet-group-name db-group-${CLUSTER_NAME} \
   --db-subnet-group-description "DB Subnet group for testing RDS" \
   --subnet-ids ${SUBNET_A} ${SUBNET_B} ${SUBNET_C} \
   --region ${AWS_REGION}

Create RDS Database

  1. Create DB with aws cli

    RDS_DB="$(aws rds create-db-instance \
    --db-instance-identifier psql-${CLUSTER_NAME} \
    --db-instance-class db.t3.micro \
    --engine postgres \
    --master-user-password ${PSQL_PASSWORD} \
    --allocated-storage 20 \
    --master-username postgres \
    --region ${AWS_REGION} \
    --db-subnet-group-name db-group-${CLUSTER_NAME} \
    --enable-iam-database-authentication \
    --publicly-accessible \
    --region ${AWS_REGION} \
    | jq -c '.DBInstance | { "DbiResourceId": .DbiResourceId, "VpcSecurityGroups": .VpcSecurityGroups[].VpcSecurityGroupId }')"
echo $RDS_DB

  2. Authorize ROSA cluster to connect to DB

    aws ec2 authorize-security-group-ingress \
 --group-id $(echo $RDS_DB | jq -r .VpcSecurityGroups) \
 --protocol tcp \
 --port 5432 \
 --cidr ${ROSA_IP_OUT}/32 \
 --region ${AWS_REGION}

IAM Permissions

  1. Build the RDS access Policy

    cat <<EOF > $SCRATCH_DIR/rds-policy.json
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "rds-db:connect"
            ],
            "Resource": [
                "arn:aws:rds-db:${AWS_REGION}:${AWS_ACCOUNT_ID}:dbuser:$(echo ${RDS_DB} | jq -r .DbiResourceId)/iamuser"
            ]
        }
    ]
}
EOF

  2. Create the RDS Access Policy

    This creates a named policy for the cluster, you could use a generic policy for multiple clusters to keep things simpler.

    POLICY=$(aws iam create-policy --policy-name "${CLUSTER_NAME}-rosa-rds-policy" \
   --policy-document file://$SCRATCH_DIR/rds-policy.json \
   --query 'Policy.Arn' --output text)
echo $POLICY

  3. Build Trust Policy

    cat <<EOF > $SCRATCH_DIR/trust-policy.json
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Federated": "arn:aws:iam::$(aws sts get-caller-identity --query 'Account' --output text):oidc-provider/${OIDC_PROVIDER}" 
            },
            "Action": "sts:AssumeRoleWithWebIdentity",
            "Condition": {
                "StringEquals": {
                    "${OIDC_PROVIDER}:sub": "system:serviceaccount:rds-sts-app:default" 
                }
            }
        }
    ]
}
EOF

  4. Create Role for accessing database

    ROLE=$(aws iam create-role \
  --role-name "${CLUSTER_NAME}-rosa-rds-access" \
  --assume-role-policy-document file://$SCRATCH_DIR/trust-policy.json \
  --query "Role.Arn" --output text)
echo $ROLE

  5. Attach the Policies to the Role

    aws iam attach-role-policy \
   --role-name "${CLUSTER_NAME}-rosa-rds-access" \
   --policy-arn $POLICY

Test STS

  1. Create new project

    oc new-project rds-sts-app

  2. Check that STS is working properly

    curl -s -H "Accept: application/json" "https://sts.amazonaws.com/\
?Action=AssumeRoleWithWebIdentity\
&DurationSeconds=3600\
&RoleSessionName=test\
&RoleArn=${ROLE}\
&WebIdentityToken=$(oc create token default --audience openshift --duration 60m)\
&Version=2011-06-15" | jq

Prepare Database

  1. Create a Pod for connecting to DB with postgres user

    DB_ENDPOINT=$(aws rds describe-db-instances --db-instance-identifier psql-${CLUSTER_NAME} --query 'DBInstances[*].[Endpoint.Address]' --output text --region ${AWS_REGION})
oc run -it --tty --rm --image registry.redhat.io/rhel8/postgresql-15 prep-db --env PGPASSWORD=${PSQL_PASSWORD} --env DB_ENDPOINT=${DB_ENDPOINT} -- /bin/sh

  2. Connect to DB, create user and DB (in the prompt of oc run)

    psql -h ${DB_ENDPOINT}
CREATE USER iamuser WITH LOGIN; 
GRANT rds_iam TO iamuser;
CREATE DATABASE iamdb;
\c iamdb
CREATE EXTENSION if not exists ip4r;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO iamuser;
quit

Connection with IAM / Populate DB

  1. Create pod to populate database and accessing with a IAM user this time

    oc run -it --tty --rm --image registry.redhat.io/rhel8/postgresql-15 iamdb-connect \
  --env PGSSLMODE=require \
  --env PGPASSWORD=$(aws rds generate-db-auth-token --hostname $DB_ENDPOINT --port 5432 --region ${AWS_REGION} --username iamuser) \
  --env DB_ENDPOINT=${DB_ENDPOINT} \
 -- /bin/sh

  2. Download dataset IPrange / Country

    curl -O -L https://github.com/sapics/ip-location-db/raw/master/geolite2-country/geolite2-country-ipv4.csv
sed -i 's/\,/\-/' geolite2-country-ipv4.csv

  3. Populate DB

    psql -h ${DB_ENDPOINT} -U iamuser -d iamdb
CREATE TABLE if not exists ref_ip_blocks
(
  iprange iprange,
  geoname varchar

);
\copy ref_ip_blocks FROM 'geolite2-country-ipv4.csv' DELIMITER ',' CSV;
CREATE INDEX ref_ip_blocks_ip4r_idx on ref_ip_blocks using gist(iprange);
quit
exit

Deploy app

  1. Create new-app

    oc new-app -e DB_ENDPOINT=${DB_ENDPOINT} \
 -e DB_PORT=5432 \
 -e AWS_REGION=${AWS_REGION} \
 -e DB_USER=iamuser \
 -e DB_NAME=iamdb \
 --strategy=docker https://github.com/fjcloud/ip-finder-api.git

  2. Add secrets to deployment

    oc apply -f - <<EOF
apiVersion: v1
kind: Secret
metadata:
  name: aws-creds
type: Opaque 
stringData: 
  credentials: |
    [default]
    role_arn = ${ROLE}
    web_identity_token_file = /var/run/secrets/openshift/serviceaccount/token
EOF

oc patch deployment ip-finder-api --type=merge -p '{"spec":{"template":{"spec":{"volumes":[{"name":"bound-sa-token","projected":{"sources":[{"serviceAccountToken":{"audience":"openshift","expirationSeconds":3600,"path":"token"}}]}},{"name":"aws-creds","secret":{"secretName":"aws-creds"}}]}}}}'


oc patch deployment ip-finder-api --type='json' -p='[{"op": "add", "path": "/spec/template/spec/containers/0/volumeMounts", "value": [{"name":"bound-sa-token","readOnly":true,"mountPath":"/var/run/secrets/openshift/serviceaccount"},{"name":"aws-creds","mountPath":"/opt/app-root/src/.aws"}]}]'

  3. Expose APP

    oc expose service ip-finder-api
oc patch route ip-finder-api --patch '{"spec":{"tls":{"termination":"edge","insecureEdgeTerminationPolicy":"Redirect"}}}'

  4. Test app

    curl https://$(oc get route ip-finder-api -o jsonpath='{.spec.host}')

    Expected output

    {
 "your_ip": X.X.X.X",
 "countrycode": "FR"
}

Cleanup

  1. Delete resources

    oc delete ns rds-sts-app
aws rds delete-db-instance --db-instance-identifier psql-${CLUSTER_NAME} --region ${AWS_REGION} --skip-final-snapshot
aws ec2 detach-internet-gateway --vpc-id ${VPC_DB} --internet-gateway-id ${IGW}
aws ec2 delete-internet-gateway --internet-gateway-id ${IGW} --region ${AWS_REGION}
aws ec2 delete-subnet --subnet-id ${SUBNET_A} --region ${AWS_REGION}
aws ec2 delete-subnet --subnet-id ${SUBNET_B} --region ${AWS_REGION}
aws ec2 delete-subnet --subnet-id ${SUBNET_C} --region ${AWS_REGION}
aws ec2 delete-vpc --vpc-id ${VPC_DB} --region ${AWS_REGION}
aws rds delete-db-subnet-group --db-subnet-group-name db-group-${CLUSTER_NAME}

  2. Detach the Policies to the Role

    aws iam detach-role-policy \
   --role-name "${CLUSTER_NAME}-rosa-rds-access" \
   --policy-arn $POLICY

  3. Delete the Role

    aws iam delete-role --role-name \
   ${CLUSTER_NAME}-rosa-rds-access

  4. Delete the Policy

    aws iam delete-policy --policy-arn \
   $POLICY

Interested in contributing to these docs?

Collaboration drives progress. Help improve our documentation The Red Hat Way.

GitHub Edit this page
Red Hat logo LinkedIn YouTube Facebook Twitter

Products

Tools

Try, buy & sell

Communicate

About Red Hat

We’re the world’s leading provider of enterprise open source solutions—including Linux, cloud, container, and Kubernetes. We deliver hardened solutions that make it easier for enterprises to work across platforms and environments, from the core datacenter to the network edge.

Subscribe to our newsletter, Red Hat Shares

Sign up now © 2023 Red Hat, Inc.