NOTE! pg_sslstatus has been superseded by the built-in pg_stat_ssl view as of PostgreSQL 9.5. It is only useful for earlier versions of PostgreSQL!
pg_sslstatus is a simple extension to PostgreSQL that allows an administrator to view the SSL status of different connections. It shows whether SSL is in force, the negotiated SSL parameters, and information about the (optional) client certificate on the connection.
pg_sslstatus is built with pgxs. To install, just run:
$ make
$ sudo make install
This assumes that the basic compilers and PostgreSQL development packages are installed on your system. On Debian for example, you can install those using:
$ sudo apt-get install build-essential postgresql-server-dev-all
Once built, the extension needs to be installed in the server. Since it's packaged as an extension, it's a simple command in psql:
postgres=# CREATE EXTENSION pg_sslstatus;
CREATE EXTENSION
This will create a view and an underlying function.
The library also needs to be loaded in shared_preload_libraries, by
setting the parameter in postgresql.conf:
shared_preload_libraries = 'pg_sslstatus'
All SSL information is exposed through a simple view:
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)
The pid field can be joined to either pg_stat_activity or
pg_stat_replication to connect the information with further details
about the connection.
There are of course a number of limitations :) Some to note are:
- Cipher names are truncated at 64 characters (I've never seen one longer...)
- Certificate DNs are truncated at 64 characters (this is certainly more common)