pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedReplication & TLS encryption - how?
10+ messages / 3 participants
[nested] [flat]
* Replication & TLS encryption - how?
@ 2021-04-07 16:24 lejeczek <peljasz@yahoo.co.uk>
0 siblings, 1 reply; 10+ messages in thread
From: lejeczek @ 2021-04-07 16:24 UTC (permalink / raw)
To: pgsql-admin
Hi guys.
A novice here thus please go easy on me as I ask this - I
see docs/howtos all over the place be those either talk of
encryption or replication. I failed to find one which blend
these two concepts together - sure it's possible to pgSQL
replication encrypted, right?
If true, could you possible point a doc/howto which covers that?
many thanks, L.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Replication & TLS encryption - how?
@ 2021-04-07 16:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: lejeczek <peljasz@yahoo.co.uk>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2021-04-07 16:36 UTC (permalink / raw)
To: lejeczek <peljasz@yahoo.co.uk>; +Cc: pgsql-admin
lejeczek <peljasz@yahoo.co.uk> writes:
> A novice here thus please go easy on me as I ask this - I
> see docs/howtos all over the place be those either talk of
> encryption or replication. I failed to find one which blend
> these two concepts together - sure it's possible to pgSQL
> replication encrypted, right?
Replication connections work exactly like normal sessions for
this purpose. Just make sure you set any required parameters
in the standby's connection string.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Replication & TLS encryption - how?
@ 2021-04-07 20:12 lejeczek <peljasz@yahoo.co.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: lejeczek @ 2021-04-07 20:12 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
On 07/04/2021 17:36, Tom Lane wrote:
> lejeczek <peljasz@yahoo.co.uk> writes:
>> A novice here thus please go easy on me as I ask this - I
>> see docs/howtos all over the place be those either talk of
>> encryption or replication. I failed to find one which blend
>> these two concepts together - sure it's possible to pgSQL
>> replication encrypted, right?
> Replication connections work exactly like normal sessions for
> this purpose. Just make sure you set any required parameters
> in the standby's connection string.
>
> regards, tom lane
>
>
Thanks. Would you know how '|clientcert=1' fits into the
equation?
With it present in pg_hba.conf pgSQL was not happy saying:
FATAL: connection requires a valid client certificate.
|
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Replication & TLS encryption - how?
@ 2021-04-08 02:59 Laurenz Albe <laurenz.albe@cybertec.at>
parent: lejeczek <peljasz@yahoo.co.uk>
0 siblings, 1 reply; 10+ messages in thread
From: Laurenz Albe @ 2021-04-08 02:59 UTC (permalink / raw)
To: lejeczek <peljasz@yahoo.co.uk>; +Cc: pgsql-admin
On Wed, 2021-04-07 at 21:12 +0100, lejeczek wrote:
> On 07/04/2021 17:36, Tom Lane wrote:
> > lejeczek <peljasz@yahoo.co.uk> writes:
> > > A novice here thus please go easy on me as I ask this - I
> > > see docs/howtos all over the place be those either talk of
> > > encryption or replication. I failed to find one which blend
> > > these two concepts together - sure it's possible to pgSQL
> > > replication encrypted, right?
> > Replication connections work exactly like normal sessions for
> > this purpose. Just make sure you set any required parameters
> > in the standby's connection string.
> >
> > regards, tom lane
> >
> >
> Thanks. Would you know how '|clientcert=1' fits into the
> equation?
> With it present in pg_hba.conf pgSQL was not happy saying:
>
> FATAL: connection requires a valid client certificate.
Then include "sslcert" in "primary_conninfo".
You can use all the libpq connection parameters:
https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Replication & TLS encryption - how?
@ 2021-04-08 08:21 lejeczek <peljasz@yahoo.co.uk>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 10+ messages in thread
From: lejeczek @ 2021-04-08 08:21 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
On 08/04/2021 03:59, Laurenz Albe wrote:
> On Wed, 2021-04-07 at 21:12 +0100, lejeczek wrote:
>> On 07/04/2021 17:36, Tom Lane wrote:
>>> lejeczek <peljasz@yahoo.co.uk> writes:
>>>> A novice here thus please go easy on me as I ask this - I
>>>> see docs/howtos all over the place be those either talk of
>>>> encryption or replication. I failed to find one which blend
>>>> these two concepts together - sure it's possible to pgSQL
>>>> replication encrypted, right?
>>> Replication connections work exactly like normal sessions for
>>> this purpose. Just make sure you set any required parameters
>>> in the standby's connection string.
>>>
>>> regards, tom lane
>>>
>>>
>> Thanks. Would you know how '|clientcert=1' fits into the
>> equation?
>> With it present in pg_hba.conf pgSQL was not happy saying:
>>
>> FATAL: connection requires a valid client certificate.
> Then include "sslcert" in "primary_conninfo".
>
> You can use all the libpq connection parameters:
> https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS
>
> Yours,
> Laurenz Albe
This below is what 'pg_basebackup' generated on the master
itself, master which already was configured for TLS/certs.
primary_conninfo = 'user=replicator password=''9897''
channel_binding=prefer host=10.1.1.224 port=5432
sslmode=prefer sslcompression=0
ssl_min_protocol_version=TLSv1.2 gssencmode=prefer
krbsrvname=postgres target_session_attrs=any'
And with master's:
hostssl replication replicator 10.1.1.223/32 md5
clientcert=1
standby would not connect, but without 'clientcert=1' it
seems to work.
I guess my question - as any novice's - would be: is
replication really 100% encrypted? How to confirm-test it?
Lastly: is there anything more at 'pg_basebackup' stage user
can do to have 'configs' more ready, more complete for 'full
encryption' when starting with master already configured
with TLS?
I'm on 13.2 version.
many thanks, L.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Replication & TLS encryption - how?
@ 2021-04-08 10:27 Laurenz Albe <laurenz.albe@cybertec.at>
parent: lejeczek <peljasz@yahoo.co.uk>
0 siblings, 1 reply; 10+ messages in thread
From: Laurenz Albe @ 2021-04-08 10:27 UTC (permalink / raw)
To: lejeczek <peljasz@yahoo.co.uk>; +Cc: pgsql-admin
On Thu, 2021-04-08 at 09:21 +0100, lejeczek wrote:
> On 08/04/2021 03:59, Laurenz Albe wrote:
> > On Wed, 2021-04-07 at 21:12 +0100, lejeczek wrote:
> > > On 07/04/2021 17:36, Tom Lane wrote:
> > > > lejeczek <peljasz@yahoo.co.uk> writes:
> > > > > A novice here thus please go easy on me as I ask this - I
> > > > > see docs/howtos all over the place be those either talk of
> > > > > encryption or replication. I failed to find one which blend
> > > > > these two concepts together - sure it's possible to pgSQL
> > > > > replication encrypted, right?
> > >
> > > Thanks. Would you know how '|clientcert=1' fits into the
> > > equation?
> > > With it present in pg_hba.conf pgSQL was not happy saying:
> > >
> > > FATAL: connection requires a valid client certificate.
> >
> > Then include "sslcert" in "primary_conninfo".
> >
> > You can use all the libpq connection parameters:
> > https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS
>
> This below is what 'pg_basebackup' generated on the master
> itself, master which already was configured for TLS/certs.
>
> primary_conninfo = 'user=replicator password=''9897''
> channel_binding=prefer host=10.1.1.224 port=5432
> sslmode=prefer sslcompression=0
> ssl_min_protocol_version=TLSv1.2 gssencmode=prefer
> krbsrvname=postgres target_session_attrs=any'
>
> And with master's:
>
> hostssl replication replicator 10.1.1.223/32 md5
> clientcert=1
I repeat: add "sslcert" to "primary_conninfo".
Of course you will need a private key that matches the certificate.
> I guess my question - as any novice's - would be: is
> replication really 100% encrypted? How to confirm-test it?
Look at the appropriate line in "pg_stat_ssl".
> Lastly: is there anything more at 'pg_basebackup' stage user
> can do to have 'configs' more ready, more complete for 'full
> encryption' when starting with master already configured
> with TLS?
> I'm on 13.2 version.
No, this always requires manual configuration.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Replication & TLS encryption - how?
@ 2021-04-08 11:37 lejeczek <peljasz@yahoo.co.uk>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 10+ messages in thread
From: lejeczek @ 2021-04-08 11:37 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
On 08/04/2021 11:27, Laurenz Albe wrote:
> On Thu, 2021-04-08 at 09:21 +0100, lejeczek wrote:
>> On 08/04/2021 03:59, Laurenz Albe wrote:
>>> On Wed, 2021-04-07 at 21:12 +0100, lejeczek wrote:
>>>> On 07/04/2021 17:36, Tom Lane wrote:
>>>>> lejeczek <peljasz@yahoo.co.uk> writes:
>>>>>> A novice here thus please go easy on me as I ask this - I
>>>>>> see docs/howtos all over the place be those either talk of
>>>>>> encryption or replication. I failed to find one which blend
>>>>>> these two concepts together - sure it's possible to pgSQL
>>>>>> replication encrypted, right?
>>>> Thanks. Would you know how '|clientcert=1' fits into the
>>>> equation?
>>>> With it present in pg_hba.conf pgSQL was not happy saying:
>>>>
>>>> FATAL: connection requires a valid client certificate.
>>> Then include "sslcert" in "primary_conninfo".
>>>
>>> You can use all the libpq connection parameters:
>>> https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS
>> This below is what 'pg_basebackup' generated on the master
>> itself, master which already was configured for TLS/certs.
>>
>> primary_conninfo = 'user=replicator password=''9897''
>> channel_binding=prefer host=10.1.1.224 port=5432
>> sslmode=prefer sslcompression=0
>> ssl_min_protocol_version=TLSv1.2 gssencmode=prefer
>> krbsrvname=postgres target_session_attrs=any'
>>
>> And with master's:
>>
>> hostssl replication replicator 10.1.1.223/32 md5
>> clientcert=1
> I repeat: add "sslcert" to "primary_conninfo".
> Of course you will need a private key that matches the certificate.
I get what you were saying but I also wondered - when I
showed my "primary_conninfo" & pg_hba: why does replication
appear to work without the bits you mention and what is the
significance of 'clientcert=1' in all this.
>
>> I guess my question - as any novice's - would be: is
>> replication really 100% encrypted? How to confirm-test it?
> Look at the appropriate line in "pg_stat_ssl".
master/provider:
-[ RECORD 1 ]-+-----------------------
pid | 78705
ssl | t
version | TLSv1.3
cipher | TLS_AES_256_GCM_SHA384
bits | 256
compression | f
client_dn |
client_serial |
issuer_dn |
-[ RECORD 2 ]-+-----------------------
pid | 78867
ssl | f
version |
cipher |
bits |
compression |
client_dn |
client_serial |
issuer_dn |
standby:
-[ RECORD 1 ]-+--------
pid | 3119249
ssl | f
version |
cipher |
bits |
compression |
client_dn |
client_serial |
issuer_dn |
Does that confirm healthy & encrypted replication?
many thanks, L.
>> Lastly: is there anything more at 'pg_basebackup' stage user
>> can do to have 'configs' more ready, more complete for 'full
>> encryption' when starting with master already configured
>> with TLS?
>> I'm on 13.2 version.
> No, this always requires manual configuration.
>
> Yours,
> Laurenz Albe
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Replication & TLS encryption - how?
@ 2021-04-09 09:09 Laurenz Albe <laurenz.albe@cybertec.at>
parent: lejeczek <peljasz@yahoo.co.uk>
0 siblings, 1 reply; 10+ messages in thread
From: Laurenz Albe @ 2021-04-09 09:09 UTC (permalink / raw)
To: lejeczek <peljasz@yahoo.co.uk>; +Cc: pgsql-admin
On Thu, 2021-04-08 at 12:37 +0100, lejeczek wrote:
> I get what you were saying but I also wondered - when I
> showed my "primary_conninfo" & pg_hba: why does replication
> appear to work without the bits you mention and what is the
> significance of 'clientcert=1' in all this.
Replication works just fine when unencrypted.
"clientcert=1" (in versions before v12) means that the server will
reject a client connection unless it sends a client certificate that is
signed by an authority that the server recognizes.
If you omit the option (or set it to 0), the server doesn't care
if the client sends a certificate or not.
Note that by default, PostgreSQL uses SSL only to encrypt the
connection, not to verify the identity of the participants.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Replication & TLS encryption - how?
@ 2021-04-09 15:43 lejeczek <peljasz@yahoo.co.uk>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 10+ messages in thread
From: lejeczek @ 2021-04-09 15:43 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
On 09/04/2021 10:09, Laurenz Albe wrote:
> On Thu, 2021-04-08 at 12:37 +0100, lejeczek wrote:
>> I get what you were saying but I also wondered - when I
>> showed my "primary_conninfo" & pg_hba: why does replication
>> appear to work without the bits you mention and what is the
>> significance of 'clientcert=1' in all this.
> Replication works just fine when unencrypted.
>
> "clientcert=1" (in versions before v12) means that the server will
> reject a client connection unless it sends a client certificate that is
> signed by an authority that the server recognizes.
And by 'recognizes' we would mean the one from 'ssl_ca_file'
which, if true then I still have to wonder why my pgSQLs
were not happy.
My first guess and first question at the same time would be
- could be because how my certs were crafted?
Beyond "regular" certs params, or something "extra" in other
words, I requested my certs to have 'Extended Key Usage'
Thus my certs have both: TLS Web Server Authentication, TLS
Web Client Authentication which I thought is a 'must' since
pgSQL in replication/clusters is both server and the
client.(no? )
>
> If you omit the option (or set it to 0), the server doesn't care
> if the client sends a certificate or not.
> Note that by default, PostgreSQL uses SSL only to encrypt the
> connection, not to verify the identity of the participants.
>
> From v12 on, there are the two values "verify-ca" and "verify-full".
> The former corresponds to the old "1", the latter is new and also
> requires that the common name in the certificate matches the user name.
>
>>>> I guess my question - as any novice's - would be: is
>>>> replication really 100% encrypted? How to confirm-test it?
>>> Look at the appropriate line in "pg_stat_ssl".
>> master/provider:
>> -[ RECORD 1 ]-+-----------------------
>> pid | 78705
>> ssl | t
>> version | TLSv1.3
>> cipher | TLS_AES_256_GCM_SHA384
>> bits | 256
>> compression | f
>> client_dn |
>> client_serial |
>> issuer_dn |
>> -[ RECORD 2 ]-+-----------------------
>> pid | 78867
>> ssl | f
>> version |
>> cipher |
>> bits |
>> compression |
>> client_dn |
>> client_serial |
>> issuer_dn |
>>
>> standby:
>> -[ RECORD 1 ]-+--------
>> pid | 3119249
>> ssl | f
>> version |
>> cipher |
>> bits |
>> compression |
>> client_dn |
>> client_serial |
>> issuer_dn |
>>
>> Does that confirm healthy & encrypted replication?
> Compare with the lines in "pg_stat_replication". If the entry with "ssl" = true
> (pid 78705) has the same PID as the entry in "pg_stat_replication", then that
> connection is encrypted, yes.
I think those match, but what is that 'Record 3' (which has
no match in 'pg_stat_replication', I can guess but I rather
ask) , master-supplier with two standbays is my setup.
-[ RECORD 1 ]-+-----------------------
pid | 108394
ssl | t
version | TLSv1.3
cipher | TLS_AES_256_GCM_SHA384
bits | 256
compression | f
client_dn |
client_serial |
issuer_dn |
-[ RECORD 2 ]-+-----------------------
pid | 108395
ssl | t
version | TLSv1.3
cipher | TLS_AES_256_GCM_SHA384
bits | 256
compression | f
client_dn |
client_serial |
issuer_dn |
-[ RECORD 3 ]-+-----------------------
pid | 111811
ssl | f
version |
cipher |
bits |
compression |
client_dn |
client_serial |
issuer_dn |
-[ RECORD 1 ]----+------------------------------
pid | 108394
usesysid | 16384
usename | replicator
application_name | 10.1.1.223
client_addr | 10.1.1.223
client_hostname |
client_port | 55734
backend_start | 2021-04-09 11:23:54.721108-04
backend_xmin |
state | streaming
sent_lsn | 0/D004FE0
write_lsn | 0/D004FE0
flush_lsn | 0/D004FE0
replay_lsn | 0/D004FE0
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2021-04-09 11:29:06.233683-04
-[ RECORD 2 ]----+------------------------------
pid | 108395
usesysid | 16384
usename | replicator
application_name | 10.1.1.224
client_addr | 10.1.1.224
client_hostname |
client_port | 60336
backend_start | 2021-04-09 11:23:54.75805-04
backend_xmin |
state | streaming
sent_lsn | 0/D004FE0
write_lsn | 0/D004FE0
flush_lsn | 0/D004FE0
replay_lsn | 0/D004FE0
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2021-04-09 11:29:06.387592-04
many thanks, L
> If it is healthy or not can be seen in "pg_stat_replication".
> Check the "state" and if the diverse LSNs are close enough or if there is lag.
>
> Yours,
> Laurenz Albe
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Replication & TLS encryption - how?
@ 2021-04-09 17:55 Laurenz Albe <laurenz.albe@cybertec.at>
parent: lejeczek <peljasz@yahoo.co.uk>
0 siblings, 0 replies; 10+ messages in thread
From: Laurenz Albe @ 2021-04-09 17:55 UTC (permalink / raw)
To: lejeczek <peljasz@yahoo.co.uk>; +Cc: pgsql-admin
On Fri, 2021-04-09 at 16:43 +0100, lejeczek wrote:
> > > I get what you were saying but I also wondered - when I
> > > showed my "primary_conninfo" & pg_hba: why does replication
> > > appear to work without the bits you mention and what is the
> > > significance of 'clientcert=1' in all this.
> >
> > Replication works just fine when unencrypted.
> >
> > "clientcert=1" (in versions before v12) means that the server will
> > reject a client connection unless it sends a client certificate that is
> > signed by an authority that the server recognizes.
>
> And by 'recognizes' we would mean the one from 'ssl_ca_file'
> which, if true then I still have to wonder why my pgSQLs
> were not happy.
> My first guess and first question at the same time would be
> - could be because how my certs were crafted?
> Beyond "regular" certs params, or something "extra" in other
> words, I requested my certs to have 'Extended Key Usage'
> Thus my certs have both: TLS Web Server Authentication, TLS
> Web Client Authentication which I thought is a 'must' since
> pgSQL in replication/clusters is both server and the
> client.(no? )
This seems to be an SSL question unrelated to PostgreSQL.
Perhaps you can use SSL tools like "openssl s_client" and
"openssl s_server" to debug this.
> > > Does that confirm healthy & encrypted replication?
> >
> > Compare with the lines in "pg_stat_replication". If the entry with "ssl" = true
> > (pid 78705) has the same PID as the entry in "pg_stat_replication", then that
> > connection is encrypted, yes.
>
> I think those match, but what is that 'Record 3' (which has
> no match in 'pg_stat_replication', I can guess but I rather
> ask) , master-supplier with two standbays is my setup.
> -[ RECORD 1 ]-+-----------------------
> pid | 108394
> ssl | t
> version | TLSv1.3
> cipher | TLS_AES_256_GCM_SHA384
> bits | 256
> compression | f
> client_dn |How should I know?
> client_serial |
> issuer_dn |
> -[ RECORD 2 ]-+-----------------------
> pid | 108395
> ssl | t
> version | TLSv1.3
> cipher | TLS_AES_256_GCM_SHA384
> bits | 256
> compression | f
> client_dn |
> client_serial |
> issuer_dn |
> -[ RECORD 3 ]-+-----------------------
> pid | 111811
> ssl | f
> version |
> cipher |
> bits |
> compression |
> client_dn |
> client_serial |
> issuer_dn |
It might well be your own local connection on which you are
running the query...
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2021-04-09 17:55 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-04-07 16:24 Replication & TLS encryption - how? lejeczek <peljasz@yahoo.co.uk>
2021-04-07 16:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-04-07 20:12 ` lejeczek <peljasz@yahoo.co.uk>
2021-04-08 02:59 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-04-08 08:21 ` lejeczek <peljasz@yahoo.co.uk>
2021-04-08 10:27 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-04-08 11:37 ` lejeczek <peljasz@yahoo.co.uk>
2021-04-09 09:09 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-04-09 15:43 ` lejeczek <peljasz@yahoo.co.uk>
2021-04-09 17:55 ` Laurenz Albe <laurenz.albe@cybertec.at>
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