1

I have a sensitive application with app server and db on separate machines, and in the case of the slave db, in separate data-centers.

Although I believe my postgresqls are configured to always use ssl I need a way to double-check this.

Is there some simple way to check that all client connections are indeed being forced to use ssl?

  • SSL requires a certificate in both sides, server and client, do not install client certificate and try to connect. – McNets Dec 27 '18 at 19:49
  • 1
    Possible duplicate of How to examine PostgreSQL server's SSL certificate? – McNets Dec 27 '18 at 19:52
  • Removing the ssl certificatie and observing doesn't guarantee that when it's in place it's actually going to use the ssl - there still could be some gotcha. I'd like some rather direct way of seeing that all live connections are ssl. – David Simic Dec 27 '18 at 20:12
2

Non-SSL connections can be disabled through pg_hba.conf.

For instance, it may start like this:

# allow local connections through Unix domain sockets
local  all  all  peer

# allow non-encrypted local TCP connections with passwords
host       all  all  127.0.0.1/32   md5
host       all  all  ::1/128        md5

# reject any other non-encrypted TCP connection
hostnossl  all  all  0.0.0.0/0     reject
hostnossl  all  all  ::/0          reject

# other rules...

The rules are tested in order and until the first match, so any rule after these will have no effect when one of these matches.


At runtime, to check which sessions are encrypted, there's the pg_stat_ssl system view (since PostgreSQL 9.5). Its pid column is a reference to pg_stat_activity that holds the other bits of information that might be relevant to identifying the connection such as usename, datname, client_addr..., so you might use this query, for instance:

SELECT datname,usename, ssl, client_addr 
  FROM pg_stat_ssl
  JOIN pg_stat_activity
    ON pg_stat_ssl.pid = pg_stat_activity.pid;
  • OK -- but my question is about double checking. Assume I did this. How do I check this is actually working as advertised. Is there some way to list all connections and show type (ssl, non-ssl)? Something higher level than looking at packets, but still a solid consistency check that the config is working as advertised. – David Simic Dec 28 '18 at 15:03
  • @DavidSimic: see edit. – Daniel Vérité Dec 28 '18 at 20:38
  • For completeness - could you please add this example join query to your answer: "SELECT datname,usename, ssl, client_addr FROM pg_stat_ssl INNER JOIN pg_stat_activity on pg_stat_ssl.pid = pg_stat_activity.pid;" or something similar -- I would add this edit myself but I do not have the privileges. Would be happy to accept once this is done. – David Simic Dec 31 '18 at 0:26
0

You can consult pg_stat_ssl to make sure clients are connected over SSL. Although if you think pg_hba is buggy, I don't know why you would have any more faith in pg_stat_ssl. But note that this only shows the client is connected over SSL. There is no way to know from the server if the client actually did a verify-full on the servers certificate. (Or of course, if the client connected to a hostile server rather than the correct server, the correct server will be unaware of this).

On the client end, you would configure them to use sslmode=verify-full. To test that this configuration is in effect, you can temporarily sabotage ~/.postgresql/root.crt by renaming it and making sure connections fail. Since misconfiguration is easy to do, it might be safer to compile your own client which ignores the setting of sslmode and always implements verify-full.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.