Re: Replication & TLS encryption - how? - Mailing list 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.

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