From: | Wim Bertels <wim(dot)bertels(at)ucll(dot)be> |
---|---|
To: | Susan Joseph <sandajoseph(at)verizon(dot)net>,"peter(dot)eisentraut(at)2ndquadrant(dot)com" <peter(dot)eisentraut(at)2ndquadrant(dot)com>,"pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: SSL between Primary and Seconday PostgreSQL DBs |
Date: | 2020-09-03 11:44:06 |
Message-ID: | 9A729A27-75BE-4EB3-A484-2736C162692C@ucll.be |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
First thougt: A general solution that you could use is wireshark.
For example : Search for wireshark ssl traffic
Susan Joseph <sandajoseph(at)verizon(dot)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
>sandajoseph(at)verizon(dot)net
>
>
>-----Original Message-----
>From: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>
>To: Susan Joseph <sandajoseph(at)verizon(dot)net>;
>pgsql-general(at)postgresql(dot)org <pgsql-general(at)postgresql(dot)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 <sandajoseph(at)verizon(dot)net>
>> To: pgsql-general(at)postgresql(dot)org <pgsql-general(at)postgresql(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Susan Joseph | 2020-09-03 12:01:34 | Re: SSL between Primary and Seconday PostgreSQL DBs |
Previous Message | o1bigtenor | 2020-09-03 11:41:22 | Re: SSL between Primary and Seconday PostgreSQL DBs |