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 >> >> >> > > >-- >Peter Eisentraut >https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.2ndquadrant.com%2F&data=02%7C01%7C%7C7239d150ef754d879fe508d84ff7cb38%7Ce638861b15d94de6a65db48789ae1f08%7C0%7C0%7C637347272998501874&sdata=zF%2BlGeB6VwbEZcekbv8xyaNEoopwR1UkLHA01XU4BTo%3D&reserved=0 >PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Verstuurd vanaf mijn Android apparaat met K-9 Mail. Excuseer mijn beknoptheid.
