TCP Connections to Postgres Secure? SSL Required?

Question

Good morning,

I was going through the Postgresql configuration files, and recently noticed that there is an ssl option. I was wondering when this is required.

Say if you have an app server and a database server - not running inside a private network. If a user tries to log in, if SSL is not enabled will the app server transmit the user's password in cleartext to the database when looking up if it is a valid username/password?

What is standard practice here? Should I be setting up my DB to use SSL?

If that is the case, is there any difference in the connection settings in config/database.yml in my Rails app?

Thanks!

Answer 1

Like for other protocols, using SSL/TLS for PostgreSQL allows you to secure the connection between the client and the server. Whether you need it depends on your network environment.

Without SSL/TLS the traffic between the client and the server will be visible by an eavesdropper: all the queries and responses, and possibly the password depending on how you've configured your pg_hba.conf (whether the client is using md5 or a plaintext password).

As far as I'm aware, it's the server that requests MD5 or plaintext password authentication, so an active Man-In-The-Middle attacker could certainly downgrade that and get your password anyway, when not using SSL/TLS.

A well-configured SSL/TLS connection should allow you to prevent eavesdropping and MITM attacks, against both passwords and data.

You can require SSL to be used on the server side using sslhost in pg_hba.conf, but that's only part of the problem. Ultimately, just like for web servers, it's up to the client to verify that SSL is used at all, and that it's used with the right server.

Table 31-1 in the libpq documentation summarises the levels of protection you get.

Essentially:

• if you think you have a reason to use SSL, disable, allow and prefer are useless (don't take "No" or "Maybe" if you want security).
• require is barely useful, since it doesn't verify the identity of the remote server at all.
• verify-ca doesn't verify the host name, which makes it vulnerable to MITM attacks.

The one you'll want if security matters to you is verify-full.

These SSL mode names are set by libpq. Other clients might not use the same (e.g. pure Ruby implementation or JDBC).

As far as I can see, ruby-pg relies on libpq. Unfortunately, it only lists "disable|allow|prefer|require" for its sslmode. Perhaps verify-full might work too if it's passed directly. However, there would also need a way to configure the CA certificates.