Re: Connect as multiple users using single client certificate

On 10/11/19 12:12 PM, Andrew Dunstan wrote:
> 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
>
>
Hmmm, too bad.  It would be nice to be able to generate a certificate, 
say with a commonName of "+users" (or some other setting) which matches 
what is specified in pg_hba.conf, allowing connections from anyone 
within the specified group.  Seems like that is the intent of the "+" 
syntax in the first place.

In my case, the middleware is validating end-users using distributed 
keys, so no username/passwords are needed.  I was hoping to avoid all 
that and just rely on SSL.

Any idea if this is a viable feature enhancement?

Kyle


Kyle