Thread: Replication & TLS encryption - how?
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.
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
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. |
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
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.
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
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
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. 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. 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 -- Cybertec | https://www.cybertec-postgresql.com
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
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