Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

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.
–
BrunoJul 11 '12 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.
–
araqnidJul 11 '12 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.
–
BrunoJul 11 '12 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.
–
araqnidJul 11 '12 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.
–
BrunoJul 11 '12 at 12:29