May 11th, 2015 by depesz | Tags: , , , | No comments »
Did it help? If yes - maybe you can help me?

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.

Leave a comment