Re: Facing issue with cert authentication

Yes Sam. This is what i did as a work around.
Thanks for your suggestion.

Thanks,
Dhirendra.

On Fri, Dec 23, 2022 at 3:22 AM Samed YILDIRIM <samed(at)reddoc(dot)net> wrote:

> Hello Dhirendra,
>
> Have you tried to change your rule in the pg_ident.conf file like below?
> cert-cn-map /^[Ss]([0-9.]+)$ s\1
>
>
> Here is my simple test result.
> openssl req -newkey rsa:2048 -keyout auth.key -x509 -days 365 -out
> auth.crt -nodes -subj '/CN=pg-d'
> openssl req -newkey rsa:2048 -keyout S123.key -out S123.csr -nodes -subj
> '/CN=S123'
> openssl x509 -req -CA auth.crt -CAkey auth.key -in S123.csr -out S123.crt
> -CAcreateserial -days 365
>
> cat <<EOF >> postgresql.conf
> ssl = on
> ssl_ca_file = 'auth.crt'
> ssl_cert_file = 'auth.crt'
> ssl_key_file = 'auth.key'
> EOF
>
> cat <<EOF > pg_hba.conf
> hostssl all all 0.0.0.0/0 cert map=cert-cn-map
> EOF
>
> cat <<EOF >> pg_ident.conf
> cert-cn-map /^[Ss]([0-9.]+)$ s\1
> EOF
>
> #restart postgresql
> openssl x509 -in S123.crt -text -noout|grep Subject
> Subject: CN = S123
> Subject Public Key Info:
>
> psql 'host=127.0.0.1 user=s123 dbname=postgres sslcert=S123.crt
> sslkey=S123.key'
>
> psql (15.0 (Debian 15.0-1.pgdg110+1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
> compression: off)
> Type "help" for help.
>
> postgres=> select current_role;
> current_role
> --------------
> s123
> (1 row)
>
> Best regards.
> Samed YILDIRIM
>
>
> On Thu, 22 Dec 2022 at 06:25, Dhirendra Singh <dhirendraks(at)gmail(dot)com>
> wrote:
>
>> 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.
>>
>