Accessing databases via port forwarding

Our databases (RDS Postgres and Redshift) are both locked away in VPC not allowing random IP inbound connections. To access them we need to use the AWS bastion host to 'port forward' into them.

There is a useful helper script in gf-infrastructure (https://github.com/GoodFit-io/gf-infrastructure/blob/main/hack/port-forward.sh).

# This is my helper to login to the correct AWS env (it sets AWS_PROFILE)
awss gf-prod
./port-foward.sh prod postgres

This, when it works will lookup the correct database hostname, open a port via AWS systems manager and expose it locally to you. You then connect to the LOCAL port, e.g. In this case 55140

If you run it on gitbash on windows, there is a need to disable auto path conversion, that essentially prefixes any string that starts with a / with a directory path.

To avoid it use MSYS_NO_PATHCONV=1

MSYS_NO_PATHCONV=1 AWS_PROFILE=gf-dev ./hack/port-forward.sh dev postgres

Eg:

psql -h localhost -p 55140 -U <username> sourcing

Never use a shared username, always use your own username, and ask for one if you don't have one.

Note that the local port is different for each db type and env combination, this prevents connecting to the wrong one by mistake. See https://github.com/GoodFit-io/gf-infrastructure/blob/main/hack/port-forward.sh#L49

Note that you need to have the session manager plugin installed locally https://docs.aws.amazon.com/systems-manager/latest/userguide/session-manager-working-with-install-plugin.html

Helpers

You can add this to your shell:

function gfdb(){
 cd {your path to gf-infra}/gf-infrastructure
 AWS_PROFILE=gf-$1 ./hack/port-forward.sh $1 $2
}

This means you can do:

gfdb dev postgres

Helpers​

Helpers