Getting information about PostgreSQL SSL connections

PostgreSQL can, as many other products, use SSL to secure client/server communications. It can be configured to be optional or required, to require a client certificate, or even to use the SSL client certificate to log in. However, the DBA tools are currently a bit lacking in this regard. In particular, there is no way for a DBA to see what SSL parameters are in force (or indeed if SSL is enabled at all) for an already existing connection.

There are multiple ways to see the status of a specific connection (such as the libpq PQgetssl() function, the psql startup message or the sslinfo module. Unfortunately all these methods have one thing in common - they are only available to the process making the connection to the database, not to a DBA inspecting the system from the outside.

9.4 will make it a little bit better, because log_connections now include SSL information when the user connects, similar to:

LOG:  connection authorized: user=mha database=postgres SSL enabled (protocol=TLSv1.1, cipher=ECDHE-RSA-AES256-SHA)

But this can still be a bit of a pain to figure out for existing connectioons of course.

To deal with this problem, I've written a small PostgreSQL extension called pg_sslstatus. When loaded using shared_preload_libraries it will keep track of the SSL status for each connection, and expose it through a system view named pg_sslstatus like this:

postgres=# SELECT * FROM pg_sslstatus;
  pid  | ssl | bits | compression | version |        cipher        |                         clientdn                         
 27286 | t   |  256 | f           | TLSv1   | ECDHE-RSA-AES256-SHA | 
 26682 | t   |  256 | t           | TLSv1   | ECDHE-RSA-AES256-SHA | /C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=magnus
 26693 | f   |      |             |         |                      | 
(3 rows)

It will include one row for each connection, and can then be joined with either pg_stat_activity or pg_stat_replication to view the SSL data in a bigger context:

postgres=# SELECT datname, usename, client_addr, ssl, cipher FROM pg_stat_activity INNER JOIN pg_sslstatus ON;
 datname  | usename | client_addr | ssl |        cipher        
 postgres | mha     |   | t   | ECDHE-RSA-AES256-SHA
 postgres | mha     |             | f   | 
 pgweb    | pgweb   |             | f   | 
(2 rows)

The extension is available from my github account today for existing version of PostgreSQL, under the standard PostgreSQL license. My plan is to work on having a similar view included in PostgreSQL 9.5 by default, but it's unfortunately too late to include it in 9.4 at this point.


Excellent, thank you! I just came across this problem yet again with a long-running COPY that I could not tell was in SSL mode or not, without having to strace into it. I'd rather not have to do it that way in the future.

Posted on Jul 15, 2014 at 02:22 by Greg Sabino Mullane.

Will this work on 8.4?

Posted on Nov 7, 2014 at 21:50 by Casey Allen Shobe.

I think so, but I actually don't know - so please try it and let me know :)

Posted on Nov 10, 2014 at 15:18 by Magnus Hagander.


I speak at and organize conferences around Open Source in general and PostgreSQL in particular.


PGConf.EU 2016
Nov 1-4, 2016
Tallinn, Estonia
Berlin PUG
Nov 17, 2016
Berlin, Germany
PGConf.Asia 2016
Dec 2-3, 2016
Tokyo, Japan
FOSDEM + PGDay 2017
Feb 2-4, 2017
Brussels, Belgium


Stockholm PUG 2016/5
Oct 25, 2016
Stockholm, Sweden
Postgres Vision 2016
Oct 11-13, 2016
San Francisco, USA
Postgres Open
Sep 13-16, 2016
Dallas, USA
Stockholm PUG 2016/4
Aug 31, 2016
Stockholm, Sweden
PG Day'16 Russia
Jul 6-8, 2016
St Petersburg, Russia
More past conferences