Thread: 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*
usersconnecting with cert authentication to be mapped to a certain database role.  The server runs on Debian Stretch,
usingthe 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
clientfails: 

<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
“provideduser 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*
usersconnecting 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.htmlwhat the map does is to specify allowed combinations of
thevalidated external user name ("Albrecht Dreß" in your example) and the database role the user asked to connect as.
Sogiven 
>
> > 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.