PostgreSQL 11 with SSL on Linux
Can someone tell me if there is a yum version of PostgreSQL 11 that can be installed on Linux that has SSL enabled? Currently the only way I have gotten SSL turned on in PostgreSQL is by doing the following commands: - tar xzvf /tmp/postgresql-11.2.tar.gz -C /data - mv /data/postgresql-11.2 /data/pgsql - cd /data/pgsql - ./configure --prefix=/data/pgsql --without-readline --without-zlib --with-openssl >> conf.log - make - make install Thanks
Re: PostgreSQL 11 with SSL on Linux
So the other issue I have is that I am running this on a server that is not connected to the Internet. So I have downloaded the RPM file but I can't figure out how to install it without it trying to access files on the Internet. Are there other libraries I need to include in my download for this to work on a non-networked server? -Original Message- From: Magnus Hagander To: Susan Joseph Cc: pgsql-general@lists.postgresql.org Sent: Thu, Jun 4, 2020 11:14 am Subject: Re: PostgreSQL 11 with SSL on Linux On Thu, Jun 4, 2020 at 5:01 PM Susan Joseph wrote: Can someone tell me if there is a yum version of PostgreSQL 11 that can be installed on Linux that has SSL enabled? Currently the only way I have gotten SSL turned on in PostgreSQL is by doing the following commands: - tar xzvf /tmp/postgresql-11.2.tar.gz -C /data - mv /data/postgresql-11.2 /data/pgsql - cd /data/pgsql - ./configure --prefix=/data/pgsql --without-readline --without-zlib --with-openssl >> conf.log - make - make install All the PostgreSQL versions available om yum from the postgresql.org site have SSL enabled. Just install using the instructions from https://www.postgresql.org/download/. -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/
Re: PostgreSQL 11 with SSL on Linux
So when I run rpm -i it says that the package is installed, but I can't find a postgresql directory with all the files and executables. So what am I missing? -Original Message- From: Tom Lane To: Susan Joseph Cc: mag...@hagander.net ; pgsql-general@lists.postgresql.org Sent: Thu, Jun 4, 2020 1:11 pm Subject: Re: PostgreSQL 11 with SSL on Linux Susan Joseph writes: > So the other issue I have is that I am running this on a server that is not > connected to the Internet. So I have downloaded the RPM file but I can't > figure out how to install it without it trying to access files on the > Internet. Are there other libraries I need to include in my download for > this to work on a non-networked server? Are you using the right install tool? "rpm -i" shouldn't result in any nonlocal accesses. Tools like yum or dnf will, because they're meant to fetch from nonlocal repositories. regards, tom lane
Re: PostgreSQL 11 with SSL on Linux
OK, when I went to PostgreSQL to get the rpm file to install I was given: yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm I put the file pgdg-redhat-repo-latest.noarch.rpm in my /tmp directory and ran rpm -i on the file. If I run rpm -qa | grep postgres I get postgresql-libs-9.2.24-4.el7_8.x86_64 When I run: rpm -ql postgresql-libs-9.2.24-4.el7_8.x86_64 I just get a list of libraries. I am not an SA, I am more of an engineer and have been working with the PostgreSQL I installed by building it so not really used to RPM. I have used YUM but that was with a connected server. How do I find all the packages that I need to have to do an install locally? -Original Message- From: Alan Hodgson To: pgsql-general@lists.postgresql.org Sent: Thu, Jun 4, 2020 1:36 pm Subject: Re: PostgreSQL 11 with SSL on Linux On Thu, 2020-06-04 at 17:32 +, Susan Joseph wrote: So when I run rpm -i it says that the package is installed, but I can't find a postgresql directory with all the files and executables. So what am I missing? The server stuff is in postgresql11-serverif you're using the community rpms.
Re: PostgreSQL 11 with SSL on Linux
OK thanks, I figured I had something messed up in my thought process. I will work through these steps and let you know how it goes. Susan -Original Message- From: Mohammed Bhatti To: Susan Joseph Cc: ahodg...@lists.simkin.ca ; pgsql-general@lists.postgresql.org Sent: Thu, Jun 4, 2020 2:14 pm Subject: Re: PostgreSQL 11 with SSL on Linux On Thu, Jun 4, 2020 at 1:50 PM Susan Joseph wrote: OK, when I went to PostgreSQL to get the rpm file to install I was given: yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm I put the file pgdg-redhat-repo-latest.noarch.rpm in my /tmp directory and ran rpm -i on the file. If I run rpm -qa | grep postgres I get postgresql-libs-9.2.24-4.el7_8.x86_64 When I run: rpm -ql postgresql-libs-9.2.24-4.el7_8.x86_64 I just get a list of libraries. I am not an SA, I am more of an engineer and have been working with the PostgreSQL I installed by building it so not really used to RPM. I have used YUM but that was with a connected server. How do I find all the packages that I need to have to do an install locally? -Original Message- From: Alan Hodgson To: pgsql-general@lists.postgresql.org Sent: Thu, Jun 4, 2020 1:36 pm Subject: Re: PostgreSQL 11 with SSL on Linux On Thu, 2020-06-04 at 17:32 +, Susan Joseph wrote: So when I run rpm -i it says that the package is installed, but I can't find a postgresql directory with all the files and executables. So what am I missing? The server stuff is in postgresql11-serverif you're using the community rpms. Hi, I've had to do this in the past and I think you probably want to do this:1. connect to a machine that has access to repos2. go here: https://www.postgresql.org/download/linux/redhat/3. where it says "Direct RPM Download", click on the "direct download" link4. this takes you here: https://yum.postgresql.org/rpmchart.php5. click on "11 RHEL/CentOS/Oracle Linux 7 - x86_64" or whatever is the appropriate version you are looking for6. this takes you here: https://yum.postgresql.org/11/redhat/rhel-7-x86_64/repoview/7. under "Available Groups", click on the "PostgreSQL Database Server 11 PGDG" link8. which takes you here: https://yum.postgresql.org/11/redhat/rhel-7-x86_64/repoview/postgresqldbserver11.group.html9. here, you'll see four packages listed10. click on each, which takes you to a page where the latest package is available11. repeat for all four and download all of the four and copy then to your offline server, so for example, /tmp12. then try and do an rpm -ivh /tmp/, so for example rpm -ivh postgresql11-server-11.8-1PGDG.rhel7.x86_64 13. I believe this should place the postgres stuff in /var/lib/pgsql14. additional packages can be downloaded as needed --mohammed
Minor Upgrade Question
So when I first started working with PostgreSQL I was using the latest version (11.2). I don't want to move to 12 yet but I would like to get my 11.2 up to 11.8. Due to my servers not being connected to the Internet I ended up downloading the libraries and building the files locally. My question is how do I upgrade to 11.8? I know how to go and get the rpms now and download those to a disconnected server and then install PostgreSQL that way. I was able to install 11.8 on another server using the rpms. But my directories are different. The rpm install placed the files into the directory /data/pgsql-11. My 11.2 database is in /data/pgsql. I checked the rpm file and it says that the files are not relocatable. So I can do a new install of 11.8 via the rpms and it will place the files into /data/pgsql-11, can just need to copy the executable files in the /data/pgsql-11/bin directory into my /data/pgsql/bin or are there other files that need to be copied over? Is there a better way to do this rather than reinstalling postgreSQL again on a server that already has it? Thanks, Susan
Re: Minor Upgrade Question
OK I will try that, thanks On 6/17/2020 11:19 AM, Joshua Drake wrote: Susan You can use -Uvh to upgrade the rpms on the existing machine. You can then use symlinks to link the expected pgsql data directories. Make sure you take a backup, and stop the service before you proceed. JD On Tue, Jun 16, 2020 at 7:12 AM Susan Joseph <mailto:sandajos...@verizon.net>> wrote: So when I first started working with PostgreSQL I was using the latest version (11.2). I don't want to move to 12 yet but I would like to get my 11.2 up to 11.8. Due to my servers not being connected to the Internet I ended up downloading the libraries and building the files locally. My question is how do I upgrade to 11.8? I know how to go and get the rpms now and download those to a disconnected server and then install PostgreSQL that way. I was able to install 11.8 on another server using the rpms. But my directories are different. The rpm install placed the files into the directory /data/pgsql-11. My 11.2 database is in /data/pgsql. I checked the rpm file and it says that the files are not relocatable. So I can do a new install of 11.8 via the rpms and it will place the files into /data/pgsql-11, can just need to copy the executable files in the /data/pgsql-11/bin directory into my /data/pgsql/bin or are there other files that need to be copied over? Is there a better way to do this rather than reinstalling postgreSQL again on a server that already has it? Thanks, Susan
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= 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 /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
Re: SSL between Primary and Seconday PostgreSQL DBs
So has no one done this before? -Original Message- From: Susan Joseph To: 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 - 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= 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 /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
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 sandajos...@verizon.net -Original Message- From: Peter Eisentraut To: Susan Joseph ; 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 > To: 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= 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 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 a
Re: SSL between Primary and Seconday PostgreSQL DBs
Unfortunately I am not allowed to use wireshark in my environment. Good idea though Susan Joseph sandajos...@verizon.net -Original Message- From: Wim Bertels To: Susan Joseph ; peter.eisentr...@2ndquadrant.com ; 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 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 To: Susan Joseph ; 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 > To: 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= 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 IP>/32 cert clientcert=1 > * On the Secondary I move the root.crt to /data/pgsql/data/.postgresql > * Then I restart the databases >
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 sandajos...@verizon.net -Original Message- From: Stephen Frost To: Susan Joseph Cc: pgsql-gene...@postgresql.org Sent: Thu, Sep 3, 2020 8:28 am Subject: Re: SSL between Primary and Seconday PostgreSQL DBs Greetings, * Susan Joseph (sandajos...@verizon.net) wrote: > - primary_conninfo = 'user=rep_user passfile=''/data/.pgpass'' >host= 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 /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
Re: SSL between Primary and Seconday PostgreSQL DBs
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" 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 Susan Joseph sandajos...@verizon.net -Original Message- From: Susan Joseph To: sfr...@snowman.net Cc: pgsql-gene...@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 sandajos...@verizon.net -Original Message----- From: Stephen Frost To: Susan Joseph Cc: pgsql-gene...@postgresql.org Sent: Thu, Sep 3, 2020 8:28 am Subject: Re: SSL between Primary and Seconday PostgreSQL DBs Greetings, * Susan Joseph (sandajos...@verizon.net) wrote: > - primary_conninfo = 'user=rep_user passfile=''/data/.pgpass'' >host= 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 /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
Re: SSL between Primary and Seconday PostgreSQL DBs
Susan Joseph sandajos...@verizon.net -Original Message- From: Stephen Frost To: Susan Joseph Cc: pgsql-gene...@postgresql.org Sent: Thu, Sep 3, 2020 9:12 am Subject: Re: SSL between Primary and Seconday PostgreSQL DBs Greetings, * Susan Joseph (sandajos...@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
Re: 答复: Security issues concerning pgsql replication
I know when I set it up with a password protecting the private key it would
prompt me for the password when I started up the service.
Susan Joseph
sandajos...@verizon.net
-Original Message-
From: xiebin (F)
To: Magnus Hagander
Cc: pgsql-gene...@postgresql.org ; zhubo (C)
; Zhuzheng (IT) ; houxiaowei
; yangshaobo (A) ; mapinghu
; Songyunpeng ; luoqi (F)
Sent: Tue, Oct 27, 2020 7:34 am
Subject: 答复: Security issues concerning pgsql replication
#yiv6531606513 #yiv6531606513 -- _filtered {} _filtered {} _filtered {}
_filtered {} _filtered {} _filtered {} _filtered {}#yiv6531606513
#yiv6531606513 p.yiv6531606513MsoNormal, #yiv6531606513
li.yiv6531606513MsoNormal, #yiv6531606513 div.yiv6531606513MsoNormal
{margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;font-family:宋体;}#yiv6531606513
a:link, #yiv6531606513 span.yiv6531606513MsoHyperlink
{color:blue;text-decoration:underline;}#yiv6531606513 a:visited, #yiv6531606513
span.yiv6531606513MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}#yiv6531606513
p.yiv6531606513gmail-m4735437323743741455msolistparagraph, #yiv6531606513
li.yiv6531606513gmail-m4735437323743741455msolistparagraph, #yiv6531606513
div.yiv6531606513gmail-m4735437323743741455msolistparagraph
{margin-right:0cm;margin-left:0cm;font-size:12.0pt;font-family:宋体;}#yiv6531606513
span.yiv6531606513EmailStyle18
{font-family:sans-serif;color:#1F497D;}#yiv6531606513
.yiv6531606513MsoChpDefault {font-family:sans-serif;} _filtered
{}#yiv6531606513 div.yiv6531606513WordSection1 {}#yiv6531606513 Perhaps you
misunderstand me. It is not user-database, but master-slave interaction that I
am concerning. The master-slave replication proceeds continually and requires
no manual interference. Both master and slave’s private key are involved, but
ssl_passphrase_command is only used to parse passphrase of master’s private
key. Pgsql cannot get slave’s private key automatically, so replication failed.
I’ve tried and proved it did not work. I refered to the list of pgsql’s
authenticate methods but did not find an appropriate one for replication.
https://www.postgresql.org/docs/12/client-authentication.html Xie Bin
发件人: Magnus Hagander [mailto:mag...@hagander.net]
发送时间: 2020年10月27日 17:00
收件人: xiebin (F)
抄送: pgsql-gene...@postgresql.org; zhubo (C) ; Zhuzheng (IT)
; houxiaowei ; yangshaobo (A)
; mapinghu ; Songyunpeng
; luoqi (F)
主题: Re: Security issues concerning pgsql replication On Tue, Oct 27,
2020 at 9:52 AM xiebin (F) wrote:
Hi, I was setting up a master/slave pgsql(version 12.4) cluster using stream
replication. I found 3 ways to authenticate, but all of them has some security
issue. 1. Disable authentication. cat pg_hba.conf host all
all 0/0 md5 host replication xie 192.168.1.31/32
trust In this case, untrusted users on slave may use pg_basebackup to stole
data. 2. Using password. cat pg_hba.conf host all all 0/0
md5 host replication xie 192.168.1.31/32 md5 cat
/var/lib/pgsql/.pgpass (on slave) 192.168.1.30:5432:xie:mydb:xie In this
case, the password is stored unencrypted. File access control may help, but
it’s not secure enough.
Why not? The user who can read that file, can also read the entire
database on the standby node already.
3. Using certificate. cat pg_hba.conf host all all 0/0
md5 hostssl replication xie 192.168.1.31/32 cert
clientcert=1 cat postgresql.conf | grep ssl ssl = on ssl_ca_file =
'root.crt' ssl_cert_file = 'server.crt' ssl_crl_file = '' ssl_key_file =
'server.key' cat recovery.conf primary_conninfo = 'host=192.168.1.30
port=5432 user=xie application_name=stream_relication sslrootcert=/tmp/root.crt
sslcert=/tmp/xie.crt sslkey=/tmp/xie.key' restore_command = ''
recovery_target_timeline = 'latest' primary_slot_name = 'rep_slot' The
certificates are created by official
instructionshttps://www.postgresql.org/docs/12/ssl-tcp.html#SSL-CERTIFICATE-CREATION.
But the private key is not encrypted. I noticed in psql 11+ version, a new
configurationssl_passphrase_command is added, so that encrypted private key can
be used. But as far as I know, encrypted private key is not supported in stream
replication. I wonder if there is another way to authenticate in replication?
Or does pgsql has any plan to support encrypted private key in replication?
PostgreSQL replication supports all authentication methods that PostgeSQL
supports for regular connections, in general. While I haven't tried it,
ssl_passphrase_command should work for this as well as long as it doesn't
require manual user interaction. But it could for example read the passphrase
from a pipe where it's provided off,or from a hardware device. Do keep in mind
that r
Upgrading from 11 to 13
I am currently using PostgreSQL 11.2 and would like to try and upgrade it to the latest version 13. Can I go straight from 11 to 13 or do I need to upgrade to 12 first and then to 13? Thanks, Susan
