Unfortunately I am not allowed to use wireshark in my environment. Good idea though Susan Joseph sandajos...@verizon.net -----Original Message----- From: Wim Bertels <wim.bert...@ucll.be> To: Susan Joseph <sandajos...@verizon.net>; peter.eisentr...@2ndquadrant.com <peter.eisentr...@2ndquadrant.com>; pgsql-gene...@postgresql.org <pgsql-gene...@postgresql.org> Sent: Thu, Sep 3, 2020 7:44 am Subject: Re: SSL between Primary and Seconday PostgreSQL DBs
First thougt: A general solution that you could use is wireshark. For example : Search for wireshark ssl traffic Susan Joseph <sandajos...@verizon.net> schreef op September 3, 2020 10:54:36 AM UTC: OK, I understand I was just hoping someone could confirm that my settings are correct. I didn't come across an error so everything seems to be working I just can't verify that SSL is working. Are there any commands you can run to verify that SSL is up and operational? Testing from a client to the database doesn't prove that database to database is working. Susan Joseph sandajos...@verizon.net -----Original Message----- From: Peter Eisentraut <peter.eisentr...@2ndquadrant.com> To: Susan Joseph <sandajos...@verizon.net>; pgsql-gene...@postgresql.org <pgsql-gene...@postgresql.org> Sent: Thu, Sep 3, 2020 1:01 am Subject: Re: SSL between Primary and Seconday PostgreSQL DBs 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 <sandajos...@verizon.net> > To: pgsql-gene...@postgresql.org <pgsql-gene...@postgresql.org> > 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 > > > -- Verstuurd vanaf mijn Android apparaat met K-9 Mail. Excuseer mijn beknoptheid.
