Horton

Thread

Commits

  1. Doc: Fix misleading wording of CRL parameters

  1. ssl_crl_file Certificate Revocation List doesn't work for postgresql 11

    Yi Sun <yinan81@gmail.com> — 2021-11-30T13:53:06Z

    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

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

    Gabriel Cabillon <gcabillon@hexa.com.uy> — 2021-11-30T14:03:06Z

    <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
          &gt; /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>

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

    Yi Sun <yinan81@gmail.com> — 2021-11-30T23:46:01Z

    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
>

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

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2021-12-01T05:38:16Z

    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

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

    Yi Sun <yinan81@gmail.com> — 2021-12-01T08:56:11Z

    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
>

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

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2021-12-02T00:28:48Z

    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

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

    Yi Sun <yinan81@gmail.com> — 2021-12-02T03:31:26Z

    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.

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

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2021-12-02T04:46:19Z

    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

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

    Yi Sun <yinan81@gmail.com> — 2021-12-03T07:41:51Z

    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
>

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

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2021-12-06T05:03:10Z

    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