Re: SSL between Primary and Seconday PostgreSQL DBs

This is great, thanks.  I have not heard of verify-full but I will try that and let you know if I have issues.

Susan Joseph
sandajoseph@verizon.net

-----Original Message-----
From: Stephen Frost <sfrost@snowman.net>
To: Susan Joseph <sandajoseph@verizon.net>
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Sent: Thu, Sep 3, 2020 8:28 am
Subject: Re: SSL between Primary and Seconday PostgreSQL DBs

Greetings,

* Susan Joseph (sandajoseph@verizon.net) wrote:
>    - primary_conninfo = 'user=rep_user passfile=''/data/.pgpass'' host=<Primary DB IP> port=5432 sslmode=verify-ca sslcert=client.crt sslkey=client.key sslcompression=0 target_session_attrs=any'

You really should be using sslmode=verify-full, otherwise any
certificate signed by a trusted CA on the server side is accepted.

Also, you shouldn't really need to pass in 'passfile' above...

>    - On the Secondary I edit the pg_hba.conf file and change the rep_user line to:
>   
>    - hostssl          replication         rep_user           <primary IP>/32      cert clientcert=1

Saying clientcert=1 when using cert auth really shouldn't be needed.

> My questions are: 
>    - Do I need to set the information in the Secondary postgresql.conf?  Originally I did not set this and everything worked but I saw errors in my log files that said to do SSL these needed to be set so I went back and set them.  Are there pgsql commands I can run to test that my SSL is working in both directions? 

The only connection you're talking about here is from the secondary to
the primary and for that you just need the primary_conninfo settings for
the secondary set up correctly and the SSL settings on the primary.
However, if you want people to be able to make SSL connections to the
secondary, then you need to configure SSL on the secondary.

As for testing the connection, see the pg_stat_ssl view.

>    - Are my pg_hba.conf files set correctly?  Is that how you get SSL "turned on" for communications between the primary and the rep_user account?

'hostssl' will only match an incoming connection if it's being made over
SSL.  As long as you don't have anything else in your pg_hba.conf, then
only SSL connections will be allowed.  However, first entry in the
pg_hba.conf matches, so if you have earlier entries, those might be
getting used instead.

>    - If I leave my key file encrypted then every time my databases have to be started have to enter the password.  So you can either leave the passwords unencrypted and set the permissions on the file to 0600 accessible only by postgres or you can enter the key password each time the database is started up.  As someone in the security field I have a tough time leaving the key unencrypted but as some setting up a production system that is located on a network that you can't get to without directly accessing the server I feel that is enough security that I can leave them unencrypted.  Thoughts?

You could use a vaulting system to pull the key and make it available at
startup and then remove it after, perhaps, but I would suggest that it's
pretty common to have SSL keys unencrypted on systems which are doing
SSL and otherwise secured.

>    - Am I missing anything?  There are no videos out there that show how to stand up a 2 way SSL communication channel between the primary and secondary, or does anyone have one that they can share?

Unfortunately, there's definitely areas here where we could, and really
should, improve when it comes to logging exactly what validation has
been done on incoming connections, to provide the kind of reassurance
you're looking for.  Things like the difference between verify-ca and
verify-full aren't really very well explained, particularly since it's
very uncommon, in my experience, for people who have used SSL/TLS in
other places to have any concept of "verify-ca" since it's basically
"don't actually verify that the other side is who they claim to be"..

Stephen