Connecting Django to RDS via pgbouncer using IAM auth

Setting up the infra

We primarily use a django backend with RDS postgres in a kubernetes cluster for production environments. We were trying to setup an environment which would be HIPAA compliant and secure as well, as well as keep complexity to a minimum in the backend layer. We decided to go with IAM authentication to connect to RDS for the django application.

The tricky part was to figure out how we would add pgbouncer between RDS and django to manage connection pooling issues.

We setup a kubernetes service account and associated an IAM role to it that'd help us generate RDS auth tokens that will allow us to connect to the database.

In short, you'd have to create an OIDC provider for your cluster, create an IAM role and attach the relevant policies to that role, and associate the role to that service account. If you use other AWS services in your backend, you could attach relevant policies for those services that'd allow you to access those without adding access keys. If you are provisioning infra resources via terraform, you will run into issues. The OIDC provider configuration will be missing if you don't explicitly assign it while provisioning through terraform. It gets automatically generated/assigned if you do it via the AWS console.

A whole guide to do the above is here.

Application configuration

We bundle and run pgbouncer in the pod running the django service. You'd need a script that will call and generate IAM tokens that'll connect pgbouncer to the RDS instance. Also pgbouncer has a max size of 160 bytes for passwords, but the passwords generated by RDS IAM are around 800-900 bytes.

We forked the pgbouncer repo and updated the size of the passwords to 2048 bytes. The forked repo is compiled and installed while building the docker image.

[databases]
{{PGBOUNCER_NAME}} = host={{POSTGRES_HOST}} port={{POSTGRES_PORT}} dbname={{POSTGRES_DB}}

[pgbouncer]
pool_mode = transaction
listen_port = {{PGBOUNCER_PORT}}
listen_addr = {{PGBOUNCER_HOST}}
auth_type = trust
auth_file = users.txt
pidfile = /var/run/pgbouncer/pgbouncer.pid
logfile = /var/run/pgbouncer/pgbouncer.log
max_client_conn = 4000
default_pool_size = 20
reserve_pool_size = 5
server_tls_sslmode = verify-full
server_tls_ca_file = rds-ca-2019-root.pem
syslog = 1

pgbouncer.template.ini

Copy pgbouncer.template.ini, pgbouncer.sh and pgbouncer-update.sh, rds-ca-2019-root.pem to the filesystem of the container. We used two scripts pgbouncer.sh, pgbouncer-update.sh in the entrypoint, the former to set the database credentials when the container starts, while the latter is ran every 10 minutes as a cron to update the credentials.

.....
COPY ./pgbouncer.template.ini /pgbouncer.template.ini
COPY /pgbouncer.template.ini /pgbouncer.ini
COPY ./rds-ca-2019-root.pem /rds-ca-2019-root.pem
COPY ./scripts/pgbouncer.sh /pgbouncer.sh
COPY ./scripts/pgbouncer-update.sh /pgbouncer-update.sh

RUN sed -i -e 's/\r//' /pgbouncer.sh
RUN sed -i -e 's/\r//' /pgbouncer-update.sh

RUN chown django /pgbouncer.template.ini
RUN chown django /pgbouncer.ini
RUN chmod +x /pgbouncer.sh
RUN chown django /pgbouncer.sh

RUN chmod +x /pgbouncer-update.sh
.....

Dockerfile

....
echo "*/10 * * * * bash /pgbouncer-update.sh >> /app/cron.log 2>&1
# This extra line makes it a valid cron" > scheduler.txt

crontab scheduler.txt
nohup cron -f &
sh /pgbouncer.sh
.....

entrypoint

#!/bin/bash
PASSWORD=`echo $(aws rds generate-db-auth-token  \
  --hostname $POSTGRES_HOST \
  --port 5432 \
  --username $POSTGRES_USER \
  --region $DJANGO_AWS_REGION)`


# pgbouncer.ini
sed -e "s/{{POSTGRES_DB}}/"$POSTGRES_DB"/" \
    -e "s/{{POSTGRES_USER}}/"$POSTGRES_USER"/" \
    -e "s/{{POSTGRES_HOST}}/"$POSTGRES_HOST"/" \
    -e "s/{{POSTGRES_PORT}}/"$POSTGRES_PORT"/" \
    -e "s/{{PGBOUNCER_NAME}}/"$PGBOUNCER_NAME"/" \
    -e "s/{{PGBOUNCER_PORT}}/"$PGBOUNCER_PORT"/" \
    -e "s/{{PGBOUNCER_HOST}}/"$PGBOUNCER_HOST"/" \
    pgbouncer.template.ini > pgbouncer.ini


# users.txt
sed -e "s|{{POSTGRES_PASSWORD}}|"$(echo $PASSWORD | sed -e 's/\\/\\\\/g; s/\//\\\//g; s/&/\\\&/g')"|" \
    -e "s/{{POSTGRES_USER}}/"$POSTGRES_USER"/" \
    users.template.txt > users.txt

# run as daemon
pgbouncer -d -v pgbouncer.ini -u django

pgbouncer.sh

#!/bin/bash
SHELL=/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

PASSWORD=`echo $(aws rds generate-db-auth-token --hostname $POSTGRES_HOST --port 5432 --username $POSTGRES_USER --region $DJANGO_AWS_REGION)`


# users.txt
sed -e "s|{{POSTGRES_PASSWORD}}|"$(echo $PASSWORD | sed -e 's/\\/\\\\/g; s/\//\\\//g; s/&/\\\&/g')"|" \
    -e "s/{{POSTGRES_USER}}/"$POSTGRES_USER"/" \
    /app/users.template.txt > /app/users.txt

pgbouncer-update.sh

The users.template.txt referenced in the above scripts

"{{POSTGRES_USER}}" "{{POSTGRES_PASSWORD}}"

users.template.txt

Hope this helps you out. We ran into some issues while trying to setup/run the cron job. You may have to grant access/permissions to the cron folders in the Dockerfile for the default user of your container.

Credit to Makarand for figuring some of the stuff here.

You might also like...