Facing issue with cert authentication

From: Dhirendra Singh <dhirendraks(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Facing issue with cert authentication
Date: 2022-12-22 04:25:22
Message-ID: CAEyh4sbEsn0BAb5KXf_wUgoJVSd=9Nj4aNZP626zVPKdy4HmXw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi All,
I am using cert authentication to authenticate.
I have created a user with name S114546 (with uppercase 'S'). user created
is s114546 (with lowercase 's').
CN in the client certificate is "pg-read (S114546)". 'S' in S114546 is
uppercase. I have no control to have the 'S' in the CN in lowercase. My
organization PKI always create the certificate with uppercase 'S'.
I extracted the string S114546 from the CN using regex in the pg_ident.conf
file.
cert-cn-map /^.*[(]([Ss][0-9.]*)[)]$ \1

Now when i try to connect using psql, authentication fails. I try to
connect with both as user S114546(uppercase S) as well as s114546(lowercase
s). In both case it fails.

When i try to connect with S114546, it fail with message that no role
"S114546" exist.
psql "host=postgres.app.net user=S114546 dbname=appdb sslmode=verify-full
sslcert=cert.pem sslkey=cert-key.pem sslrootcert=tls-ca-bundle.pem"
psql: error: connection to server at "postgres.app.net" (10.129.187.27),
port 5432 failed: FATAL: role "S114546" does not exist

When i try to connect with s114546, certificate authentication fail.
extracted username from CN is S114546(uppercase S) and supplied username in
connection is s114546(lowercase s).
psql "host=postgres.app.net user=s114546 dbname=appdb sslmode=verify-full
sslcert=cert.pem sslkey=cert-key.pem sslrootcert=tls-ca-bundle.pem"
psql: error: connection to server at "postgres.app.net" (10.129.187.27),
port 5432 failed: FATAL: certificate authentication failed for user
"s114546"

isn't it strange behavior? while creating the user it ignores the case but
checks the case during authentication.
Anyone can please suggest how to resolve this issue ?
I can create the user with uppercase 'S' by double quoting the username.
but the script which creates the user will do the same for all users which
i do not want.

Thanks,
Dhirendra.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message David G. Johnston 2022-12-22 04:51:40 Re: Facing issue with cert authentication
Previous Message SASIKUMAR Devaraj 2022-12-20 11:32:34 Re: How to speed WAL apply in destination