Home > mailing lists

Thread: ssl_crl_file Certificate Revocation List doesn't work for postgresql 11

ssl_crl_file Certificate Revocation List doesn't work for postgresql 11

From

Yi Sun

Date:

30 November 2021, 16:53:06

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

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

--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

Could you please advise if need any more configuration? Thanks

Thanks and best regards

Sun Yi

Re: ssl_crl_file Certificate Revocation List doesn't work for postgresql 11

From

Yi Sun

Date:

01 December 2021, 02:46:01

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写道：

El 30/11/2021 a las 10:53, Yi Sun escribió:
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

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

--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

Hi,

according to the ls command it seems you copied root.crt instead of root.crl

Yours,
Gabriel

Re: ssl_crl_file Certificate Revocation List doesn't work for postgresql 11

From

Kyotaro Horiguchi

Date:

01 December 2021, 08:38:16

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

Re: ssl_crl_file Certificate Revocation List doesn't work for postgresql 11

From

Yi Sun

Date:

01 December 2021, 11:56:11

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写道：

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

Re: ssl_crl_file Certificate Revocation List doesn't work for postgresql 11

From

Kyotaro Horiguchi

Date:

02 December 2021, 03:28:48

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

Re: ssl_crl_file Certificate Revocation List doesn't work for postgresql 11

From

Yi Sun

Date:

02 December 2021, 06:31:26

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.

Re: ssl_crl_file Certificate Revocation List doesn't work for postgresql 11

From

Kyotaro Horiguchi

Date:

02 December 2021, 07:46:19

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
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index ab617c7b86..4ac617615c 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1248,7 +1248,7 @@ include_dir 'conf.d'
       </term>
       <listitem>
        <para>
-        Specifies the name of the file containing the SSL server certificate
+        Specifies the name of the file containing the SSL client certificate
         revocation list (CRL).
         Relative paths are relative to the data directory.
         This parameter can only be set in the <filename>postgresql.conf</filename>
@@ -1267,7 +1267,7 @@ include_dir 'conf.d'
       </term>
       <listitem>
        <para>
-        Specifies the name of the directory containing the SSL server
+        Specifies the name of the directory containing the SSL client
         certificate revocation list (CRL).  Relative paths are relative to the
         data directory.  This parameter can only be set in
         the <filename>postgresql.conf</filename> file or on the server command
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index c17d33a54f..eb3a0c6b55 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -1742,11 +1742,10 @@ postgresql://%2Fvar%2Flib%2Fpostgresql/dbname
       <term><literal>sslcrl</literal></term>
       <listitem>
        <para>
-        This parameter specifies the file name of the SSL certificate
+        This parameter specifies the file name of the SSL server certificate
         revocation list (CRL).  Certificates listed in this file, if it
-        exists, will be rejected while attempting to authenticate the
-        server's certificate.  If neither
-        <xref linkend='libpq-connect-sslcrl'/> nor
+        exists, will be rejected while attempting to authenticate the server's
+        certificate.  If neither <xref linkend='libpq-connect-sslcrl'/> nor
         <xref linkend='libpq-connect-sslcrldir'/> is set, this setting is
         taken as
         <filename>~/.postgresql/root.crl</filename>.
@@ -1758,9 +1757,9 @@ postgresql://%2Fvar%2Flib%2Fpostgresql/dbname
       <term><literal>sslcrldir</literal></term>
       <listitem>
        <para>
-        This parameter specifies the directory name of the SSL certificate
-        revocation list (CRL).  Certificates listed in the files in this
-        directory, if it exists, will be rejected while attempting to
+        This parameter specifies the directory name of the SSL server
+        certificate revocation list (CRL).  Certificates listed in the files
+        in this directory, if it exists, will be rejected while attempting to
         authenticate the server's certificate.
        </para>

Re: ssl_crl_file Certificate Revocation List doesn't work for postgresql 11

From

Yi Sun

Date:

03 December 2021, 10:41:51

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写道：

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

Re: ssl_crl_file Certificate Revocation List doesn't work for postgresql 11

From

Kyotaro Horiguchi

Date:

06 December 2021, 08:03:10

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