PostrgeSQL 9.1 Certificate Authentication: how to get certificate common name of client?

Question

I am using Certificate Authentication for clients on a PostgreSQL 9.1 database server. Several certificate common names are mapped to a single database user in pg_ident.conf.

I do not want to create a database user for each certificate because it is easier to manage accepted certificates with pg_ident.conf. But now I need some query to map a backend process to the common name used for the connection. Is there a way to get the common name (CN field) of the certificate with which a user connected to the database?

EDIT

Thanks araqnid for pointing me at the sslinfo extension (source). The function ssl_client_dn_field('CN') provides exactly the information I need. But the sslinfo functions work only for the connection/backend of the current session, I would rather need this information for other connections/backends (query it by backend process id).

The sslinfo extension uses the global variable MyProcPort (defined in globals.c) of type struct Port* to get the required SSL information. Maybe someone can give me a hint how the MyProcPort variable of other backends could be accessed in an extension (aka contrib module).

Answer 1

Add the extension sslinfo: http://www.postgresql.org/docs/9.1/static/sslinfo.html

This will give you the ssl_client_dn function for the full subject name, or I believe you can do ssl_client_dn_field('CN') to extract just the common name.