Re: Replication & TLS encryption - how? - Mailing list pgsql-admin
From | lejeczek |
---|---|
Subject | Re: Replication & TLS encryption - how? |
Date | |
Msg-id | 0918fe8c-05a7-f381-2346-4857cc3479e9@yahoo.co.uk Whole thread Raw |
In response to | Re: Replication & TLS encryption - how? (Laurenz Albe <laurenz.albe@cybertec.at>) |
Responses |
Re: Replication & TLS encryption - how?
|
List | 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
pgsql-admin by date: