[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: ssl_crl_file Certificate Revocation List doesn't work for postgresql 11
From: Yi Sun <yinan81 () gmail ! com>
Date: 2021-11-30 23:46:01
Message-ID: CABWY_HB-sBaFdEMsKHe+bccOy5TVDpUZnn8DWNFb++q1OsE3yg () mail ! gmail ! com
[Download RAW message or body]
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
>
[Attachment #3 (text/html)]
<div dir="ltr">Hi Gabriel,<div><br></div><div>Thank you.</div><div><br></div><div>I \
copied root.crl again and reload postgresql</div><div><br></div><div>-bash-4.2$ ls \
-alrt /var/lib/pgsql/tls<br>total 24<br>-rw-r----- 1 postgres postgres 1168 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>-rw-r----- 1 postgres postgres 410 Nov 30 07:42 root.crl<br>drwx------ \
4 postgres postgres 4096 Nov 30 08:02 ..<br>drwx------ 2 postgres postgres 4096 Nov \
30 23:36 .<br></div><div><br></div><div>-bash-4.2$ 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><br>postgres=# show \
ssl_crl_file;<br> ssl_crl_file<br>-----------------------------<br> \
/var/lib/pgsql/tls/root.crl<br>(1 row)<br><br></div><div>--From client to connect \
still can connect not as expect<br></div><div>-bash-4.2$ psql \
"host=master.pgcluster11.service.consul port=5432 dbname=testdb user=test \
sslmode=verify-full"</div><div>Password:<br>SSL connection (protocol: TLSv1.2, \
cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: \
off)<br><br></div><div>Is there any more configuration need to do please? \
Thanks</div><div><br></div><div><div>Thanks and best regards</div><font \
color="#888888"><div>Sun \
Yi</div></font></div><div><br></div><div><br></div></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">Gabriel Cabillon <<a \
href="mailto:gcabillon@hexa.com.uy">gcabillon@hexa.com.uy</a>> \
于2021年11月30日周二 下午10:03写道：<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex">
<div>
El 30/11/2021 a las 10:53, Yi Sun escribió:<br>
<blockquote type="cite">
<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_inf \
rastructure/2.1/html/administration_guide/chap-red_hat_update_infrastructure-administration_guide-certification_revocation_list_crl" \
target="_blank">https://access.redhat.com/documentation/en-us/red_hat_update_infrastru \
cture/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 lang="en"><span><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>
</div>
</blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic