Обсуждение: Question on SSL certificate expiry

Поиск
Список
Период
Сортировка

Question on SSL certificate expiry

От
Nikhil Shetty
Дата:
Hi Team,

We were using MTLS to connect to the database. We noticed that even after server certificates expired the client was able to connect to the database. 

1. Doesn't postgres check the expiry date of the certificate?

Thanks,
Nikhil

Re: Question on SSL certificate expiry

От
Tom Lane
Дата:
Nikhil Shetty <nikhil.dba04@gmail.com> writes:
> We were using MTLS to connect to the database. We noticed that even after
> server certificates expired the client was able to connect to the database.

> 1. Doesn't postgres check the expiry date of the certificate?

Postgres does not.  The openssl library can.  The most likely
guess, on the basis of the next-to-zero details you provided,
is that the connection is succeeding via some method that doesn't
require the client to check the server's certificate --- for
instance, a completely unencrypted connection.

            regards, tom lane

Re: Question on SSL certificate expiry

От
Nikhil Shetty
Дата:
Hi Tom,

We are using verify-full on both client and server. 

Server Side pg_hba.conf

hostssl all <user> <ip> cert clientcert=1


Server Side SSL

postgres=# show ssl_cert_file ;

            ssl_cert_file             

--------------------------------------

 /data/server.cert


postgres=# show ssl_ca_file ;

                 ssl_ca_file                 

---------------------------------------------

 /data/ca-cert.pem


postgres=# show ssl_key_file ;

            ssl_key_file             

-------------------------------------

 /data/server.key


Client side SSL

export PGSSLROOTCERT="ca.pem"                                       

export PGSSLMODE="verify-full"                               

export PGSSLCERT="cert.pem"

export PGSSLKEY="cert.key" 



Thanks,

Nikhil


On Thu, Jun 1, 2023 at 6:37 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Nikhil Shetty <nikhil.dba04@gmail.com> writes:
> We were using MTLS to connect to the database. We noticed that even after
> server certificates expired the client was able to connect to the database.

> 1. Doesn't postgres check the expiry date of the certificate?

Postgres does not.  The openssl library can.  The most likely
guess, on the basis of the next-to-zero details you provided,
is that the connection is succeeding via some method that doesn't
require the client to check the server's certificate --- for
instance, a completely unencrypted connection.

                        regards, tom lane

Re: Question on SSL certificate expiry

От
Nikhil Shetty
Дата:
If we provide the CRL then the CRL will be referred and the connection might not go through but the CRL takes atleast 12 hours to reflect the expired certificate.

We wanted to understand if the connection can be rejected based on the 'Expiry date' in the server certificate even without referring the CRL? 

Thanks,
Nikhil

On Thu, Jun 1, 2023 at 9:57 PM Nikhil Shetty <nikhil.dba04@gmail.com> wrote:
Hi Tom,

We are using verify-full on both client and server. 

Server Side pg_hba.conf

hostssl all <user> <ip> cert clientcert=1


Server Side SSL

postgres=# show ssl_cert_file ;

            ssl_cert_file             

--------------------------------------

 /data/server.cert


postgres=# show ssl_ca_file ;

                 ssl_ca_file                 

---------------------------------------------

 /data/ca-cert.pem


postgres=# show ssl_key_file ;

            ssl_key_file             

-------------------------------------

 /data/server.key


Client side SSL

export PGSSLROOTCERT="ca.pem"                                       

export PGSSLMODE="verify-full"                               

export PGSSLCERT="cert.pem"

export PGSSLKEY="cert.key" 



Thanks,

Nikhil


On Thu, Jun 1, 2023 at 6:37 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Nikhil Shetty <nikhil.dba04@gmail.com> writes:
> We were using MTLS to connect to the database. We noticed that even after
> server certificates expired the client was able to connect to the database.

> 1. Doesn't postgres check the expiry date of the certificate?

Postgres does not.  The openssl library can.  The most likely
guess, on the basis of the next-to-zero details you provided,
is that the connection is succeeding via some method that doesn't
require the client to check the server's certificate --- for
instance, a completely unencrypted connection.

                        regards, tom lane

Re: Question on SSL certificate expiry

От
Jeff Janes
Дата:
On Thu, Jun 1, 2023 at 7:40 AM Nikhil Shetty <nikhil.dba04@gmail.com> wrote:
Hi Team,

We were using MTLS to connect to the database. We noticed that even after server certificates expired the client was able to connect to the database. 

1. Doesn't postgres check the expiry date of the certificate?

I think PostgreSQL doesn't check it, but the ssl library does. And it works for me.  After setting the system clock ahead a year (minus 12 hours, because I messed up the am/pm thing), I start getting this on the server side log file:

11863 [unknown]  08P01 2024-06-02 07:38:11.538 EDT LOG:  could not accept SSL connection: sslv3 alert certificate expired

It is weird that that message ends up in the server's log file, as it is the client which is doing the rejecting, not the server.  So you would think the client would get the details and the server would get the vague conclusion.  But it is certainly not the only ssl error reporting oddity I've seen.

Re: Question on SSL certificate expiry

От
Nikhil Shetty
Дата:
Hi Jeff

I am not getting this error when I tried using psql 

I think PostgreSQL doesn't check it, but the ssl library does

Do you mean the psql client(libpq) will not be able to check?

It is weird that that message ends up in the server's log file, as it is the client which is doing the rejecting, not the server.  So you would think the client would get the details and the server would get the vague conclusion.  But it is certainly not the only ssl error reporting oddity I've seen.
 
Are you saying the client will be able to login but the error will be reported only in the server log?

Thanks,
Nikhil



On Sat, Jun 3, 2023 at 5:37 AM Jeff Janes <jeff.janes@gmail.com> wrote:
On Thu, Jun 1, 2023 at 7:40 AM Nikhil Shetty <nikhil.dba04@gmail.com> wrote:
Hi Team,

We were using MTLS to connect to the database. We noticed that even after server certificates expired the client was able to connect to the database. 

1. Doesn't postgres check the expiry date of the certificate?

I think PostgreSQL doesn't check it, but the ssl library does. And it works for me.  After setting the system clock ahead a year (minus 12 hours, because I messed up the am/pm thing), I start getting this on the server side log file:

11863 [unknown]  08P01 2024-06-02 07:38:11.538 EDT LOG:  could not accept SSL connection: sslv3 alert certificate expired

It is weird that that message ends up in the server's log file, as it is the client which is doing the rejecting, not the server.  So you would think the client would get the details and the server would get the vague conclusion.  But it is certainly not the only ssl error reporting oddity I've seen.

Re: Question on SSL certificate expiry

От
Jeff Janes
Дата:
On Sun, Jun 4, 2023 at 8:38 AM Nikhil Shetty <nikhil.dba04@gmail.com> wrote:
Hi Jeff

I am not getting this error when I tried using psql

What is your OS and version, your version of PostgreSQL (client, if not the same installation as the server), your version of SSL support, and how did you install the client?
 
 

I think PostgreSQL doesn't check it, but the ssl library does

Do you mean the psql client(libpq) will not be able to check?

It means the code which does the checking is not in the PostgreSQL source code, but rather in your ssl library, presumably openssl.
 

It is weird that that message ends up in the server's log file, as it is the client which is doing the rejecting, not the server.  So you would think the client would get the details and the server would get the vague conclusion.  But it is certainly not the only ssl error reporting oddity I've seen.
 
Are you saying the client will be able to login but the error will be reported only in the server log?

No, the client fails with the vague 'psql: error: connection to server at "192.168.0.14", port 5432 failed: SSL error: certificate verify failed'.  The server log is where the more detailed reason for the failure goes.

Cheers,

Jeff

Re: Question on SSL certificate expiry

От
Nikhil Shetty
Дата:
What is your OS and version, your version of PostgreSQL (client, if not the same installation as the server), your version of SSL support, and how did you install the client?
Database is running on Postgres v13.9 and hosted on RHEL8 . Client is MacOS with psql version 14.

Openssl on MacOS

  openssl version - LibreSSL 3.3.6

Thanks,
Nikhil

On Sun, Jun 4, 2023 at 8:59 PM Jeff Janes <jeff.janes@gmail.com> wrote:
On Sun, Jun 4, 2023 at 8:38 AM Nikhil Shetty <nikhil.dba04@gmail.com> wrote:
Hi Jeff

I am not getting this error when I tried using psql

What is your OS and version, your version of PostgreSQL (client, if not the same installation as the server), your version of SSL support, and how did you install the client?
 
 

I think PostgreSQL doesn't check it, but the ssl library does

Do you mean the psql client(libpq) will not be able to check?

It means the code which does the checking is not in the PostgreSQL source code, but rather in your ssl library, presumably openssl.
 

It is weird that that message ends up in the server's log file, as it is the client which is doing the rejecting, not the server.  So you would think the client would get the details and the server would get the vague conclusion.  But it is certainly not the only ssl error reporting oddity I've seen.
 
Are you saying the client will be able to login but the error will be reported only in the server log?

No, the client fails with the vague 'psql: error: connection to server at "192.168.0.14", port 5432 failed: SSL error: certificate verify failed'.  The server log is where the more detailed reason for the failure goes.

Cheers,

Jeff

Re: Question on SSL certificate expiry

От
Nikhil Shetty
Дата:
I see it is working now but it takes a little bit of time. 

Thanks Jeff, Tom!


On Mon, Jun 5, 2023 at 9:47 AM Nikhil Shetty <nikhil.dba04@gmail.com> wrote:
What is your OS and version, your version of PostgreSQL (client, if not the same installation as the server), your version of SSL support, and how did you install the client?
Database is running on Postgres v13.9 and hosted on RHEL8 . Client is MacOS with psql version 14.

Openssl on MacOS

  openssl version - LibreSSL 3.3.6

Thanks,
Nikhil

On Sun, Jun 4, 2023 at 8:59 PM Jeff Janes <jeff.janes@gmail.com> wrote:
On Sun, Jun 4, 2023 at 8:38 AM Nikhil Shetty <nikhil.dba04@gmail.com> wrote:
Hi Jeff

I am not getting this error when I tried using psql

What is your OS and version, your version of PostgreSQL (client, if not the same installation as the server), your version of SSL support, and how did you install the client?
 
 

I think PostgreSQL doesn't check it, but the ssl library does

Do you mean the psql client(libpq) will not be able to check?

It means the code which does the checking is not in the PostgreSQL source code, but rather in your ssl library, presumably openssl.
 

It is weird that that message ends up in the server's log file, as it is the client which is doing the rejecting, not the server.  So you would think the client would get the details and the server would get the vague conclusion.  But it is certainly not the only ssl error reporting oddity I've seen.
 
Are you saying the client will be able to login but the error will be reported only in the server log?

No, the client fails with the vague 'psql: error: connection to server at "192.168.0.14", port 5432 failed: SSL error: certificate verify failed'.  The server log is where the more detailed reason for the failure goes.

Cheers,

Jeff