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!

@Wooble Obviously this was a contrived example. There are any number of reasons you'd want to encrypt traffic between an app server and a database, even with hashed passwords. Unfortunately your comment was not in the slightest bit useful. — Brandon
– Brandon, Commented Mar 12, 2013 at 16:56

Community · Accepted Answer · 2017-04-13 12:33:14Z

8

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.

edited Apr 13, 2017 at 12:33

CommunityBot

11 silver badge

answered Mar 12, 2013 at 14:55

Bruno

123k33 gold badges278 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brandon Over a year ago

Thanks for your useful answer, that directly answered my question!

Bruno Over a year ago

"If a user tries to log in, if SSL is not enabled will the app server transmit the user's password in cleartext", I should also say that I was referring to the PostgreSQL user, which is often different from the application user (the application user may be referring to something stored as data in the database: how this is secure is a different thing).

Community · Accepted Answer · 2017-05-23 12:02:08Z

1

Considering data other than the password. If you use or not i pretty much a security posture issue. How safe do you need your system to be? If the connection is just over your private network then you anyone on that network can listien in. If that is acceptable that dont use SSL, I not enable it. If the connection is ove r internet SSL should be enable.

As @Wooble says. You should never send the password as cleartext in the first place you have a problem. The stanard solution in this case is to store a hash in the database and only send the hash for validation.

Here is som link about the rails part

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Mar 12, 2013 at 13:50

Stefan Rasmusson

5,6133 gold badges25 silver badges48 bronze badges

Collectives™ on Stack Overflow

TCP Connections to Postgres Secure? SSL Required?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related