postgres connection failing with error FATAL: certificate authentication failed for user "xxxxxxxxxx"

Products

• VMware Tanzu SQL

Issue/Introduction

+ Customer reported that when OS username and DB username is same postgres connection from pgadmin connects successfully. Whereas when DB username is different from OS username its failing with below error in pg_logs

[postgres@xxxxx log]$ tail -0f postgresql.log|grep FA766


t=2024-06-28 13:12:43.224 GMT u=FA76636 db=xxxxxxxxxx pid=[2461682] c=xx.xxx.xx.xx app=[unknown]:LOG:  connection authenticated: identity="CN=FA76636,OU=TechnicalUser,OU=CA,O=UBS,C=CH" method=cert (/var/lib/postgresql/data/pg_hba.conf:87)


t=2024-06-28 13:12:43.224 GMT u=FA76636 db=xxxxxxxxxx pid=[2461682] c=xx.xxx.xx.xx app=[unknown]:LOG:  no match in usermap "CertMap" for user "FA76636" authenticated as "FA76636"


t=2024-06-28 13:12:43.225 GMT u=FA76636 db=xxxxxxxxxx pid=[2461682] c=xx.xxx.xx.xx app=[unknown]:FATAL:  certificate authentication failed for user "FA76636"


t=2024-06-28 13:12:43.225 GMT u=FA76636 db=xxxxxxxxxx pid=[2461682] c=xx.xxx.xx.xx app=[unknown]:DETAIL:  Connection matched pg_hba.conf line 87: "hostssl all all 0.0.0.0/0 cert clientcert=verify-full map=CertMap"


t=2024-06-28 13:12:43.480 GMT u=FA76636 db=xxxxxxxxxx pid=[2461683] c=xx.xxx.xx.xx app=[unknown]:FATAL:  no pg_hba.conf entry for host "xx.xxx.xx.xx", user "FA76636", database "xxxxxxxxxx", no encryption

Environment

VMware Tanzu SQL - VMware Tanzu Postgres 15.5.0

Cause

+ Login was failing after mapping for OS-DB user in file pg_ident.conf replaced from FA76636-FA76636 to FA76636-test_user which is implied in the error logs as "no match in usermap".

+ So OS user FA76636 was authenticated with certificate but db user mapping for FA76636 lost and hence caused the error.

Resolution

Replacing the DB username as test_user in pgadmin connection string resolved the issue as the OS use FA76636 was authenticated with certificate and as well as mapping for OS user-DB user present in the pg_ident.conf file.