Postgres SSLMODE Explained

When you connect to a database, Postgres uses the sslmode parameter to determine the security of the connection. There are many options, so here’s an analogy to web security:

All the other options fall somewhere in between, and by design, make less guarantees of security than HTTPS in your browser does.

This connection is not private

This includes the default prefer. The Postgres docs have a great table explaining this:

Table

Other modes like require are still useful in protecting against passive attacks (sniffing), but are vulnerable to active attacks that can compromise your credentials. Tarjei Husøy created postgres-mitm to demonstrate this.

Defense

The best way to protect a database is to limit inbound traffic. Require a VPN or SSH tunneling through a bastion host to connect. This ensures connections are always secure, and even if database credentials are compromised, an attacker won’t be able to access the database.

If this is not feasible, always use verify-full. This includes from code, psql, SQL clients, and other tools like pgsync and pgslice.

You can specify sslmode in the connection URI:

postgresql://user:pass@host/dbname?sslmode=verify-full&sslrootcert=ca.pem

Or use environment variables.

PGSSLMODE=verify-full PGSSLROOTCERT=ca.pem

Libraries for most programming languages have options as well.

PG.connect(sslmode: "verify-full", sslrootcert: "ca.pem")

Certificates

To verify an SSL/TLS certificate, the client checks it against a root certificate. Your browser ships with root certificates to verify HTTPS websites. Postgres doesn’t come with any root certificates, so to use verify-full, you must specify one.

Here are root certificates for a number of providers:

Provider Certificate Docs
Amazon RDS Download View
Azure Database Download View
Crunchy Bridge In Account View
Digital Ocean In Account View
Google Cloud SQL In Account View
Heroku Download View
Supabase In Account View

If you use PgBouncer, set up secure connections for it as well.

Conclusion

Hopefully this helps you understand connection security a bit better.

Updates

Published November 18, 2018


You might also enjoy

Securing Database Traffic with PgBouncer and Amazon RDS

16 New ML Gems for Ruby

TensorFlow Object Detection in PHP


All code examples are public domain.
Use them however you’d like (licensed under CC0).