Take the 2-minute tour ×
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 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).

share|improve this question

migrated from stackoverflow.com Jul 11 '12 at 12:36

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
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 '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. –  araqnid Jul 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. –  Bruno Jul 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. –  araqnid Jul 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. –  Bruno Jul 11 '12 at 12:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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