Q: cert authentication and user remapping fails
Hi all,
I have a problem with psql cert authentication and user mapping.
In my installation, the user certificate CN's contain human-readable names (utf8, with spaces, etc.). I want *all* users connecting with cert authentication to be mapped to a certain database role. The server runs on Debian Stretch, using the package “postgresql-10” ver. “10.11-1.pgdg90+1”.
The configuration in pg_hba.conf is, inter alia
<snip>
hostssl testdb all 172.16.61.0/24 cert map=certaccess
</snip>
The file pg_ident.conf contains the line (which should, as the re matches *everything*, map all users?)
<snip>
certaccess /^.*$ testuser
</snip>
I have a user certificate, issued by the same CA as the server cert, with CN "Albrecht Dreß". Running psql on a remote client fails:
<snip>
albrecht@deneb:~$ psql -h dbserver -U "Albrecht Dreß" testdb
psql: FATAL: certificate authentication failed for user "Albrecht Dreß"
FATAL: no pg_hba.conf entry for host "172.16.61.70", user "Albrecht Dreß", database "testdb", SSL off
</snip>
The server log says:
<snip>
Albrecht Dreß@testdb LOG: no match in usermap "certaccess" for user "Albrecht Dreß" authenticated as "Albrecht Dreß"
Albrecht Dreß@testdb FATAL: certificate authentication failed for user "Albrecht Dreß"
Albrecht Dreß@testdb DETAIL: Connection matched pg_hba.conf line 136: " hostssl testdb all 172.16.61.0/24 cert map=certaccess"
Albrecht Dreß@testdb FATAL: no pg_hba.conf entry for host "172.16.61.70", user "Albrecht Dreß", database "testdb", SSL off
</snip>
For me, this looks as if the certificate is accepted, but the regexp match of the CN somehow fails.
Note 1: I don't have a role “Albrecht Dreß” defined.
Note 2: using my “real” user name (albrecht), i.e. omitting the “-U” option above, fails with the server log message “provided user name (albrecht) and authenticated user name (Albrecht Dreß) do not match”.
Any idea what I did wrong, and how I can a working cert authentication?
Thanks in advance,
Albrecht.
Albrecht =?iso-8859-1?b?RHJl3w==?= <albrecht.dress@arcor.de> writes:
In my installation, the user certificate CN's contain human-readable names (utf8, with spaces, etc.). I want *all* users connecting with cert authentication to be mapped to a certain database role.
I don't think that the user name mapping feature works in the way
you are hoping it does. According to
https://www.postgresql.org/docs/current/auth-username-maps.html
what the map does is to specify allowed combinations of the validated
external user name ("Albrecht Dreß" in your example) and the database
role the user asked to connect as. So given
certaccess /^.*$ testuser
it should be possible to do
psql -h dbserver -U testuser testdb
with a certificate that has CN="Albrecht Dreß" (or anything else).
But the map won't result in silently connecting you as some other
role than the one you asked for.
(I haven't actually tried this, but that's how I read the docs.)
regards, tom lane
Sorry for the late reply, I could test your solution only today…
Am 06.12.19 18:52 schrieb(en) Tom Lane:
I don't think that the user name mapping feature works in the way you are hoping it does. According to https://www.postgresql.org/docs/current/auth-username-maps.html what the map does is to specify allowed combinations of the validated external user name ("Albrecht Dreß" in your example) and the database role the user asked to connect as. So given
certaccess /^.*$ testuser
it should be possible to do
psql -h dbserver -U testuser testdb
with a certificate that has CN="Albrecht Dreß" (or anything else).
Yes, this works perfectly – I really misunderstood the docs here!
Thanks a lot for your help,
Albrecht.