Re: Facing issue with cert authentication
| От | Samed YILDIRIM |
|---|---|
| Тема | Re: Facing issue with cert authentication |
| Дата | |
| Msg-id | CAAo1mbnvxdFuDLs2GmWNWFxFNzD1=SZBfub7tKO98ZQ--Po67w@mail.gmail.com обсуждение исходный текст |
| Ответ на | Facing issue with cert authentication (Dhirendra Singh <dhirendraks@gmail.com>) |
| Ответы |
Re: Facing issue with cert authentication
(Dhirendra Singh <dhirendraks@gmail.com>)
|
| Список | pgsql-admin |
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)
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.
В списке pgsql-admin по дате отправления:
Предыдущее
От: M SarwarДата:
Сообщение: Re: Possible options to import xml files from desktop to postgress/AWS-RDS