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

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(at)hexa(dot)com(dot)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
>