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.