How to verify SSL always being used on Postgresql 9.6

Question

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?

• McNets: SSL requires a certificate in both sides, server and client, do not install client certificate and try to connect.
• McNets: Possible duplicate of How to examine PostgreSQL server's SSL certificate?
• David Simic: 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.

Answer 1

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;

• David Simic: 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.
• Daniel Vérité: @DavidSimic: see edit.
• David Simic: 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.

Answer 2

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.

• jlandercy: Summarized as: SELECT * FROM pg_stat_activity JOIN pg_stat_ssl USING(pid);