Connect to Amazon Aurora PostgreSQL from your Mac

What you need

• The cluster’s endpoint and port, and a database user and password (the master user is postgres unless you chose another name).
• A network path into the cluster’s VPC: a public endpoint with a security group rule for your IP, or a host inside the VPC to tunnel through.
• The RDS certificate bundle, global-bundle.pem, to check the server’s certificate.

Find your connection details

Open the Amazon RDS console, choose Databases, and choose the name of the cluster (not one of its instances). The cluster’s details page lists its cluster, reader and custom endpoints; each instance’s own page shows its instance endpoint. aws rds describe-db-clusters lists them too.

The port is 5432 unless you picked another when you created the cluster. For Aurora Serverless, AWS says to connect to the database endpoint shown on the Connectivity & security tab.

Prefer the cluster and reader endpoints over instance endpoints: after a failover they move to the right instances by themselves. The reader endpoint balances connections, not queries, and only refuses writes when the cluster has at least one Aurora Replica; with no replicas it connects to the primary.

Reaching the cluster. Aurora clusters always live in a VPC. From outside it, you need the cluster’s public endpoint address and a VPC security group with an inbound rule for your IP. For a private cluster, AWS recommends a VPN or AWS Direct Connect, or a bastion host (an EC2 instance in the VPC) to tunnel through. An RDS Proxy can’t be made publicly accessible, so from a Mac you reach a proxy only from inside the VPC.

Connection string

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

For read-only work, swap in the cluster-ro- reader endpoint. See PostgreSQL connection strings and special characters in passwords.

TLS

Aurora PostgreSQL supports TLS in every Region. Whether it’s required depends on the rds.force_ssl cluster parameter:

• Aurora PostgreSQL 17 and later: on by default.
• Aurora PostgreSQL 16 and older: off by default.

A major version upgrade from 16 or earlier to 17 or later flips the default to on, which can break clients that weren’t using TLS. With it on, a client without TLS gets no pg_hba.conf entry for host …, SSL off.

The server certificate comes from an Amazon RDS certificate authority, which macOS doesn’t trust by default. Download the bundle for all commercial Regions and use sslmode=verify-full with it:

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

Connect with Inlet

1. Choose New Connection and enter the cluster 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. For a private cluster, turn on the SSH tunnel and give the bastion. Inlet runs your Mac’s own ssh, so ~/.ssh/config, the SSH agent and the 1Password SSH agent work as they do in Terminal. Keep the cluster endpoint as the database host.
4. Keep the password in the Keychain, or have Inlet ask every time.
5. Tag the environment. A connection tagged production opens read-only: the server refuses writes until you unlock it, for ten minutes at a time. For browsing production you can also connect to the reader endpoint, which can’t write at all when the cluster has replicas.

With IAM database authentication, the password is a token from aws rds generate-db-auth-token, valid for 15 minutes. Choose to be asked for the password, and paste a fresh token each time you connect; an open session isn’t affected when the token expires.

Connect from the command line

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

With IAM authentication:

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

Through a bastion: forward a port with ssh -N -L 5433:<cluster-endpoint>:5432 ec2-user@<bastion>, then connect with host=<cluster-endpoint> hostaddr=127.0.0.1 port=5433, so libpq still checks the certificate against the endpoint name.

Troubleshooting

• Timeouts or connection refused: AWS’s list of usual causes for a new cluster starts with a VPC security group that doesn’t allow your address, and a firewall that blocks the port. (Its third, an incomplete IAM set-up, shows up as a sign-in failure instead.)
• no pg_hba.conf entry … SSL off: rds.force_ssl is on (the default from version 17). Turn TLS on in your client.
• cannot execute … in a read-only transaction when you didn’t expect it: you’re on the reader endpoint or an instance endpoint of a replica. Use the cluster endpoint to write.
• password authentication failed: check the user and password; with IAM, generate a new token for the exact host, port and user you connect with.
• Too many connections: each instance has its own limit; close idle sessions, or put RDS Proxy in front of your app (not your Mac).