Q: cert authentication and user remapping fails - Mailing list pgsql-general

From Albrecht Dreß
Subject Q: cert authentication and user remapping fails
Date
Msg-id NV7V2E3B.DNJBSI3A.SXG5UOID@C6UM6KSS.I47KT6BN.VTVKOWVO
Whole thread Raw
Responses Re: Q: cert authentication and user remapping fails
List pgsql-general
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.
Attachment

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: upgrade and migrate
Next
From: Tom Lane
Date:
Subject: Re: Q: cert authentication and user remapping fails