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
