From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Facing issue with cert authentication
Date: Thu, 22 Dec 2022 10:40:04 +0100
Message-ID: <05afcadd-0e31-751c-c915-cb27e6bf5a5b@jakobs.com> (raw)
In-Reply-To: <CAEyh4sbEsn0BAb5KXf_wUgoJVSd=9Nj4aNZP626zVPKdy4HmXw@mail.gmail.com>
References: <CAEyh4sbEsn0BAb5KXf_wUgoJVSd=9Nj4aNZP626zVPKdy4HmXw@mail.gmail.com>
Am 22.12.22 um 05:25 schrieb Dhirendra Singh:
> 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 <http://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
> <http://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 <http://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
> <http://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.
Can you try:
psql "host=postgres.app.net <http://postgres.app.net; user=\"S114546\"
dbname=appdb sslmode=verify-full sslcert=cert.pem sslkey=cert-key.pem
sslrootcert=tls-ca-bundle.pem"
This should keep the user name in upper case. Without quotes (which have
to be protected by a preceding backslash, because the whole conninfo is
already in quotes) the name will be folded to lower case.
Best Regards,
Holger
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../05afcadd-0e31-751c-c915-cb27e6bf5a5b@jakobs.com/3-OpenPGP_signature)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: holger@jakobs.com, pgsql-admin@lists.postgresql.org
Subject: Re: Facing issue with cert authentication
In-Reply-To: <05afcadd-0e31-751c-c915-cb27e6bf5a5b@jakobs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox