Postgresql – How to verify SSL always being used on Postgresql 9.6

postgresqlpostgresql-9.6ssl

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?

Best Answer

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;
Related Question