Thread: Connect as multiple users using single client certificate

Connect as multiple users using single client certificate

From
Kyle Bateman
Date:
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.




Re: Connect as multiple users using single client certificate

From
Andrew Dunstan
Date:
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




Re: Connect as multiple users using single client certificate

From
Kyle Bateman
Date:
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




Re: Connect as multiple users using single client certificate

From
Tom Lane
Date:
Kyle Bateman <kyle@batemans.org> writes:
> On 10/11/19 12:12 PM, Andrew Dunstan wrote:
>> 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.

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

No, it's not.  The point of the +syntax is to let a collection of users
log in without having to adjust pg_hba.conf anytime you add a new user.
It's not meant to bypass the requirement that the users authenticate
properly.  Would you expect that if you used +users with a password-
based auth method, then all the users would have the same password?

> 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?

I agree with Andrew that that's just silly.  If you give all your users
the same cert then any of them can masquerade as any other.  You might
as well just tell them to share the same login id.

            regards, tom lane



Re: Connect as multiple users using single client certificate

From
Kyle Bateman
Date:
On 10/11/19 1:05 PM, Tom Lane wrote:
> Kyle Bateman <kyle@batemans.org> writes:
>> On 10/11/19 12:12 PM, Andrew Dunstan wrote:
>>> 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.
>> 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.
> No, it's not.  The point of the +syntax is to let a collection of users
> log in without having to adjust pg_hba.conf anytime you add a new user.
> It's not meant to bypass the requirement that the users authenticate
> properly.  Would you expect that if you used +users with a password-
> based auth method, then all the users would have the same password?
>
>> 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?
> I agree with Andrew that that's just silly.  If you give all your users
> the same cert then any of them can masquerade as any other.  You might
> as well just tell them to share the same login id.
In my implementation, I'm not giving the cert to all my users.  I'm only 
giving it to the middleware server that manages connections.

What I hope to accomplish is: Establish a secure, encrypted connection 
to Postgresql from a trusted process, possibly running on another 
machine, whom I trust to tell me which user (within a limited set, 
defined by a role) it would like to connect as.  That process does it's 
own robust authentication of users before letting them through to the 
database by the username they claim.  However, it is still useful to 
connect as different users because my views and functions operate 
differently depending on which user is on the other end of the connection.

Is there a way I can accomplish this using the existing authentication 
methods (other than trust)?




Re: Connect as multiple users using single client certificate

From
Tom Lane
Date:
Kyle Bateman <kyle@batemans.org> writes:
> On 10/11/19 1:05 PM, Tom Lane wrote:
>> I agree with Andrew that that's just silly.  If you give all your users
>> the same cert then any of them can masquerade as any other.  You might
>> as well just tell them to share the same login id.

> In my implementation, I'm not giving the cert to all my users.  I'm only 
> giving it to the middleware server that manages connections.

> What I hope to accomplish is: Establish a secure, encrypted connection 
> to Postgresql from a trusted process, possibly running on another 
> machine, whom I trust to tell me which user (within a limited set, 
> defined by a role) it would like to connect as.  That process does it's 
> own robust authentication of users before letting them through to the 
> database by the username they claim.  However, it is still useful to 
> connect as different users because my views and functions operate 
> differently depending on which user is on the other end of the connection.

Well, you can do that, it's just not cert authentication.

What you might consider is (1) set up an ssl_ca_file, so that the
server only believes client certs traceable to that CA, and (2) require
SSL connections (use "hostssl" entries in pg_hba.conf).  Then you
expect that possession of a cert issued by your CA is enough to
authorize connections to the database.  But don't use the cert
auth method --- based on what you said here, you might even just
use "trust".

            regards, tom lane



Re: Connect as multiple users using single client certificate

From
Stephen Frost
Date:
Greetings,

* Kyle Bateman (kyle@batemans.org) wrote:
> What I hope to accomplish is: Establish a secure, encrypted connection to
> Postgresql from a trusted process, possibly running on another machine, whom
> I trust to tell me which user (within a limited set, defined by a role) it
> would like to connect as.  That process does it's own robust authentication
> of users before letting them through to the database by the username they
> claim.  However, it is still useful to connect as different users because my
> views and functions operate differently depending on which user is on the
> other end of the connection.
>
> Is there a way I can accomplish this using the existing authentication
> methods (other than trust)?

Have you considered just having a regular client-side cert for the
middleware that logs in as a common user to the PG database, and then
performs a SET ROLE to whichever user the middleware has authenticated
the user as?  That seems to match pretty closely what you're looking for
and has the advantage that it'll also allow you to work through
connection poolers.

Thanks,

Stephen

Attachment