Connect to DigitalOcean Managed PostgreSQL from your Mac

What you need

• The cluster’s connection details from the DigitalOcean control panel, including the doadmin password (or another user’s).
• Your Mac’s IP address in the cluster’s trusted sources, if you’ve set any.
• For certificate checks on a Standard Edition cluster, the cluster’s CA certificate.

Find your connection details

Go to the Databases page, select the cluster, and scroll to the Connection Details section on its Overview page. There you choose:

• Public network or VPC network. Only resources in the same VPC network can use the private host name, so from your Mac choose Public network.
• The database or pool, and the user.
• verify-full, a switch that makes the details check the server’s certificate (see TLS).
• The format: Connection parameters (separate fields), Connection string, or Flags (a psql command).

The password is hidden until you click show, or Copy to copy the details with it. Clusters use port 25060; the default user is doadmin and the default database defaultdb.

Trusted sources limit which addresses can connect. When you add trusted sources, My current IP address (the Quick Add option) adds your machine’s current IP address. Trusted sources can’t hold IPv6 addresses.

Connection pools. DigitalOcean runs PgBouncer for the pools you create. A pool has its own connection details: the same host, port 25061, and the pool name in place of the database name. Pools default to transaction mode (session and statement modes also exist), and you can’t change a pool’s mode after you create it. DigitalOcean warns that pg_dump fails through a transaction-mode pool; connect to the cluster directly for backups.

Connection string

Direct to the cluster:

postgresql://doadmin:<password>@<host>:25060/defaultdb?sslmode=require

Through a pool named <pool>:

postgresql://doadmin:<password>@<host>:25061/<pool>?sslmode=require

See PostgreSQL connection strings and special characters in passwords.

TLS

Connection details use sslmode=require unless you turn on the verify-full switch; require encrypts but doesn’t check the server’s certificate. What verify-full needs depends on the cluster’s edition:

• Standard Edition: click Download CA certificate in Connection Details and point sslrootcert at that file.
• Advanced Edition: the certificate is checked against your system’s trusted roots, so there’s nothing to download. With psql, add sslrootcert=system, which needs libpq 16 or later (older versions treat system as a file name and fail).

Connect with Inlet

1. In Connection Details, choose Public network and Connection string, copy it, and paste it into Inlet: Inlet fills in the connection form. Or choose New Connection and copy the separate fields across.
2. Under TLS, choose verify-full. On a Standard Edition cluster, select the downloaded CA certificate as the CA file. On Advanced Edition, leave the CA file empty: Inlet checks against the certificates macOS trusts.
3. Keep the password in the Keychain, or have Inlet ask each time.
4. Tag the environment. A production connection opens read-only, with writes refused by the server until you unlock it for ten minutes.

Connect to port 25060, not a transaction-mode pool, for production. Inlet’s read-only mode is a session setting. Through a transaction pool each transaction can run on a different server connection, and PgBouncer doesn’t reset session state in transaction mode, so the setting isn’t guaranteed to hold. A session-mode pool or the direct port keeps it. To make a user read-only however it connects, set it on the server:

ALTER ROLE <user> SET default_transaction_read_only = on;

That’s a default a session can still switch off; for a hard limit, give that user only SELECT privileges and keep doadmin for changes.

Connect from the command line

DigitalOcean notes that the Flags command in the control panel passes --set=sslmode=require, which doesn’t configure TLS in psql; set PGSSLMODE instead:

PGPASSWORD=<password> PGSSLMODE=require psql -U doadmin -h <host> -p 25060 -d defaultdb

With certificate checks on a Standard Edition cluster:

psql "postgresql://doadmin:<password>@<host>:25060/defaultdb?sslmode=verify-full&sslrootcert=$HOME/Downloads/ca-certificate.crt"

Use the path and name of the file you downloaded. On Advanced Edition, use sslrootcert=system instead. Homebrew’s libpq package includes a current psql (/opt/homebrew/opt/libpq/bin/psql).

Troubleshooting

• Timed out or connection refused: your IP isn’t in the trusted sources, or you copied the VPC network address. Use Quick Add and the Public network details.
• password authentication failed: check the user and password in Connection Details. Through a pool, the database name must be the pool’s name.
• pg_dump errors through a pool: connect to port 25060 instead.
• Too many connections: create a connection pool for your application, or close idle sessions.
• sslrootcert=system fails on an Advanced Edition cluster: your psql is older than 16 and treats system as a file name. Use a newer one (Homebrew’s libpq is 18).