Connect to Amazon RDS for PostgreSQL from your Mac

What you need

• The instance’s endpoint and port, and the master user name and password (or another database user).
• A network path from your Mac: either the instance is publicly accessible and its security group allows your IP address, or you reach it through a host inside the VPC.
• The RDS certificate bundle, global-bundle.pem, if you want to check the server’s certificate (you should).

Find your connection details

1. Open the Amazon RDS console and choose Databases.
2. Choose the instance. On the Connectivity & security tab, copy the Endpoint and note the Port (5432 unless someone changed it). The same tab shows Publicly accessible: Yes or No.
3. On the Configuration tab, the Master username is the user (postgres by default), and DB name is the database created with the instance. A dash means none was created; connect to postgres.

From the AWS CLI, aws rds describe-db-instances returns the same details.

Reaching the instance. An instance that isn’t publicly accessible can only be reached from inside its VPC. AWS’s advice for that case is a VPN or AWS Direct Connect, or failing those, a bastion host: an EC2 instance in the same VPC that you SSH to and tunnel through. Either way, the instance’s VPC security group needs an inbound rule for the port: for a public instance, a rule with your IP as the source (the console’s My IP option fills it in); for a bastion, the bastion’s address.

RDS Proxy. If your apps use a proxy (endpoints look like <proxy>.proxy-<id>.<region>.rds.amazonaws.com), note that AWS doesn’t let a proxy be publicly accessible, so from a Mac you connect to the instance endpoint, or to the proxy through a host in the VPC.

Connection string

postgresql://<user>:<password>@<instance>.<id>.<region>.rds.amazonaws.com:5432/postgres?sslmode=verify-full&sslrootcert=/path/to/global-bundle.pem

Percent-encode special characters in the password; see special characters in passwords.

TLS

RDS for PostgreSQL supports TLS on every instance. The rds.force_ssl parameter decides whether it’s required: it’s on by default for PostgreSQL 15 and later, and off by default for 14 and older. To change it, use a custom DB parameter group. With it on, a connection without TLS is refused with no pg_hba.conf entry for host …, SSL off.

The server certificate is signed by an Amazon RDS certificate authority (by default rds-ca-rsa2048-g1), which macOS doesn’t trust out of the box. Download the bundle that covers every commercial Region:

curl -O https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem

Then use sslmode=verify-full with that file. The certificate names the instance endpoint, so connect with the endpoint, not an IP address or your own DNS alias.

Connect with Inlet

1. Choose New Connection and enter the endpoint, port, user and database, or paste a postgresql:// URL and Inlet fills in the form.
2. Under TLS, choose verify-full and select global-bundle.pem as the CA file.
3. Private instance? Turn on the SSH tunnel and enter the bastion host. Inlet uses your Mac’s own ssh, so a Host entry in ~/.ssh/config, the SSH agent and the 1Password SSH agent all work. Keep the instance endpoint as the database host: the tunnel’s far end connects to it from inside the VPC.
4. Save the password in the Keychain, or have Inlet ask each time.
5. Tag the connection’s environment. A production connection opens read-only, so the server refuses writes until you unlock it for ten minutes.

IAM database authentication. If the user signs in with IAM, the password is a token that’s valid for 15 minutes. Generate one (see below), choose to be asked for the password each time, and paste the token when Inlet asks. The token only matters while signing in: the session stays open after it expires. IAM authentication needs TLS.

Connect from the command line

AWS’s example, with certificate checks:

psql "host=<instance>.<id>.<region>.rds.amazonaws.com port=5432 dbname=postgres user=<user> sslrootcert=global-bundle.pem sslmode=verify-full"

With IAM authentication, put the token in PGPASSWORD:

export RDSHOST="<instance>.<id>.<region>.rds.amazonaws.com"
export PGPASSWORD="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --region <region> --username <user>)"
psql "host=$RDSHOST port=5432 sslmode=verify-full sslrootcert=global-bundle.pem dbname=postgres user=<user>"

Through a bastion, forward a local port in one terminal, then connect to it from another:

ssh -N -L 5433:<instance>.<id>.<region>.rds.amazonaws.com:5432 ec2-user@<bastion>

psql "host=<instance>.<id>.<region>.rds.amazonaws.com hostaddr=127.0.0.1 port=5433 dbname=postgres user=<user> sslmode=verify-full sslrootcert=global-bundle.pem"

hostaddr sends the connection to the tunnel, while host stays the endpoint, which is the name libpq checks the certificate against. With host=localhost instead, verify-full would fail, because the certificate doesn’t name localhost.

Troubleshooting

• Connection timed out: AWS’s most common case. Check you used the endpoint and port, that Publicly accessible is Yes, and that the security group has an inbound rule for your current IP. Some office firewalls block port 5432. See also connection refused.
• A local PostgreSQL in the way: AWS notes that a PostgreSQL running on your own Mac on port 5432 can stop the connection working. Stop it, or use another local port for tunnels.
• no pg_hba.conf entry … SSL off: rds.force_ssl is on and your client didn’t use TLS. Turn TLS on.
• FATAL: database "<name>" does not exist: connect to the postgres database.
• password authentication failed: check the user and password. With IAM, the token may have expired, or was made for a different host, port or user. AWS also notes that once a user has the rds_iam role, it must sign in with IAM.