ssl_crl_file Certificate Revocation List doesn't work for postgresql 11
Hi All,
OS: CentOS 7.6
PG: 11.11
Our env already configured ssl
--server postgresql.conf
ssl = 'on'
ssl_ca_file = '/var/lib/pgsql/tls/root.crt'
ssl_cert_file = '/var/lib/pgsql/tls/server.crt'
ssl_key_file = '/var/lib/pgsql/tls/server.key'
--client configuration
$ ls -alrt /var/lib/pgsql/.postgresql
total 20
-rw-r--r-- 1 postgres postgres 688 Nov 30 06:46 root.crt
-rw-r--r-- 1 postgres postgres 778 Nov 30 06:46 postgresql.crt
-rw------- 1 postgres postgres 1708 Nov 30 06:47 postgresql.key
--From client to connect ssl works
$ psql "host=master.pgcluster11.service.consul port=5432 dbname=testdb
user=test sslmode=verify-full"
Password:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)
--Now we want to configure the ssl_crl_file and generated the root.crl file
as below redhat doc
--Use openssl to verify, shows "certificate revoked"
# cat /home/sunyi/tls/root.crt /home/sunyi/tls/1/root.crl > /tmp/test_1.pem
# openssl verify -extended_crl -verbose -CAfile /tmp/test_1.pem -crl_check
/home/sunyi/tls/1/server.crt
/home/sunyi/tls/1/server.crt: O = Acronis, OU = DBS, CN =
s12345y-patroni_cluster-507460701
error 23 at 0 depth lookup:certificate revoked
--copy root.crl file to /var/lib/pgsql/tls
-bash-4.2$ ls -alrt /var/lib/pgsql/tls
total 20
drwx------ 4 postgres postgres 4096 Nov 30 04:20 ..
-rw-r----- 1 postgres postgres 1164 Nov 30 04:20 server.crt
-rw------- 1 postgres postgres 1679 Nov 30 04:20 server.key
-rw-r----- 1 postgres postgres 688 Nov 30 04:20 root.crt
drwx------ 2 postgres postgres 4096 Nov 30 04:20 .
--Configure /var/lib/pgsql/11/data/postgresql.conf
ssl_crl_file = '/var/lib/pgsql/tls/root.crl'
--Reload postgresql
$ psql
psql (11.11)
Type "help" for help.
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# show ssl_crl_file;
ssl_crl_file
-----------------------------
/var/lib/pgsql/tls/root.crl
(1 row)
--From client to connect still can connect not as expect
$ psql "host=master.pgcluster11.service.consul port=5432 dbname=testdb
user=test sslmode=verify-full"
Password:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)
Could you please advise if need any more configuration? Thanks
Thanks and best regards
Sun Yi
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
El 30/11/2021 a las 10:53, Yi Sun escribió:<br>
<blockquote type="cite"
cite="mid:CABWY_HCBUCjY1EJHrEGePGEaSZ5b29apgTohCyygtsqe_ySYng@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="ltr">Hi All,
<div><br>
</div>
<div>OS: CentOS 7.6</div>
<div>PG: 11.11</div>
<div><br>
</div>
<div>Our env already configured ssl</div>
<div>--server postgresql.conf</div>
<div>ssl = 'on'<br>
ssl_ca_file = '/var/lib/pgsql/tls/root.crt'<br>
ssl_cert_file = '/var/lib/pgsql/tls/server.crt'<br>
ssl_key_file = '/var/lib/pgsql/tls/server.key'<br>
</div>
<div><br>
</div>
<div>--client configuration</div>
<div>$ ls -alrt /var/lib/pgsql/.postgresql<br>
total 20<br>
-rw-r--r-- 1 postgres postgres 688 Nov 30 06:46 root.crt<br>
-rw-r--r-- 1 postgres postgres 778 Nov 30 06:46
postgresql.crt<br>
-rw------- 1 postgres postgres 1708 Nov 30 06:47
postgresql.key<br>
</div>
<div><br>
</div>
<div>--From client to connect ssl works</div>
<div>$ psql "host=master.pgcluster11.service.consul port=5432
dbname=testdb user=test sslmode=verify-full"<br>
Password:<br>
SSL connection (protocol: TLSv1.2, cipher:
ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)<br>
<br>
</div>
<div>--Now we want to configure the ssl_crl_file and generated
the root.crl file as below redhat doc</div>
<div><br>
</div>
<div><a
href="https://access.redhat.com/documentation/en-us/red_hat_update_infrastructure/2.1/html/administration_guide/chap-red_hat_update_infrastructure-administration_guide-certification_revocation_list_crl"
moz-do-not-send="true" class="moz-txt-link-freetext">https://access.redhat.com/documentation/en-us/red_hat_update_infrastructure/2.1/html/administration_guide/chap-red_hat_update_infrastructure-administration_guide-certification_revocation_list_crl</a><br>
</div>
<div><br>
</div>
<div>--Use openssl to verify, shows "certificate revoked"</div>
<div># cat /home/sunyi/tls/root.crt /home/sunyi/tls/1/root.crl
> /tmp/test_1.pem<br>
</div>
<div># openssl verify -extended_crl -verbose -CAfile
/tmp/test_1.pem -crl_check /home/sunyi/tls/1/server.crt<br>
/home/sunyi/tls/1/server.crt: O = Acronis, OU = DBS, CN =
s12345y-patroni_cluster-507460701<br>
error 23 at 0 depth lookup:certificate revoked<br>
</div>
<div><br>
</div>
<div>--copy root.crl file to /var/lib/pgsql/tls </div>
<div>-bash-4.2$ ls -alrt /var/lib/pgsql/tls<br>
total 20<br>
drwx------ 4 postgres postgres 4096 Nov 30 04:20 ..<br>
-rw-r----- 1 postgres postgres 1164 Nov 30 04:20 server.crt<br>
-rw------- 1 postgres postgres 1679 Nov 30 04:20 server.key<br>
-rw-r----- 1 postgres postgres 688 Nov 30 04:20 root.crt<br>
drwx------ 2 postgres postgres 4096 Nov 30 04:20 .<br>
</div>
<div><br>
</div>
<div>--Configure /var/lib/pgsql/11/data/postgresql.conf<br>
ssl_crl_file = '/var/lib/pgsql/tls/root.crl'<br>
</div>
<div><br>
</div>
<div>--Reload postgresql</div>
<div>$ psql<br>
psql (11.11)<br>
Type "help" for help.<br>
<br>
postgres=# select pg_reload_conf();<br>
pg_reload_conf<br>
----------------<br>
t<br>
(1 row)<br>
</div>
<div>postgres=# show ssl_crl_file;<br>
ssl_crl_file<br>
-----------------------------<br>
/var/lib/pgsql/tls/root.crl<br>
(1 row)<br>
</div>
<div><br>
</div>
<div>--From client to connect still can connect not as expect</div>
<div>$ psql "host=master.pgcluster11.service.consul port=5432
dbname=testdb user=test sslmode=verify-full"<br>
Password:<br>
SSL connection (protocol: TLSv1.2, cipher:
ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)<br>
</div>
<div><br>
</div>
<div>Could you please advise if need any more configuration?
Thanks</div>
<div><br>
</div>
<div>Thanks and best regards</div>
<div>Sun Yi</div>
</div>
</blockquote>
<br>
Hi, <br>
<br>
<span class="VIiyi" lang="en"><span class="JLqJ4b ChMk0b"
data-language-for-alternatives="en"
data-language-to-translate-into="es" data-phrase-index="0"
data-number-of-phrases="1"><span>according to the ls command it
seems you copied root.crt instead of</span></span></span>
root.crl<br>
<br>
Yours,<br>
Gabriel<br>
</body>
</html>
Hi Gabriel,
Thank you.
I copied root.crl again and reload postgresql
-bash-4.2$ ls -alrt /var/lib/pgsql/tls
total 24
-rw-r----- 1 postgres postgres 1168 Nov 30 04:20 server.crt
-rw------- 1 postgres postgres 1679 Nov 30 04:20 server.key
-rw-r----- 1 postgres postgres 688 Nov 30 04:20 root.crt
-rw-r----- 1 postgres postgres 410 Nov 30 07:42 root.crl
drwx------ 4 postgres postgres 4096 Nov 30 08:02 ..
drwx------ 2 postgres postgres 4096 Nov 30 23:36 .
-bash-4.2$ psql
psql (11.11)
Type "help" for help.
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# show ssl_crl_file;
ssl_crl_file
-----------------------------
/var/lib/pgsql/tls/root.crl
(1 row)
--From client to connect still can connect not as expect
-bash-4.2$ psql "host=master.pgcluster11.service.consul port=5432
dbname=testdb user=test sslmode=verify-full"
Password:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)
Is there any more configuration need to do please? Thanks
Thanks and best regards
Sun Yi
Gabriel Cabillon <gcabillon@hexa.com.uy> 于2021年11月30日周二 下午10:03写道：
Show quoted text
El 30/11/2021 a las 10:53, Yi Sun escribió:
Hi All,
OS: CentOS 7.6
PG: 11.11Our env already configured ssl
--server postgresql.conf
ssl = 'on'
ssl_ca_file = '/var/lib/pgsql/tls/root.crt'
ssl_cert_file = '/var/lib/pgsql/tls/server.crt'
ssl_key_file = '/var/lib/pgsql/tls/server.key'--client configuration
$ ls -alrt /var/lib/pgsql/.postgresql
total 20
-rw-r--r-- 1 postgres postgres 688 Nov 30 06:46 root.crt
-rw-r--r-- 1 postgres postgres 778 Nov 30 06:46 postgresql.crt
-rw------- 1 postgres postgres 1708 Nov 30 06:47 postgresql.key--From client to connect ssl works
$ psql "host=master.pgcluster11.service.consul port=5432 dbname=testdb
user=test sslmode=verify-full"
Password:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)--Now we want to configure the ssl_crl_file and generated the root.crl
file as below redhat doc--Use openssl to verify, shows "certificate revoked"
# cat /home/sunyi/tls/root.crt /home/sunyi/tls/1/root.crl > /tmp/test_1.pem
# openssl verify -extended_crl -verbose -CAfile /tmp/test_1.pem -crl_check
/home/sunyi/tls/1/server.crt
/home/sunyi/tls/1/server.crt: O = Acronis, OU = DBS, CN =
s12345y-patroni_cluster-507460701
error 23 at 0 depth lookup:certificate revoked--copy root.crl file to /var/lib/pgsql/tls
-bash-4.2$ ls -alrt /var/lib/pgsql/tls
total 20
drwx------ 4 postgres postgres 4096 Nov 30 04:20 ..
-rw-r----- 1 postgres postgres 1164 Nov 30 04:20 server.crt
-rw------- 1 postgres postgres 1679 Nov 30 04:20 server.key
-rw-r----- 1 postgres postgres 688 Nov 30 04:20 root.crt
drwx------ 2 postgres postgres 4096 Nov 30 04:20 .--Configure /var/lib/pgsql/11/data/postgresql.conf
ssl_crl_file = '/var/lib/pgsql/tls/root.crl'--Reload postgresql
$ psql
psql (11.11)
Type "help" for help.postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# show ssl_crl_file;
ssl_crl_file
-----------------------------
/var/lib/pgsql/tls/root.crl
(1 row)--From client to connect still can connect not as expect
$ psql "host=master.pgcluster11.service.consul port=5432 dbname=testdb
user=test sslmode=verify-full"
Password:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)Could you please advise if need any more configuration? Thanks
Thanks and best regards
Sun YiHi,
according to the ls command it seems you copied root.crt instead of
root.crlYours,
Gabriel
At Tue, 30 Nov 2021 21:53:06 +0800, Yi Sun <yinan81@gmail.com> wrote in
# cat /home/sunyi/tls/root.crt /home/sunyi/tls/1/root.crl > /tmp/test_1.pem
# openssl verify -extended_crl -verbose -CAfile /tmp/test_1.pem -crl_check
/home/sunyi/tls/1/server.crt
I guess what you really wanted to revoke was not server.crt but
postgresql.crt.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
Hi Kyotaro,
We want to revoke server certificate, just don't know why doesn't take
affect
https://www.postgresql.org/docs/11/ssl-tcp.html
https://www.postgresql.org/docs/11/runtime-config-connection.html#GUC-SSL-CRL-FILE
Kyotaro Horiguchi <horikyota.ntt@gmail.com> 于2021年12月1日周三 下午2:12写道：
Show quoted text
At Tue, 30 Nov 2021 21:53:06 +0800, Yi Sun <yinan81@gmail.com> wrote in
# cat /home/sunyi/tls/root.crt /home/sunyi/tls/1/root.crl >
/tmp/test_1.pem
# openssl verify -extended_crl -verbose -CAfile /tmp/test_1.pem
-crl_check
/home/sunyi/tls/1/server.crt
I guess what you really wanted to revoke was not server.crt but
postgresql.crt.regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
At Wed, 1 Dec 2021 16:56:11 +0800, Yi Sun <yinan81@gmail.com> wrote in
We want to revoke server certificate, just don't know why doesn't take
affect
https://www.postgresql.org/docs/11/ssl-tcp.html
https://www.postgresql.org/docs/11/runtime-config-connection.html#GUC-SSL-CRL-FILE
Understood. ~/.postgresq/root.crl is required to check server
revokation.
https://www.postgresql.org/docs/11/libpq-ssl.html
To allow server certificate verification, one or more root
certificates must be placed in the file ~/.postgresql/root.crt in the
user's home directory. (On Microsoft Windows the file is named
%APPDATA%\postgresql\root.crt.) Intermediate certificates should also
be added to the file if they are needed to link the certificate chain
sent by the server to the root certificates stored on the client.Certificate Revocation List (CRL) entries are also checked if the file
~/.postgresql/root.crl exists (%APPDATA%\postgresql\root.crl on
Microsoft Windows).
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
Hi Kyotaro
From the description, seems ~/.postgresql/root.crl is store client
revoked certificate
https://www.postgresql.org/docs/11/libpq-ssl.html
~/.postgresql/root.crl certificates revoked by certificate authorities server
certificate must not be on this list
Just don't know why server parameter ssl_crl_file parameter configured but
don't take affect
https://www.postgresql.org/docs/11/runtime-config-connection.html#GUC-SSL-CRL-FILE
ssl_crl_file (string)
Specifies the name of the file containing the SSL server certificate
revocation list (CRL). Relative paths are relative to the data directory.
This parameter can only be set in the postgresql.conf file or on the server
command line. The default is empty, meaning no CRL file is loaded.
Hi.
At Thu, 2 Dec 2021 11:31:26 +0800, Yi Sun <yinan81@gmail.com> wrote in
Hi Kyotaro
From the description, seems ~/.postgresql/root.crl is store client
revoked certificate
No. Revocation is checked on the peer. There's no point for a server
to check for revocation of its own certificate, and actually that
doesn't happen. Revocation of a client certificate is checked on
server side referencing server.crl. Revocation of a server certificate
is checked on client side referencing postgresql.crl. For example,
some web browsers make use of CRL of web *servers*, which is
automatically maintained in background.
You will see it work if you duped the server.crl as
~/.postgresql/root.crl on the client. (I spelled this wrongly in the
previous message..)
https://www.postgresql.org/docs/11/libpq-ssl.html
~/.postgresql/root.crl certificates revoked by certificate authorities server
certificate must not be on this list
Just don't know why server parameter ssl_crl_file parameter configured but
don't take affect
As explained above, it is because the CRL specified by ssl_crl_file
can only be used to verify client certificates.
https://www.postgresql.org/docs/11/runtime-config-connection.html#GUC-SSL-CRL-FILE
ssl_crl_file (string)
Specifies the name of the file containing the SSL server certificate
revocation list (CRL). Relative paths are relative to the data directory.
This parameter can only be set in the postgresql.conf file or on the server
command line. The default is empty, meaning no CRL file is loaded.
Ah, the "server" in "SSL server certificate revocation list" looks
like a noise word, rather misleading, or plain wrong, I'm not sure
which one it actually is.
Anyway I propose change the rephrase as "SSL client certification
revocation list" as attached.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
Attachments:
fix_crl_doc.patchtext/x-patch; charset=us-asciiDownload+8-9
Hi Kyotaro,
Thank you for your explanation, after putting the crl file to client, it
works now, thanks.
Kyotaro Horiguchi <horikyota.ntt@gmail.com> 于2021年12月2日周四 下午12:46写道：
Show quoted text
Hi.
At Thu, 2 Dec 2021 11:31:26 +0800, Yi Sun <yinan81@gmail.com> wrote in
Hi Kyotaro
From the description, seems ~/.postgresql/root.crl is store client
revoked certificateNo. Revocation is checked on the peer. There's no point for a server
to check for revocation of its own certificate, and actually that
doesn't happen. Revocation of a client certificate is checked on
server side referencing server.crl. Revocation of a server certificate
is checked on client side referencing postgresql.crl. For example,
some web browsers make use of CRL of web *servers*, which is
automatically maintained in background.You will see it work if you duped the server.crl as
~/.postgresql/root.crl on the client. (I spelled this wrongly in the
previous message..)https://www.postgresql.org/docs/11/libpq-ssl.html
~/.postgresql/root.crl certificates revoked by certificate authoritiesserver
certificate must not be on this list
Just don't know why server parameter ssl_crl_file parameter configuredbut
don't take affect
As explained above, it is because the CRL specified by ssl_crl_file
can only be used to verify client certificates.https://www.postgresql.org/docs/11/runtime-config-connection.html#GUC-SSL-CRL-FILE
ssl_crl_file (string)
Specifies the name of the file containing the SSL server certificate
revocation list (CRL). Relative paths are relative to the data directory.
This parameter can only be set in the postgresql.conf file or on theserver
command line. The default is empty, meaning no CRL file is loaded.
Ah, the "server" in "SSL server certificate revocation list" looks
like a noise word, rather misleading, or plain wrong, I'm not sure
which one it actually is.Anyway I propose change the rephrase as "SSL client certification
revocation list" as attached.regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
At Fri, 3 Dec 2021 15:41:51 +0800, Yi Sun <yinan81@gmail.com> wrote in
Hi Kyotaro,
Thank you for your explanation, after putting the crl file to client, it
works now, thanks.
Good to hear that. That portion of the documentation has been fixed on
the repository, and it will be released in the next minor releases.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center