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?

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;

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.