Connect as multiple users using single client certificate

From: Kyle Bateman <kyle(at)batemans(dot)org>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Connect as multiple users using single client certificate
Date: 2019-10-11 17:58:50
Message-ID: 6dd1d5b5-b511-48aa-148a-1a0dacf574d0@batemans.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I have some JS middleware that needs to securely connect to the
postgresql back end.  Any number of different users may connect via
websocket to this middleware to manage their connection to the
database.  I want the JS process to have a client certificate
authorizing it to connect to the database.

I have this line in my pg_hba.conf:

hostssl        all    +users        all        cert

So the idea is, I should be able to connect as any user that is a member
of the role "users."

Under this configuration, I can currently connect as the user "users"
but not as "joe" who is a member of the role "users."  I get:

FATAL:  certificate authentication failed for user "joe"

This makes sense as the commonName on the certificate is "users" and not
"joe."  But the documentation for pg_hba.conf states that prefixing the
username with a "+" should allow me to connect as any role who is a
member of the stated role.

Is there a way to do this via client certificate authorization?  I have
no way of knowing the specific usernames ahead of time, as new users may
be created in the database (thousands) and I can't really be creating
separate certificates for every different user.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Justin Pryzby 2019-10-11 17:59:56 Re: v12.0: ERROR: could not find pathkey item to sort
Previous Message Tom Lane 2019-10-11 15:45:31 Re: stress test for parallel workers