Subject: Re: Replication & TLS encryption - how?
Cc: pgsql-admin@postgresql.org
References: <54a5bf2b-0504-02c0-e5a2-ab24173abcdb.ref@yahoo.co.uk>
 <54a5bf2b-0504-02c0-e5a2-ab24173abcdb@yahoo.co.uk>
 <1596782.1617813374@sss.pgh.pa.us>
 <7e89a9be-e788-23fc-6b1b-2ee67586d452@yahoo.co.uk>
 <d7c701fd45117f1281f7342be412c5297610755f.camel@cybertec.at>
 <a217cf80-b5be-443f-f3df-445ee7b88b37@yahoo.co.uk>
 <84304703ff2131cb3302eaf12b31a235b25a76a6.camel@cybertec.at>
From: lejeczek <peljasz@yahoo.co.uk>
Message-ID: <9d29a3c7-80de-4408-9197-e5ce1dd54a8e@yahoo.co.uk>
Date: Thu, 8 Apr 2021 12:37:04 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.8.1
MIME-Version: 1.0
In-Reply-To: <84304703ff2131cb3302eaf12b31a235b25a76a6.camel@cybertec.at>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Content-Length: 3122
Archived-At: 
 <https://www.postgresql.org/message-id/9d29a3c7-80de-4408-9197-e5ce1dd54a8e%40yahoo.co.uk>
Precedence: bulk


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