Re: Connect as multiple users using single client certificate

On 10/11/19 1:58 PM, Kyle Bateman wrote:
> 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.
>
>

I think the short answer is: No. The client certificate should match the
username and nothing else. If you don't want to generate certificates
for all your users I suggest using some other form of auth (e.g.
scram-sha-256).

The long answer is that you can use maps, but it's probably not a good
idea. e.g. you have a map allowing foo to connect as both bar and baz,
and give both bar and baz a certificate with a CN of foo. But then bar
can connect as baz and vice versa, which isn't a good thing.

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services