Re: SSL between Primary and Seconday PostgreSQL DBs

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 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

>
>
>