On 12th of April, Magnus Hagander committed patch:
Add system view pg_stat_ssl This view shows information about all connections, such as if the connection is using SSL, which cipher is used, and which client certificate (if any) is used. Reviews by Alex Shulgin, Heikki Linnakangas, Andres Freund & Michael Paquier
It was committed some time ago, but I needed to setup SSL for my test machine to show how this works. Now I finally found time to do it, so I can show.
Configured my machine as described in this post, and after connecting, I see:
$ SELECT * FROM pg_stat_ssl ; pid | ssl | version | cipher | bits | compression | clientdn -------+-----+---------+-----------------------------+--------+-------------+----------------------------------------------------------------- 31547 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | f | /C=PL/ST=Mazowieckie/L=Warsaw/O=depesz/CN=pg/USER/pgdba/emailAd 31427 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | f | /C=PL/ST=Mazowieckie/L=Warsaw/O=depesz/CN=pg/USER/depesz/emailA 31573 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | f | 31614 | f | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] (4 ROWS)
There are 4 connections. One of them doesn't use SSL at all, the other 3 have the same connection settings, but one is not using client certificate.
This data can be joined with pg_stat_activity to show more information:
$ SELECT a.client_addr, a.client_port, a.application_name, a.usename, a.datname, s.* FROM pg_stat_ssl s JOIN pg_stat_activity a USING (pid); client_addr | client_port | application_name | usename | datname | pid | ssl | version | cipher | bits | compression | clientdn -------------+-------------+------------------+---------+---------+-------+-----+---------+-----------------------------+--------+-------------+----------------------------------------------------------------- 127.0.0.1 | 39883 | psql | pgdba | pgdba | 31547 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | f | /C=PL/ST=Mazowieckie/L=Warsaw/O=depesz/CN=pg/USER/pgdba/emailAd 127.0.0.1 | 39878 | psql | depesz | depesz | 31427 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | f | /C=PL/ST=Mazowieckie/L=Warsaw/O=depesz/CN=pg/USER/depesz/emailA 127.0.0.1 | 39884 | psql | pgdba | pgdba | 31573 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | f | [NULL] | -1 | psql | depesz | depesz | 31614 | f | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] (4 ROWS)
Which clearly shows what's what. Nice. That will definitely be useful for DBAs using SSL for their databases.