iam database auth

IAM Database Authentication

Requirements

• kion CLI installed; see Kion access instructions in setup Wiki page
• postgresql client

Notes/Considerations

• The following snippets are written in bash
• Some of the following snippets assume that you are generally using ZSH
• aws-vault configuration for MFA serial assumes an older convention for MFA devices

Steps

1. Install the RDS global bundle locally:

   #!/usr/bin/env bash
   
   echo "Storing latest RDS Global Bundle at ${HOME}/.local"
   mkdir -p "${HOME}/.config/"
   curl -o "${HOME}/.config/global-bundle.pem" https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem -s

2. Configure Kion (~/.kion.yml) with 2 favorites named bfd-db-non-prod and bfd-db-prod that assumes the BFD Database Admin BFD Application Admin Role (temporary workaround) (get the account ID by going to the AWS Console in non-prod and clicking on the navbar on the top-right with your Role name):

   favorites:
     - name: bfd-db-non-prod
       account: <BFD_NON_PROD_ACCOUNT_ID>
       region: us-east-1
       cloud_access_role:
         BFD Application Admin
     - name: bfd-db-prod
       account: <BFD_PROD_ACCOUNT_ID>
       region: us-east-1
       cloud_access_role:
         BFD Application Admin

3. Define an environment variable named CMS_EUA_ID in your shell's configuration (e.g. ~/.zshrc, ~/.bashrc, etc.) corresponding to your EUA ID:

   export CMS_EUA_ID=XXXX

4. Add the following helper functions to your ~/.zshrc:

   ## Requires CMS_EUA_ID is defined, and can easily reside in e.g. the user's .zshrc, as below
   function locust-conn-string {
     if [ "$1" = "" ]; then
       echo 'Empty positional environment argument $1' 1>&2
       echo 'Try again with an environment, e.g. `locust-conn-string test`' 1>&2
       return 1
     fi
   
     if [ "$2" = "writer" ]; then
       # echo '**WARNING** Using the writer endpoint. Please be careful.'
       ENDPOINT_QUERY="DBClusters[].Endpoint"
     else
       # echo 'Defaulting to cluster reader endpoint.'
       ENDPOINT_QUERY="DBClusters[].ReaderEndpoint"
     fi
   
     unset BFD_SEED_ENV BFD_ENV PGHOST PGPORT PGUSER PGPASSWORD PGDATABASE PGSSLROOTCERT PGSSLMODE PGGSSENCMODE
     BFD_SEED_ENV="$(echo "$1" | rg -o "(test|sandbox|prod)" | head -n 1)"
     KION_FAV_NAME=""
     if [[ "$BFD_SEED_ENV" == "prod" || "$BFD_SEED_ENV" == "sandbox" ]]; then
       KION_FAV_NAME="bfd-db-prod"
     else
       KION_FAV_NAME="bfd-db-non-prod"
     fi
     BFD_ENV="$1"
     PGHOST="$(kion run -f "$KION_FAV_NAME" -- aws rds describe-db-clusters --query "${ENDPOINT_QUERY}" --db-cluster-identifier "bfd-${BFD_ENV}-aurora-cluster" --output text)"
     PGPORT=5432
     PGUSER="$CMS_EUA_ID"
     PGDATABASE=fhirdb
     PGSSLROOTCERT="${HOME}/.config/global-bundle.pem"
     PGSSLMODE=verify-full
     PGGSSENCMODE=disable
   
     if PGPASSWORD="$(kion run -f "$KION_FAV_NAME" -- aws rds generate-db-auth-token --hostname "$PGHOST" --port "$PGPORT" --username "$PGUSER")"; then
       echo "dbname=$PGDATABASE user=$PGUSER password=$PGPASSWORD host=$PGHOST port=$PGPORT"
     else
       echo 'Something went wrong.' 1>&2
       return 1
     fi
   }
   
   function rds-env {
     if [ "$1" = "" ]; then
       echo 'Empty positional environment argument $1'
       echo 'Try again with an environment, e.g. `rds-sql test`'
       return 1
     fi
   
     if [ "$2" = "writer" ]; then
       echo '**WARNING** Using the writer endpoint. Please be careful.'
       ENDPOINT_QUERY="DBClusters[].Endpoint"
     else
       echo 'Defaulting to cluster reader endpoint.'
       ENDPOINT_QUERY="DBClusters[].ReaderEndpoint"
     fi
   
     unset BFD_ENV PGHOST PGPORT PGUSER PGPASSWORD PGDATABASE PGSSLROOTCERT PGSSLMODE PGGSSENCMODE
     BFD_SEED_ENV="$(echo "$1" | rg -o "(test|sandbox|prod)" | head -n 1)"
     KION_FAV_NAME=""
     if [[ "$BFD_SEED_ENV" == "prod" || "$BFD_SEED_ENV" == "sandbox" ]]; then
       KION_FAV_NAME="bfd-db-prod"
     else
       KION_FAV_NAME="bfd-db-non-prod"
     fi
     PGHOST="$(kion run -f "$KION_FAV_NAME" -- aws rds describe-db-clusters --query "${ENDPOINT_QUERY}" --db-cluster-identifier "bfd-$1-aurora-cluster" --output text)"
     BFD_DB_ENDPOINT="$PGHOST"
     PGPORT=5432
     PGUSER="$CMS_EUA_ID"
     BFD_DB_USERNAME="$PGUSER"
     PGDATABASE=fhirdb
     PGSSLROOTCERT="${HOME}/.config/global-bundle.pem"
     PGSSLMODE=verify-full
     PGGSSENCMODE=disable
   
     if PGPASSWORD="$(kion run -f "$KION_FAV_NAME" -- aws rds generate-db-auth-token --hostname "$PGHOST" --port "$PGPORT" --username "$PGUSER")"; then
       BFD_DB_PASSWORD="$PGPASSWORD"
       export BFD_ENV PGHOST PGPORT PGUSER PGPASSWORD PGDATABASE PGSSLROOTCERT PGSSLMODE PGGSSENCMODE BFD_DB_ENDPOINT BFD_DB_USERNAME BFD_DB_PASSWORD
       echo "Environment Variables Set for ${PGHOST}"
       return 0
     else
       echo 'Something went wrong.'
       return 1
     fi
   }

5. Restart your terminal session to reload your ~/.zshrc (or other shell equivalent) or exec zsh

6. Attempt to connect to the test database:

   rds-env test
   # You should now be able to connect to test
   pgcli