pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedFacing issue with cert authentication
11+ messages / 7 participants
[nested] [flat]
* Facing issue with cert authentication
@ 2022-12-22 04:25 Dhirendra Singh <dhirendraks@gmail.com>
2022-12-22 04:51 ` Re: Facing issue with cert authentication David G. Johnston <david.g.johnston@gmail.com>
2022-12-22 09:40 ` Re: Facing issue with cert authentication Holger Jakobs <holger@jakobs.com>
2022-12-22 21:52 ` Re: Facing issue with cert authentication Samed YILDIRIM <samed@reddoc.net>
0 siblings, 3 replies; 11+ messages in thread
From: Dhirendra Singh @ 2022-12-22 04:25 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Facing issue with cert authentication
2022-12-22 04:25 Facing issue with cert authentication Dhirendra Singh <dhirendraks@gmail.com>
@ 2022-12-22 04:51 ` David G. Johnston <david.g.johnston@gmail.com>
2022-12-22 09:09 ` Re: Facing issue with cert authentication Dhirendra Singh <dhirendraks@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: David G. Johnston @ 2022-12-22 04:51 UTC (permalink / raw)
To: Dhirendra Singh <dhirendraks@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Wed, Dec 21, 2022 at 9:25 PM Dhirendra Singh <dhirendraks@gmail.com>
wrote:
>
> 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').
>
> isn't it strange behavior? while creating the user it ignores the case but
> checks the case during authentication.
>
It is what it is...that you can write transient SQL with case-folding
behavior doesn't mean other contexts (in this case the O/S) are going to
behave the same way. Regardless, it is case-sensitive through-and-through
which is not strange.
>
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.
>
>
It seems considerably easier to modify your database role creation compared
to the PKI certification creation. I suggest you figure out what you can
live with on that front.
David J.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Facing issue with cert authentication
2022-12-22 04:25 Facing issue with cert authentication Dhirendra Singh <dhirendraks@gmail.com>
2022-12-22 04:51 ` Re: Facing issue with cert authentication David G. Johnston <david.g.johnston@gmail.com>
@ 2022-12-22 09:09 ` Dhirendra Singh <dhirendraks@gmail.com>
2022-12-22 12:46 ` Re: Facing issue with cert authentication Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 11+ messages in thread
From: Dhirendra Singh @ 2022-12-22 09:09 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
It seems regex provides a way to convert the extracted string to lowercase
by using "\L\1". but this is also not working...seems not supported by
postgres.
Is there a way to convert the matched string to lowercase ?
Thanks,
Dhirendra.
On Thu, Dec 22, 2022 at 10:21 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Wed, Dec 21, 2022 at 9:25 PM Dhirendra Singh <dhirendraks@gmail.com>
> wrote:
>
>>
>> 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').
>>
>> isn't it strange behavior? while creating the user it ignores the case
>> but checks the case during authentication.
>>
>
> It is what it is...that you can write transient SQL with case-folding
> behavior doesn't mean other contexts (in this case the O/S) are going to
> behave the same way. Regardless, it is case-sensitive through-and-through
> which is not strange.
>
>
>>
> 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.
>>
>>
> It seems considerably easier to modify your database role creation
> compared to the PKI certification creation. I suggest you figure out what
> you can live with on that front.
>
> David J.
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Facing issue with cert authentication
2022-12-22 04:25 Facing issue with cert authentication Dhirendra Singh <dhirendraks@gmail.com>
2022-12-22 04:51 ` Re: Facing issue with cert authentication David G. Johnston <david.g.johnston@gmail.com>
2022-12-22 09:09 ` Re: Facing issue with cert authentication Dhirendra Singh <dhirendraks@gmail.com>
@ 2022-12-22 12:46 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-12-22 23:19 ` Re: Facing issue with cert authentication Ed Sabol <edwardjsabol@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Laurenz Albe @ 2022-12-22 12:46 UTC (permalink / raw)
To: Dhirendra Singh <dhirendraks@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Thu, 2022-12-22 at 14:39 +0530, Dhirendra Singh wrote:
> On Thu, Dec 22, 2022 at 10:21 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
> > On Wed, Dec 21, 2022 at 9:25 PM Dhirendra Singh <dhirendraks@gmail.com> wrote:
> > >
> > > 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').
> > >
> > > 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.
> >
> > It seems considerably easier to modify your database role creation
> > compared to the PKI certification creation. I suggest you figure out
> > what you can live with on that front.
>
> It seems regex provides a way to convert the extracted string to lowercase
> by using "\L\1". but this is also not working...seems not supported by postgres.
> Is there a way to convert the matched string to lowercase ?
That syntax is not supported by PostgreSQL, and I cannot think of a better
way that to create the role with an upper case "S".
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Facing issue with cert authentication
2022-12-22 04:25 Facing issue with cert authentication Dhirendra Singh <dhirendraks@gmail.com>
2022-12-22 04:51 ` Re: Facing issue with cert authentication David G. Johnston <david.g.johnston@gmail.com>
2022-12-22 09:09 ` Re: Facing issue with cert authentication Dhirendra Singh <dhirendraks@gmail.com>
2022-12-22 12:46 ` Re: Facing issue with cert authentication Laurenz Albe <laurenz.albe@cybertec.at>
@ 2022-12-22 23:19 ` Ed Sabol <edwardjsabol@gmail.com>
2022-12-22 23:23 ` Re: Facing issue with cert authentication David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Ed Sabol @ 2022-12-22 23:19 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>; +Cc: Dhirendra Singh <dhirendraks@gmail.com>
On Dec 22, 2022, at 7:46 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> That syntax is not supported by PostgreSQL, and I cannot think of a better
> way than to create the role with an upper case "S".
That sounds like the solution for Dhirendra. Is there some trick to doing that?
Dhirendra said the username was converted to lowercase when he tried....
Thanks,
Ed
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Facing issue with cert authentication
2022-12-22 04:25 Facing issue with cert authentication Dhirendra Singh <dhirendraks@gmail.com>
2022-12-22 04:51 ` Re: Facing issue with cert authentication David G. Johnston <david.g.johnston@gmail.com>
2022-12-22 09:09 ` Re: Facing issue with cert authentication Dhirendra Singh <dhirendraks@gmail.com>
2022-12-22 12:46 ` Re: Facing issue with cert authentication Laurenz Albe <laurenz.albe@cybertec.at>
2022-12-22 23:19 ` Re: Facing issue with cert authentication Ed Sabol <edwardjsabol@gmail.com>
@ 2022-12-22 23:23 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: David G. Johnston @ 2022-12-22 23:23 UTC (permalink / raw)
To: Ed Sabol <edwardjsabol@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>; Dhirendra Singh <dhirendraks@gmail.com>
On Thu, Dec 22, 2022 at 4:19 PM Ed Sabol <edwardjsabol@gmail.com> wrote:
> On Dec 22, 2022, at 7:46 AM, Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
> > That syntax is not supported by PostgreSQL, and I cannot think of a
> better
> > way than to create the role with an upper case "S".
>
> That sounds like the solution for Dhirendra. Is there some trick to doing
> that?
> Dhirendra said the username was converted to lowercase when he tried....
>
>
Actually, what they said at the end was:
"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."
So the trick is already known here - use quoting.
Assuming all these IDs really begin with a capital letter S, Samed's
suggestion of having the pg_ident.conf file hard-code the lower-case s into
the resultant name (i.e., just copy over the numbers) is probably the
easiest solution.
David J.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Facing issue with cert authentication
2022-12-22 04:25 Facing issue with cert authentication Dhirendra Singh <dhirendraks@gmail.com>
@ 2022-12-22 09:40 ` Holger Jakobs <holger@jakobs.com>
2022-12-22 11:29 ` Re: Facing issue with cert authentication Dhirendra Singh <dhirendraks@gmail.com>
2022-12-23 01:56 ` Re: Facing issue with cert authentication Jeff Janes <jeff.janes@gmail.com>
2 siblings, 2 replies; 11+ messages in thread
From: Holger Jakobs @ 2022-12-22 09:40 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Facing issue with cert authentication
2022-12-22 04:25 Facing issue with cert authentication Dhirendra Singh <dhirendraks@gmail.com>
2022-12-22 09:40 ` Re: Facing issue with cert authentication Holger Jakobs <holger@jakobs.com>
@ 2022-12-22 11:29 ` Dhirendra Singh <dhirendraks@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Dhirendra Singh @ 2022-12-22 11:29 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
I have tried it but it did not work.
On Thu, Dec 22, 2022 at 3:10 PM Holger Jakobs <holger@jakobs.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
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Facing issue with cert authentication
2022-12-22 04:25 Facing issue with cert authentication Dhirendra Singh <dhirendraks@gmail.com>
2022-12-22 09:40 ` Re: Facing issue with cert authentication Holger Jakobs <holger@jakobs.com>
@ 2022-12-23 01:56 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Jeff Janes @ 2022-12-23 01:56 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
On Thu, Dec 22, 2022 at 4:40 AM Holger Jakobs <holger@jakobs.com> wrote:
>
> 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.
>
On the command line, case folding is not done, so adding the quotes doesn't
do what you want. It is once you are inside psql (which is presumably
where the user is created via CREATE USER) where the case folding kicks in.
Cheers,
Jeff
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Facing issue with cert authentication
2022-12-22 04:25 Facing issue with cert authentication Dhirendra Singh <dhirendraks@gmail.com>
@ 2022-12-22 21:52 ` Samed YILDIRIM <samed@reddoc.net>
2022-12-23 03:31 ` Re: Facing issue with cert authentication Dhirendra Singh <dhirendraks@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: Samed YILDIRIM @ 2022-12-22 21:52 UTC (permalink / raw)
To: Dhirendra Singh <dhirendraks@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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@gmail.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.
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Facing issue with cert authentication
2022-12-22 04:25 Facing issue with cert authentication Dhirendra Singh <dhirendraks@gmail.com>
2022-12-22 21:52 ` Re: Facing issue with cert authentication Samed YILDIRIM <samed@reddoc.net>
@ 2022-12-23 03:31 ` Dhirendra Singh <dhirendraks@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Dhirendra Singh @ 2022-12-23 03:31 UTC (permalink / raw)
To: Samed YILDIRIM <samed@reddoc.net>; +Cc: pgsql-admin@lists.postgresql.org
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@reddoc.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@gmail.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.
>>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2022-12-23 03:31 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-22 04:25 Facing issue with cert authentication Dhirendra Singh <dhirendraks@gmail.com>
2022-12-22 04:51 ` David G. Johnston <david.g.johnston@gmail.com>
2022-12-22 09:09 ` Dhirendra Singh <dhirendraks@gmail.com>
2022-12-22 12:46 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-12-22 23:19 ` Ed Sabol <edwardjsabol@gmail.com>
2022-12-22 23:23 ` David G. Johnston <david.g.johnston@gmail.com>
2022-12-22 09:40 ` Holger Jakobs <holger@jakobs.com>
2022-12-22 11:29 ` Dhirendra Singh <dhirendraks@gmail.com>
2022-12-23 01:56 ` Jeff Janes <jeff.janes@gmail.com>
2022-12-22 21:52 ` Samed YILDIRIM <samed@reddoc.net>
2022-12-23 03:31 ` Dhirendra Singh <dhirendraks@gmail.com>
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