Thread: 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
- The FQDN and IP address are set in the SAN field.
- FQDN is also the CN in the DN
- Key Usage is set to Digital Signature and Key encipherment
- EKU is set to Server Authentication and Client Authentication
- Rep_user certificate - Secondary Database
- CN is set to the rep_user account name
- Key Usage is set to digital signature and key encipherment
- 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:
- ssl=on
- ssl_ca_file='root.crt'
- ssl_cert_file='server.crt'
- ssl_key_file='server.key'
- ssl_ciphers='HIGH:MEDIUM:+3DES:!aNULL'
- On the Primary in pg_hba.conf I add a replication line:
- hostssl replication rep_user cert
- On the Secondary I set the following information in the postgresql.conf to: (DO I NEED TO DO THIS??)
- ssl=on
- ssl_ca_file='root.crt'
- ssl_cert_file='client.crt'
- ssl_cert_fkey='client.key'
- ssl_ciphers='HIGH:MEDIUM:+3DES:!aNULL'
- On the Secondary I edit the recovery.conf file to the following:
- 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:
- 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
So has no one done this before?
-----Original Message-----
From: Susan Joseph <sandajoseph@verizon.net>
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Sent: Mon, Aug 24, 2020 10:10 am
Subject: SSL between Primary and Seconday PostgreSQL DBs
From: Susan Joseph <sandajoseph@verizon.net>
To: pgsql-general@postgresql.org <pgsql-general@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
- The FQDN and IP address are set in the SAN field.
- FQDN is also the CN in the DN
- Key Usage is set to Digital Signature and Key encipherment
- EKU is set to Server Authentication and Client Authentication
- Rep_user certificate - Secondary Database
- CN is set to the rep_user account name
- Key Usage is set to digital signature and key encipherment
- 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:
- ssl=on
- ssl_ca_file='root.crt'
- ssl_cert_file='server.crt'
- ssl_key_file='server.key'
- ssl_ciphers='HIGH:MEDIUM:+3DES:!aNULL'
- On the Primary in pg_hba.conf I add a replication line:
- hostssl replication rep_user cert
- On the Secondary I set the following information in the postgresql.conf to: (DO I NEED TO DO THIS??)
- ssl=on
- ssl_ca_file='root.crt'
- ssl_cert_file='client.crt'
- ssl_cert_fkey='client.key'
- ssl_ciphers='HIGH:MEDIUM:+3DES:!aNULL'
- On the Secondary I edit the recovery.conf file to the following:
- 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:
- 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
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@verizon.net> > To: pgsql-general@postgresql.org <pgsql-general@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 http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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@verizon.net
sandajoseph@verizon.net
-----Original Message-----
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Susan Joseph <sandajoseph@verizon.net>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Sent: Thu, Sep 3, 2020 1:01 am
Subject: Re: SSL between Primary and Seconday PostgreSQL DBs
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Susan Joseph <sandajoseph@verizon.net>; pgsql-general@postgresql.org <pgsql-general@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 <sandajoseph@verizon.net>
> To: pgsql-general@postgresql.org <pgsql-general@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 http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
> 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@verizon.net>
> To: pgsql-general@postgresql.org <pgsql-general@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 http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Thu, Sep 3, 2020 at 5:54 AM Susan Joseph <sandajoseph@verizon.net> wrote: > > 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. > > I'm most definitely NOT any kind of either Postgresql nor computer security expert. Did a search using 'linus testing SSL' and found some possible options that you may wish to consider. These did not seem to be specific to database access testing but more to website testing. I believe that, website testing, would give you the information you are looking for. Regards
First thougt: A general solution that you could use is wireshark.
For example : Search for wireshark ssl traffic
--
Verstuurd vanaf mijn Android apparaat met K-9 Mail. Excuseer mijn beknoptheid.
For example : Search for wireshark ssl traffic
Susan Joseph <sandajoseph@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
sandajoseph@verizon.net-----Original Message-----
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Susan Joseph <sandajoseph@verizon.net>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Sent: Thu, Sep 3, 2020 1:01 am
Subject: Re: SSL between Primary and Seconday PostgreSQL DBsOn 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@verizon.net>
> To: pgsql-general@postgresql.org <pgsql-general@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.
Unfortunately I am not allowed to use wireshark in my environment. Good idea though
Susan Joseph
sandajoseph@verizon.net
sandajoseph@verizon.net
-----Original Message-----
From: Wim Bertels <wim.bertels@ucll.be>
To: Susan Joseph <sandajoseph@verizon.net>; peter.eisentraut@2ndquadrant.com <peter.eisentraut@2ndquadrant.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Sent: Thu, Sep 3, 2020 7:44 am
Subject: Re: SSL between Primary and Seconday PostgreSQL DBs
From: Wim Bertels <wim.bertels@ucll.be>
To: Susan Joseph <sandajoseph@verizon.net>; peter.eisentraut@2ndquadrant.com <peter.eisentraut@2ndquadrant.com>; pgsql-general@postgresql.org <pgsql-general@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
--
Verstuurd vanaf mijn Android apparaat met K-9 Mail. Excuseer mijn beknoptheid.
For example : Search for wireshark ssl traffic
Susan Joseph <sandajoseph@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
sandajoseph@verizon.net-----Original Message-----
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Susan Joseph <sandajoseph@verizon.net>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Sent: Thu, Sep 3, 2020 1:01 am
Subject: Re: SSL between Primary and Seconday PostgreSQL DBsOn 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@verizon.net>
> To: pgsql-general@postgresql.org <pgsql-general@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.
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.crtsslkey=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 workedbut 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 therepgsql 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 primaryand 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 youcan either leave the passwords unencrypted and set the permissions on the file to 0600 accessible only by postgres oryou can enter the key password each time the database is started up. As someone in the security field I have a tough timeleaving the key unencrypted but as some setting up a production system that is located on a network that you can't getto 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 channelbetween 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".. Thanks, Stephen
Attachment
This is great, thanks. I have not heard of verify-full but I will try that and let you know if I have issues.
So I will remove the passfile info for recovery.conf. It was there because I first set it up using passwords to make sure it was working and then moved to SSL so if not needed I can remove it.
So if I don't put clientcert=1 do I just leave it at cert or should it be set to something else?
I will try and drop the other connection lines in the pg_hba.conf but I thought the last time I did that it through an error. Let me try it again and see if it works.
So are you saying that "sslmode=verify-ca" doesn't actually verify that the certificate is from a CA it trusts?
So my big question is: am I really adding more security by turning on SSL or is it just more work for the DBA?
Susan Joseph
sandajoseph@verizon.net
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"..
Thanks,
Stephen
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"..
Thanks,
Stephen
Greetings, We really prefer it if you don't top-post on these lists but instead reply in-line, as I did. * Susan Joseph (sandajoseph@verizon.net) wrote: > This is great, thanks. I have not heard of verify-full but I will try that and let you know if I have issues. > So I will remove the passfile info for recovery.conf. It was there because I first set it up using passwords to make sureit was working and then moved to SSL so if not needed I can remove it. > So if I don't put clientcert=1 do I just leave it at cert or should it be set to something else? Leaving it as 'cert' should be sufficient. The clientcert option is really intended to be used with the *other* auth methods, to provide a "check the password *and* the client certificate" option. > I will try and drop the other connection lines in the pg_hba.conf but I thought the last time I did that it through anerror. Let me try it again and see if it works. If that results in an error then it's possible you have an error in your configuration and you aren't actually using SSL for this connection. > So are you saying that "sslmode=verify-ca" doesn't actually verify that the certificate is from a CA it trusts? verify-ca checks that the certificate is from a trusted CA, but it does *not* make sure that the common name in that certificate matches the name of the system that is being connected to. In other words, if you configure your secondary to connect to 'server-a.mydomain.com', but the certificate installed on the server has a common name (CN) of 'server-x.mydomain.com', the connection will still be allowed, as long as the certificate is signed by a trusted CA. That's not good. > So my big question is: am I really adding more security by turning on SSL or is it just more work for the DBA? I'm not sure if this is a serious question or not. If you want to mitigate attack vectors which arise from data-in-transit then you absolutely need to be using strong authentication and encryption when connecting. If your profile is such that you're not concerned about network-based attack vectors, then perhaps it's not needed. Thanks, Stephen
Attachment
So I made the changes on the secondary to change the sslmode to verify-full
I removed the clientcert=1 in pg_hba.conf and removed any connections other than ssl
I removed the passfile info from recovery.conf
and now I am getting this error:
2020-09-03 13:01:49.990 UTC [7963] FATAL: could not connect to the primary server: server certificate for "lc-subca-pg.theforest.sap" does not match host name "192.168.1.142"
My certificate for my primary is:
Version: 3
Serial: 0x1B
Issuer: CN=LifeCycle SubCA, OU=CA, OU=DoDSAP, OU=DoD, O=U.S. Government, C=US
Subject: CN=lc-subca-pg.theforest.sap, OU=NPE, OU=DoDSAP, OU=DoD, O=U.S. Government, C=US
NotBefore: 20 AUG 2020 16:46:48 GMT
NotAfter: 05 AUG 2028 18:51:19 GMT
KeyType: RSA2048
Signature Algorithm: sha384RSA
Extensions:
Enhanced Key Usage
Server Authentication
Client Authentication
Key Usage
(Critical)
Digital signature
Key encipherment
(A0)
Subject Alternative Name
DNS name: lc-subca-pg.theforest.sap
IP Address: 192.168.1.142
Subject Key Identifier
FF4C0DCD62B17F99935DB3977D49711892958E20
Authority Key Identifier
Key ID: 6495303FEB8925B9C83B7A63FF7F026C974E308E
SHA-1 Fingerprint: 51B1A879EB5ADAFA23042A8E84165CF89438F29B
Serial: 0x1B
Issuer: CN=LifeCycle SubCA, OU=CA, OU=DoDSAP, OU=DoD, O=U.S. Government, C=US
Subject: CN=lc-subca-pg.theforest.sap, OU=NPE, OU=DoDSAP, OU=DoD, O=U.S. Government, C=US
NotBefore: 20 AUG 2020 16:46:48 GMT
NotAfter: 05 AUG 2028 18:51:19 GMT
KeyType: RSA2048
Signature Algorithm: sha384RSA
Extensions:
Enhanced Key Usage
Server Authentication
Client Authentication
Key Usage
(Critical)
Digital signature
Key encipherment
(A0)
Subject Alternative Name
DNS name: lc-subca-pg.theforest.sap
IP Address: 192.168.1.142
Subject Key Identifier
FF4C0DCD62B17F99935DB3977D49711892958E20
Authority Key Identifier
Key ID: 6495303FEB8925B9C83B7A63FF7F026C974E308E
SHA-1 Fingerprint: 51B1A879EB5ADAFA23042A8E84165CF89438F29B
Susan Joseph
sandajoseph@verizon.net
sandajoseph@verizon.net
-----Original Message-----
From: Susan Joseph <sandajoseph@verizon.net>
To: sfrost@snowman.net <sfrost@snowman.net>
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Sent: Thu, Sep 3, 2020 8:55 am
Subject: Re: SSL between Primary and Seconday PostgreSQL DBs
From: Susan Joseph <sandajoseph@verizon.net>
To: sfrost@snowman.net <sfrost@snowman.net>
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Sent: Thu, Sep 3, 2020 8:55 am
Subject: 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.
So I will remove the passfile info for recovery.conf. It was there because I first set it up using passwords to make sure it was working and then moved to SSL so if not needed I can remove it.
So if I don't put clientcert=1 do I just leave it at cert or should it be set to something else?
I will try and drop the other connection lines in the pg_hba.conf but I thought the last time I did that it through an error. Let me try it again and see if it works.
So are you saying that "sslmode=verify-ca" doesn't actually verify that the certificate is from a CA it trusts?
So my big question is: am I really adding more security by turning on SSL or is it just more work for the DBA?
Susan Joseph
sandajoseph@verizon.net
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"..
Thanks,
Stephen
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"..
Thanks,
Stephen
Greetings, * Susan Joseph (sandajoseph@verizon.net) wrote: > So I made the changes on the secondary to change the sslmode to verify-fullI removed the clientcert=1 in pg_hba.conf andremoved any connections other than sslI removed the passfile info from recovery.confand now I am getting this error: > 2020-09-03 13:01:49.990 UTC [7963] FATAL: could not connect to the primary server: server certificate for "lc-subca-pg.theforest.sap"does not match host name "192.168.1.142" Yes, as I explained, because of exactly the issue that the host you've told your secondary to connect to (looks like 192.168.1.142) doesn't match the certificate presented by the primary (which looks to be "lc-subca-pg.theforest.sap"). The answer is to make those two match. Thanks, Stephen
Attachment
Susan Joseph
sandajoseph@verizon.net
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 9:12 am
Subject: Re: SSL between Primary and Seconday PostgreSQL DBs
Greetings,
* Susan Joseph (sandajoseph@verizon.net) wrote:
> So I made the changes on the secondary to change the sslmode to verify-fullI removed the clientcert=1 in pg_hba.conf and removed any connections other than sslI removed the passfile info from recovery.confand now I am getting this error:
> 2020-09-03 13:01:49.990 UTC [7963] FATAL: could not connect to the primary server: server certificate for "lc-subca-pg.theforest.sap" does not match host name "192.168.1.142"
>>Yes, as I explained, because of exactly the issue that the host you've
>>told your secondary to connect to (looks like 192.168.1.142) doesn't
>>match the certificate presented by the primary (which looks to be
>>"lc-subca-pg.theforest.sap").
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 9:12 am
Subject: Re: SSL between Primary and Seconday PostgreSQL DBs
Greetings,
* Susan Joseph (sandajoseph@verizon.net) wrote:
> So I made the changes on the secondary to change the sslmode to verify-fullI removed the clientcert=1 in pg_hba.conf and removed any connections other than sslI removed the passfile info from recovery.confand now I am getting this error:
> 2020-09-03 13:01:49.990 UTC [7963] FATAL: could not connect to the primary server: server certificate for "lc-subca-pg.theforest.sap" does not match host name "192.168.1.142"
>>Yes, as I explained, because of exactly the issue that the host you've
>>told your secondary to connect to (looks like 192.168.1.142) doesn't
>>match the certificate presented by the primary (which looks to be
>>"lc-subca-pg.theforest.sap").
OK so I fixed that in my recovery.conf file so it is not set to the IP but to the FQDN and it is no longer throwing this error.
>>The answer is to make those two match.
Thanks,
Stephen
Thanks,
Stephen
On 2020-09-03 14:01, Susan Joseph wrote: > Unfortunately I am not allowed to use wireshark in my environment. Good > idea though The system view pg_stat_ssl, in combination with pg_stat_activity, will show you whether a connection is using SSL. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services