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
