Re: Connect as multiple users using single client certificate

On 10/11/19 1:05 PM, Tom Lane wrote:
> Kyle Bateman <kyle(at)batemans(dot)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)?