Configuring TLS for Pgcli Connections to PostgreSQL

Pgcli supports TLS, but it will not configure itself. To protect data in transit, you must set the right flags and point to the correct certificates. Missteps here can expose queries and results to interception. This guide walks through Pgcli TLS configuration step by step.

Why TLS Matters for Pgcli

TLS encrypts communication between Pgcli and PostgreSQL. Without it, packets travel in plain text. With it, you prevent attacks like packet sniffing and man-in-the-middle. PostgreSQL supports SSL/TLS natively; Pgcli builds on that support.

Preparing Certificates

You need three files:

• server.crt: the server’s public certificate.
• server.key: the server’s private key.
• root.crt: root CA certificate to verify the server.

Keep server.key secure. Set file permissions to limit access.

Configuring PostgreSQL for TLS

Edit postgresql.conf:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'

Restart PostgreSQL after changes. Verify with:

SHOW ssl;

It should return on.

Connecting with Pgcli Using TLS

Pgcli uses the standard libpq connection parameters. Pass SSL options directly in the connection string:

pgcli "postgresql://user@hostname:port/dbname?sslmode=verify-full&sslrootcert=/path/to/root.crt"

Key sslmode values:

• require: forces SSL but skips verification.
• verify-ca: verifies certificate authority.
• verify-full: verifies CA and hostname. Use this in production.

You can also export environment variables:

export PGSSLMODE=verify-full
export PGSSLROOTCERT=/path/to/root.crt
pgcli -h hostname -U user dbname

Testing the Connection

Run:

\conninfo

Pgcli will report the SSL mode in use. Confirm it matches your intent.

Common Issues

• Invalid certificate path: Check file locations and permissions.
• Hostname mismatch: Ensure the certificate’s CN or SAN matches the server’s DNS name.
• Key permissions: PostgreSQL will refuse to start if server.key is world-readable.

TLS is not optional when security matters. Set it once, check it twice, and keep certificates updated. A misconfigured TLS setup can break trust instantly.

See TLS in action with fast PostgreSQL connections and secure workflows. Visit hoop.dev to connect, configure, and go live in minutes.