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

[Yi Sun <yinan81@xxxxxxxxx>]

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@xxxxxxxxxxx> 于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