Setting Up Pgcli TLS Configuration for Secure PostgreSQL Connections

Pgcli supports TLS for secure connections to PostgreSQL. Configuring it correctly prevents man-in-the-middle attacks and ensures data integrity. Here’s how to set up Pgcli TLS configuration from start to finish.

1. Verify PostgreSQL TLS Settings
Pgcli depends on the server’s TLS configuration. On the PostgreSQL server, confirm that ssl = on in postgresql.conf and that valid certificate and key files are set with ssl_cert_file, ssl_key_file, and ssl_ca_file. Reload or restart the server for changes to take effect.

2. Install Pgcli
Use pip:

pip install pgcli

3. Use TLS Options When Connecting
Pgcli accepts standard libpq connection parameters. You can pass them via the command line or environment variables.

Example with full TLS verification:

pgcli "postgresql://user:password@host:5432/dbname?sslmode=verify-full&sslcert=/path/client.crt&sslkey=/path/client.key&sslrootcert=/path/ca.crt"

Key Parameters:

• sslmode: values include disable, require, verify-ca, and verify-full. For production, use verify-full.
• sslcert: path to client certificate.
• sslkey: path to client private key.
• sslrootcert: path to CA certificate.

4. Troubleshooting TLS in Pgcli
If the connection fails:

• Ensure file permissions restrict access to your key file (chmod 600).
• Match CN in the server certificate to the hostname when using verify-full.
• Check that certificates are not expired.

5. Automating TLS Connections
For repeated use, set the PGSSL* environment variables:

export PGSSLMODE=verify-full
export PGSSLCERT=/path/client.crt
export PGSSLKEY=/path/client.key
export PGSSLROOTCERT=/path/ca.crt

Then run:

pgcli -h host -p 5432 -U user dbname

With Pgcli TLS configuration done properly, your queries run in a secure channel, every time. No surprises. No leaks.

See it live in minutes with hoop.dev — launch a secure PostgreSQL instance, connect with Pgcli over TLS, and verify your setup without leaving your browser.