Re: Facing issue with cert authentication

I have tried it but it did not work.

On Thu, Dec 22, 2022 at 3:10 PM Holger Jakobs <holger(at)jakobs(dot)com> wrote:

> 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 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.
>
>
> Can you try:
>
> psql "host=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
>
>