TLS for pgcli is not just a nice-to-have. It’s the line between safe queries and quiet disaster. Configuring it right means every byte is encrypted, every credential guarded, and every session trusted. No middleman can watch your traffic. No stray packet travels unprotected.
pgcli makes querying Postgres fast and friendly, but out of the box it won’t enforce TLS unless you tell it to. Start by making sure you have the right certificates: a server certificate signed by a trusted authority, the matching private key, and—if you want client-side validation—a CA certificate that the server trusts. Keep them in a secure path, with permissions locked down.
Here’s a clean, tested way to set it up:
- Place your
server.crt, server.key, and root.crt in a dedicated .postgresql directory under your home folder. - Set the file permissions so that only your user can read the key:
chmod 600 ~/.postgresql/server.key
- When connecting with
pgcli, use the --sslmode flag. For the highest security without skipping checks, pick:
pgcli "postgres://user@host:port/dbname?sslmode=verify-full&sslrootcert=/path/to/root.crt"
- If the certificate hostname matches exactly, the handshake will complete. If not,
pgcli will refuse the connection. That’s the point.
sslmode=verify-full forces TLS encryption and validates the server identity. Lower settings like require or verify-ca weaken the guarantee. For production, don’t settle—use full verification.
Test your connection with verbose logging on both server and client. Make sure renegotiation and protocol settings are aligned. Update certificates before they expire. Automate renewal where possible. A broken TLS chain will block all traffic, and the fix is harder under pressure.
Once configured, every query over pgcli will be as secure as your certificates. You’ll have speed, autocomplete, and encryption in one smooth workflow.
Want to see this kind of secure Postgres access in action without spending hours setting it up? Spin it up with hoop.dev and watch it work in minutes.