On 2020-08-27 12:57, Susan Joseph wrote:
So has no one done this before?
I'm sure people have done this. But I suggest that if you ask a
question on this mailing list, you ask something more concrete, like, I
tried to do this, and got stuck here, and tried this and got this error.
People can help with that sort of thing. What we have here is a
complex security setup and you are asking people to do an open-ended
review. No one wants to do that.
-----Original Message-----
From: Susan Joseph <sandajoseph@xxxxxxxxxxx>
To: pgsql-general@xxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxx>
Sent: Mon, Aug 24, 2020 10:10 am
Subject: SSL between Primary and Seconday PostgreSQL DBs
I have setup a Primary and Secondary PostgreSQL DBs. They were setup up
with basic replication then I went back and modified them to use SSL. I
am just not sure if I did it correctly. Everything is working but I
want to make sure I have the settings correctly. I am using PostgreSQL
11.2.
* I have a PKI that I stood up so I issued 2 server certificates one
for each database from my CA.
* Primary server certificate - Primary Database
o The FQDN and IP address are set in the SAN field.
o FQDN is also the CN in the DN
o Key Usage is set to Digital Signature and Key encipherment
o EKU is set to Server Authentication and Client Authentication
* Rep_user certificate - Secondary Database
o CN is set to the rep_user account name
o Key Usage is set to digital signature and key encipherment
o EKU is set to client authentication
* Each certificate file contains the certificate and the subCA
certificate who issued the certificate and put in a file called
server.crt for the Primary and client.crt for the secondary.
* The key for each certificate is stored in a separate file
unencrypted (I have questions about this later on) in a file called
server.key and client.key
* The server.crt, server.key, and root.crt are put onto the primary
database server in the /data/pgsql/data location, the owner and
group of these files is set to postgres
* The client.crt, client.key, and root.crt are put onto the primary
database server in the /data/pgsql/data location, the owner and
group of these files is set to postgres
* On the Primary in postgresql.conf I set:
o ssl=on
o ssl_ca_file='root.crt'
o ssl_cert_file='server.crt'
o ssl_key_file='server.key'
o ssl_ciphers='HIGH:MEDIUM:+3DES:!aNULL'
* On the Primary in pg_hba.conf I add a replication line:
o hostssl replication
rep_user cert
* On the Secondary I set the following information in the
postgresql.conf to: (DO I NEED TO DO THIS??)
o ssl=on
o ssl_ca_file='root.crt'
o ssl_cert_file='client.crt'
o ssl_cert_fkey='client.key'
o ssl_ciphers='HIGH:MEDIUM:+3DES:!aNULL'
* On the Secondary I edit the recovery.conf file to the following:
o 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'
* On the Secondary I edit the pg_hba.conf file and change the rep_user
line to:
o hostssl replication rep_user <primary
IP>/32 cert clientcert=1
* On the Secondary I move the root.crt to /data/pgsql/data/.postgresql
* Then I restart the databases
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?
* 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?
* 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?
* 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?
Thanks,
Susan
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
