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
