Re: Replication & TLS encryption - how?

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