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 a favorite named bfd-db that assumes the BFD Database Admin Role:

   favorites:
     - name: bfd-db
       account: <BFD_ACCOUNT_ID>
       region: us-east-1
       cloud_access_role:
         BFD Database Administrator

3. Run the following Bash snippet in your terminal to install helper functions for connecting to the databases:

   ## Requires CMS_EUA_ID is defined, and can easily reside in e.g. the user's .zshrc, as below
   cat <<"EOF" >> "${HOME}/.zshrc"
   function rds-sql {
     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|prod-sbx|prod)" | head -n 1)"
     PGHOST="$(aws rds describe-db-clusters --query "${ENDPOINT_QUERY}" --db-cluster-identifier "bfd-$1-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 bfd-db -- aws rds generate-db-auth-token --hostname "$PGHOST" --port "$PGPORT" --username "$PGUSER")"; then
       export BFD_ENV PGHOST PGPORT PGUSER PGPASSWORD PGDATABASE PGSSLROOTCERT PGSSLMODE PGGSSENCMODE
       echo "Environment Variables Set for ${PGHOST}"
     else
       echo 'Something went wrong.'
       return 1
     fi
   }
   
   function locust-conn-string {
     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|prod-sbx|prod)" | head -n 1)"
     PGHOST="$(aws rds describe-db-clusters --query "${ENDPOINT_QUERY}" --db-cluster-identifier "bfd-$1-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 bfd-db -- aws rds generate-db-auth-token --hostname "$PGHOST" --port "$PGPORT" --username "$PGUSER")"; then
       echo "dbname=$PGDATABASE user=$PGUSER password=$PGPASSWORD host=$PGHOST port=$PGPORT"
       return 0
     else
       echo 'Something went wrong.'
       return 1
     fi
   }
   EOF

4. Restart your terminal session to reload your ~/.zshrc or exec zsh

5. Attempt to connect to the test database:

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