pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion on SSL certificate expiry
9+ messages / 3 participants
[nested] [flat]
* Question on SSL certificate expiry
@ 2023-06-01 11:40 Nikhil Shetty <nikhil.dba04@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Nikhil Shetty @ 2023-06-01 11:40 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question on SSL certificate expiry
@ 2023-06-01 13:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nikhil Shetty <nikhil.dba04@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2023-06-01 13:07 UTC (permalink / raw)
To: Nikhil Shetty <nikhil.dba04@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question on SSL certificate expiry
@ 2023-06-01 16:27 Nikhil Shetty <nikhil.dba04@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Nikhil Shetty @ 2023-06-01 16:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question on SSL certificate expiry
@ 2023-06-01 17:00 Nikhil Shetty <nikhil.dba04@gmail.com>
parent: Nikhil Shetty <nikhil.dba04@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Nikhil Shetty @ 2023-06-01 17:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question on SSL certificate expiry
@ 2023-06-03 00:06 Jeff Janes <jeff.janes@gmail.com>
parent: Nikhil Shetty <nikhil.dba04@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Jeff Janes @ 2023-06-03 00:06 UTC (permalink / raw)
To: Nikhil Shetty <nikhil.dba04@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question on SSL certificate expiry
@ 2023-06-04 12:38 Nikhil Shetty <nikhil.dba04@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Nikhil Shetty @ 2023-06-04 12:38 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
>
>>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question on SSL certificate expiry
@ 2023-06-04 15:29 Jeff Janes <jeff.janes@gmail.com>
parent: Nikhil Shetty <nikhil.dba04@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Jeff Janes @ 2023-06-04 15:29 UTC (permalink / raw)
To: Nikhil Shetty <nikhil.dba04@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question on SSL certificate expiry
@ 2023-06-05 04:17 Nikhil Shetty <nikhil.dba04@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Nikhil Shetty @ 2023-06-05 04:17 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
>
> 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
>
>>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question on SSL certificate expiry
@ 2023-06-09 05:03 Nikhil Shetty <nikhil.dba04@gmail.com>
parent: Nikhil Shetty <nikhil.dba04@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Nikhil Shetty @ 2023-06-09 05:03 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>>
>>>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2023-06-09 05:03 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-01 11:40 Question on SSL certificate expiry Nikhil Shetty <nikhil.dba04@gmail.com>
2023-06-01 13:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-06-01 16:27 ` Nikhil Shetty <nikhil.dba04@gmail.com>
2023-06-01 17:00 ` Nikhil Shetty <nikhil.dba04@gmail.com>
2023-06-03 00:06 ` Jeff Janes <jeff.janes@gmail.com>
2023-06-04 12:38 ` Nikhil Shetty <nikhil.dba04@gmail.com>
2023-06-04 15:29 ` Jeff Janes <jeff.janes@gmail.com>
2023-06-05 04:17 ` Nikhil Shetty <nikhil.dba04@gmail.com>
2023-06-09 05:03 ` Nikhil Shetty <nikhil.dba04@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox