postgres rds debeaver session w/ IAM and ssh tunneling

db.sh

# useful script if you happen to have any rds instances on private subnets
# with a bastion host to tunnel into And IAM authentication
# Mostly inspired from this article on the AWS blog:
# https://aws.amazon.com/blogs/database/using-iam-authentication-to-connect-with-pgadmin-amazon-aurora-postgresql-or-amazon-rds-for-postgresql/
# Thanks to @mjreed-turner for teaching me all things Linux, as usual

export AWS_DEFAULT_REGION=us-east-1
export AWS_PROFILE=your-aws-creds-profile

export BASTION_EC2_INSTANCE=i-12345678

export RDSHOST="your-db-endpoint-0.aaaaaaaaaaaa.us-east-1.rds.amazonaws.com"

# start the bastion host
aws ec2 start-instances --instance-ids $BASTION_EC2_INSTANCE
aws ec2 wait instance-running --instance-ids $BASTION_EC2_INSTANCE
echo "EC2 instance is now running"

# gets the creds from IAM for the user for your db, uses them in the session
export PGPASSWORD="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --username app)"

export DBNAME=yourdbname

export IAMUSER=youriamuser

LOCAL_PORT=9999

echo "connecting to ssh tunnel"
sleep 2
# run an ssh session in the background without TTY, save pid
ssh -N -n -i ~/.ssh/yourprivatekey -L $LOCAL_PORT:$RDSHOST:5432 [email protected] &
pid=$!

/Applications/DBeaver.app/Contents/MacOS/dbeaver -con \
"driver=PostgreSQL|prop.ssl=false|prop.sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory|host=localhost|port=$LOCAL_PORT|database=$DBNAME|user=$IAMUSER|password=$PGPASSWORD"

# stop the bastion host
aws ec2 stop-instances --instance-ids $BASTION_EC2_INSTANCE
aws ec2 wait instance-stopped --instance-ids $BASTION_EC2_INSTANCE
echo "EC2 instance is now stopped"


# kills the ssh session on dbeaver close
kill $pid