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?


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).


1 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.

  • Doesn't this happen too late? These look like PostgreSQL functions that you can only run once you've been able to connect. The pg_ident.conf doc doesn't seem to say anything about running functions.
    – Bruno
    Jul 11, 2012 at 11:39
  • "Is there a way to get the common name (CN field) of the certificate with which a user connected to the database?" - this sounds to me like something being done after connecting. Although it might be an issue that the information can only be fetched for the current connection, not any arbitrary one.
    – araqnid
    Jul 11, 2012 at 11:51
  • Yes, sorry, of course, after connecting, but before authenticating and having access to SELECT ssl_client_dn_field('CN') and so on.
    – Bruno
    Jul 11, 2012 at 11:53
  • 1
    @Bruno tbh I'm not sure we can really tell what the OP needs. do stored functions need this value to save as auditing data? I got the impression that he'd already set up pg_ident.conf to allow the correct set of users to connect, but wanted to be able to distinguish them later without mapping them each to a different database user.
    – araqnid
    Jul 11, 2012 at 12:06
  • Ah, I was under the impression that he wanted to implement the mapping using data in the database itself, instead of fixing it in pg_ident.conf, to have something more dynamic. I think your interpretation makes more sense indeed, sorry.
    – Bruno
    Jul 11, 2012 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.