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 includes the default
prefer. The Postgres docs have a great table explaining this:
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.
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:
Or use environment variables.
Libraries for most programming languages have options as well.
PG.connect(sslmode: "verify-full", sslrootcert: "ca.pem")
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:
|Google Cloud SQL||In Account||View|
|Digital Ocean||In Account||View|
There’s no way to use
verify-full with Heroku Postgres, so use caution when connecting from networks you don't fully trust. Instead of
heroku pg:psql, use:
heroku run psql \$DATABASE_URL
This securely connects to a dyno before connecting to the database.
If you use PgBouncer, set up secure connections for it as well.
Hopefully this helps you understand connection security a bit better.
- August 2019: Added Digital Ocean