So, this is an issue that was caused on RHEL 7.4 with EnterpriseDB 9.6.6.11 and pgPool 3.6. I followed this link to configure wrapper scripts for /sbin/ip and /sbin/arping.
This was added to the /etc/sudoers file to allow the enterprisedb user to execute the wrapper scripts:
enterprisedb compute01=(root) NOPASSWD:EXEC:SETENV: /sbin/ip, /sbin/arping
Just as a check, I was able to sudo to the enterprisedb user and execute the following command and successfully bring up the delegate IP (or VIP):
ip_w addr add 10.10.10.85/24 dev ens192 label ens192:0
Now, as root I started the pgPool service:
systemctl start edb-pgpool-3.6
Checking /var/log/messages, the following was shown:
15:49:12: pid 860: LOG: creating socket for sending heartbeat
15:49:12: pid 860: DETAIL: setsockopt(SO_BINDTODEVICE) requires root privilege
15:49:12: pid 860: LOG: set SO_REUSEPORT option to the socket
15:49:12: pid 860: LOG: creating socket for sending heartbeat
15:49:12: pid 860: DETAIL: set SO_REUSEPORT
15:49:12: pid 858: LOG: failed to create watchdog heartbeat receive socket.
15:49:12: pid 858: DETAIL: setsockopt(SO_BINDTODEVICE) requies root privilege
15:49:12: pid 858: LOG: set SO_REUSEPORT option to the socket
15:49:12: pid 858: LOG: creating watchdog heartbeat receive socket.
15:49:12: pid 858: DETAIL: set SO_REUSEPORT
15:49:14: pid 854: WARNING: watchdog failed to ping host"10.85.10.134"
15:49:14: pid 854: DETAIL: ping process exits with code: 1
15:49:14: pid 854: LOG: waiting for the delegate IP address to become active
15:49:14: pid 854: DETAIL: waiting... count: 1
15:49:17: pid 854: WARNING: watchdog failed to ping host"10.85.10.134"
15:49:17: pid 854: DETAIL: ping process exits with code: 1
15:49:17: pid 854: LOG: waiting for the delegate IP address to become active
15:49:17: pid 854: DETAIL: waiting... count: 2
15:49:20: pid 854: WARNING: watchdog failed to ping host"10.85.10.134"
15:49:20: pid 854: DETAIL: ping process exits with code: 1
15:49:20: pid 854: LOG: waiting for the delegate IP address to become active
15:49:20: pid 854: DETAIL: waiting... count: 3
15:49:20: pid 854: LOG: failed to acquire the delegate IP address
15:49:20: pid 854: DETAIL: 'if_up_cmd' failed
15:49:20: pid 854: WARNING: watchdog escalation failed to acquire delegate IP
15:49:20: pid 850: LOG: watchdog escalation process with pid: 854 exit with SUCCESS.
Notice the "DETAIL: 'if_up_cmd' failed" line. The delegate IP was not brought up.
The following was noted in the /var/log/edb/pgpool3.6/edb-pgpool-3.6.log file:
sudo: sorry, you must have a tty to run sudo
So, what's going on here? Checking the /etc/sudoers file, note this line:
Defaults listpw=all, requiretty, syslog=authpriv, !root_sudo, !umask, env_reset, secure_path = /usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
It looks like a terminal is required by the enterprisedb user to execute the commands to bring up the delegate IP by the fact that "requiretty" is set for all users.
Checking online for the above message, the following link discusses the fix.
Adding the following to /etc/sudoers (use visudo to make changes to this file):
Defaults:enterprisedb !requiretty
So now, enterprisedb can run the relevant commands without a tty.
Since I'm using Puppet to manage the environment, I added the following class to my module that manages the Enterprise DB environment:
class edbas::set_privs {
sudo::default_entry { 'enterprisedb_requiretty':
content => [ '!requiretty', ],
target => ':enterprisedb',
}
sudo::user_specification { 'enterprisedb':
user_list => ['enterprisedb'],
runas => 'root',
cmnd => ['/sbin/ip', '/sbin/arping'],
passwd => false,
}
}
I'm using pupmod-simp-sudo modules in this environment, so this is the actual module that manages the /etc/sudoers file and I call this from my edbas module.
Tuesday, March 20, 2018
Wednesday, February 7, 2018
Migrating a PostgreSQL 9.1 Database with PostGIS to PostgreSQL 9.6.5
This post shows how to migrate a PostgreSQL 9.1 database that has geospatial data types to a PostgreSQL 9.6.5 database.
An in-place upgrade is possible, but this case required was to move from one physical server to another server. The target server already had PostgreSQL 9.6.5 installed.
Note, the following was done on RHEL 7.4 using PostgreSQL 9.6.5.
On source server, dump the source database as follows:
pg_dump -U postgres -Fc -b -v -f "spatial_db.dump" spatial_db
where:
-F specifies output file format
-c specifies custom-format archive file
-b specifies include large objects
-v specifies verbose mode
-f specifies output file
Copy the dump file over to the target server.
On the 9.6.5 instance, create the new database and appropriate extensions:
psql
create database spatial_db;
\c spatial_db
create extension postgis;
create extension btree_gist;
\q
I re-started the 9.6.5 instance at this point. If you get any errors when creating the btree_gist extension, try again and restart the instance. Check what extensions exist in your database as follows:
psql -U postgres -d spatial_db
spatial_db=# \dx
List of installed extensions
<headers removed from dx command>
btree_gist
plpgsql
postgis
You may require other extensions based on your site.
Run the restore as follows:
perl /usr/pgsql-9.6/share/contrib/postgis-2.3/postgis_restore.pl spatial_db.dump | psql -U postgres spatial_db 2>errors.log
You'll need to find the postgis_restore.pl file on your system as it may be located in a different location.
Check the errors.log for any errors.
One thing to note when going from 9.1 to 9.6.5 is that the public.geometry_columns table in 9.1 gets converted to a view in 9.6.5. There might be other objects as well, but this is one that I noticed when I did table counts from 9.1 to 9.6.5.
An in-place upgrade is possible, but this case required was to move from one physical server to another server. The target server already had PostgreSQL 9.6.5 installed.
Note, the following was done on RHEL 7.4 using PostgreSQL 9.6.5.
On source server, dump the source database as follows:
pg_dump -U postgres -Fc -b -v -f "spatial_db.dump" spatial_db
where:
-F specifies output file format
-c specifies custom-format archive file
-b specifies include large objects
-v specifies verbose mode
-f specifies output file
Copy the dump file over to the target server.
On the 9.6.5 instance, create the new database and appropriate extensions:
psql
create database spatial_db;
\c spatial_db
create extension postgis;
create extension btree_gist;
\q
I re-started the 9.6.5 instance at this point. If you get any errors when creating the btree_gist extension, try again and restart the instance. Check what extensions exist in your database as follows:
psql -U postgres -d spatial_db
spatial_db=# \dx
List of installed extensions
<headers removed from dx command>
btree_gist
plpgsql
postgis
You may require other extensions based on your site.
Run the restore as follows:
perl /usr/pgsql-9.6/share/contrib/postgis-2.3/postgis_restore.pl spatial_db.dump | psql -U postgres spatial_db 2>errors.log
You'll need to find the postgis_restore.pl file on your system as it may be located in a different location.
Check the errors.log for any errors.
One thing to note when going from 9.1 to 9.6.5 is that the public.geometry_columns table in 9.1 gets converted to a view in 9.6.5. There might be other objects as well, but this is one that I noticed when I did table counts from 9.1 to 9.6.5.
Thursday, January 11, 2018
PostgreSQL SSL Configuration - Part II
This post describes how to connect to PostgreSQL using Java and client/auth. This assumes that client and server certificates have been installed as described in the previous post.
The PgClientCertDemo.zip can be downloaded here.
Our environment is RHEL 7.4, PostgreSQL 9.6.5 and openjdk version "1.8.0_151".
Java Truststore and Keystore
First, create a Java truststore. Assume that our postgres home is set to /data/pgsql. We need the root and intermediate certificates which can be downloaded from your CA or from the ca-chain.cert.pem file. From this file, copy the intermediate and root certs into separate files e.g. CA-root.crt and CA-intermediate.crt.
Once these are placed in /data/pgsql/.postgresql, import these into the keystore as follows:
$ keytool -import -file CA-root.crt -alias CA-root -keystore cacerts
Enter keystore password:
Re-enter new password:
Owner: CN=...
...
...
...
#3: ObjectId: 2.5.29.14 Criticality=false
SubjectKeyIdentifier [
KeyIdentifier [
0000: 6C 8A 94 A2 77 B1 80 72 1D 81 7A 16 AA F2 DC CE l...w..r..z.....
0010: 66 EE 45 C0 f.E.
]
]
Trust this certificate? [no]: yes
Certificate was added to keystore
$ keytool -import -file CA-intermediate.crt -alias CA-intermediate -keystore cacerts
Enter keystore password:
Certificate was added to keystore
Next, concatenate the postgresql.crt and postgresql.key file as follows:
$ cat postgresql.key postgresql.crt > postgresql.pem
The postgresql.pem will include the private key, the postgres certificate and the intermediate and root certificates.
Take this PEM file and import into the keystore:
$ openssl pkcs12 -export -in postgresql.pem -out keystore.jks -name postgres -noiter -nomaciter
Enter Export Password:
Verifying - Enter Export Password:
So, we now have a trusted certificate store (cacerts) and key store (keystore.jks).
You can check the contents of the trusted store and key store as follows:
$ keytool -list -v -keystore cacerts
$ keytool -list -v -keystore keystore.jks
You will be prompted for a password.
Or, check the contents specifying an alias:
$ keytool -list -v -keystore keystore.jks -alias postgres
$ keytool -list -v -keystore cacerts -alias CA-root
$ keytool -list -v -keystore cacerts -alias CA-intermediate
Source Code
Unzip the contents of PgClientCertDemo.zip to a temporary location. Once can either use Maven to compile or manually as described in the steps below.
Copy the following files to the root of your temporary location:
./au/com/postnewspapers/pgclientcertdemo/pgclientcertdemo/App.java
./au/com/postnewspapers/pgclientcertdemo/pgclientcertdemo/TestConnection.java
./au/com/postnewspapers/pgclientcertdemo/ssl/CustomSSLError.java
./au/com/postnewspapers/pgclientcertdemo/ssl/CustomSSLSocketFactory.java
./au/com/postnewspapers/pgclientcertdemo/ssl/CustomX509KeyManager.java
So now you should have the following files in your temporary location:
App.java
TestConnection.java
CustomSSLError.java
CustomSSLSocketFactory.java
CustomX509KeyManager.java
Open each of these and comment out the following line:
package au.com.postnewspapers.pgclientcertdemo;
Next, download the following JARs into the same location as where the source code is:
commons-lang3-3.7.jar or commons-lang3-3.7.jar
postgresql-42.1.4.jar
Open the following files:
App.java
CustomSSLSocketFactory.java
and change:
import org.apache.commons.lang.exception.ExceptionUtils;
to
import org.apache.commons.lang3.exception.ExceptionUtils;
If you are using the old version of org.apache.commons.lang package, then the above change is not required.
Compilation Step
Compile each class as follows:
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar CustomX509KeyManager.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar CustomSSLSocketFactory.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar CustomSSLError.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar TestConnection.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar App.java
The PgClientCertDemo.zip can be downloaded here.
Our environment is RHEL 7.4, PostgreSQL 9.6.5 and openjdk version "1.8.0_151".
Java Truststore and Keystore
First, create a Java truststore. Assume that our postgres home is set to /data/pgsql. We need the root and intermediate certificates which can be downloaded from your CA or from the ca-chain.cert.pem file. From this file, copy the intermediate and root certs into separate files e.g. CA-root.crt and CA-intermediate.crt.
Once these are placed in /data/pgsql/.postgresql, import these into the keystore as follows:
$ keytool -import -file CA-root.crt -alias CA-root -keystore cacerts
Enter keystore password:
Re-enter new password:
Owner: CN=...
...
...
...
#3: ObjectId: 2.5.29.14 Criticality=false
SubjectKeyIdentifier [
KeyIdentifier [
0000: 6C 8A 94 A2 77 B1 80 72 1D 81 7A 16 AA F2 DC CE l...w..r..z.....
0010: 66 EE 45 C0 f.E.
]
]
Trust this certificate? [no]: yes
Certificate was added to keystore
$ keytool -import -file CA-intermediate.crt -alias CA-intermediate -keystore cacerts
Enter keystore password:
Certificate was added to keystore
Next, concatenate the postgresql.crt and postgresql.key file as follows:
$ cat postgresql.key postgresql.crt > postgresql.pem
The postgresql.pem will include the private key, the postgres certificate and the intermediate and root certificates.
Take this PEM file and import into the keystore:
$ openssl pkcs12 -export -in postgresql.pem -out keystore.jks -name postgres -noiter -nomaciter
Enter Export Password:
Verifying - Enter Export Password:
So, we now have a trusted certificate store (cacerts) and key store (keystore.jks).
You can check the contents of the trusted store and key store as follows:
$ keytool -list -v -keystore cacerts
$ keytool -list -v -keystore keystore.jks
You will be prompted for a password.
Or, check the contents specifying an alias:
$ keytool -list -v -keystore keystore.jks -alias postgres
$ keytool -list -v -keystore cacerts -alias CA-root
$ keytool -list -v -keystore cacerts -alias CA-intermediate
Source Code
Unzip the contents of PgClientCertDemo.zip to a temporary location. Once can either use Maven to compile or manually as described in the steps below.
Copy the following files to the root of your temporary location:
./au/com/postnewspapers/pgclientcertdemo/pgclientcertdemo/App.java
./au/com/postnewspapers/pgclientcertdemo/pgclientcertdemo/TestConnection.java
./au/com/postnewspapers/pgclientcertdemo/ssl/CustomSSLError.java
./au/com/postnewspapers/pgclientcertdemo/ssl/CustomSSLSocketFactory.java
./au/com/postnewspapers/pgclientcertdemo/ssl/CustomX509KeyManager.java
So now you should have the following files in your temporary location:
App.java
TestConnection.java
CustomSSLError.java
CustomSSLSocketFactory.java
CustomX509KeyManager.java
Open each of these and comment out the following line:
package au.com.postnewspapers.pgclientcertdemo;
Next, download the following JARs into the same location as where the source code is:
commons-lang3-3.7.jar or commons-lang3-3.7.jar
postgresql-42.1.4.jar
Open the following files:
App.java
CustomSSLSocketFactory.java
and change:
import org.apache.commons.lang.exception.ExceptionUtils;
to
import org.apache.commons.lang3.exception.ExceptionUtils;
If you are using the old version of org.apache.commons.lang package, then the above change is not required.
Compilation Step
Compile each class as follows:
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar CustomX509KeyManager.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar CustomSSLSocketFactory.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar CustomSSLError.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar TestConnection.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar App.java
Run Step
Run the test as follows:
$ java -cp .:postgresql-42.1.4.jar:commons-lang3-3.7.jar \
-Djavax.net.ssl.trustStore=/data/pgsql/.postgresql/cacerts App default jks \
/data/pgsql/.postgresql/keystore.jks Password1234 \
'jdbc:postgresql://10.10.10.123/postgres?user=postgres&ssl=true'
Using trusted root store: /data/pgsql/.postgresql/cacerts
trustStore is: /data/pgsql/.postgresql/cacerts
trustStore type is : jks
trustStore provider is :
init truststore
adding as trusted cert:
Subject: CN=...
trustStore is: /data/pgsql/.postgresql/cacerts
trustStore type is : jks
trustStore provider is :
init truststore
adding as trusted cert:
Subject: CN=...
...
...
...
main, WRITE: TLSv1.2 Application Data, length = 147
main, READ: TLSv1.2 Application Data, length = 355
main, WRITE: TLSv1.2 Application Data, length = 87
main, READ: TLSv1.2 Application Data, length = 49
main, WRITE: TLSv1.2 Application Data, length = 108
main, READ: TLSv1.2 Application Data, length = 94
main, WRITE: TLSv1.2 Application Data, length = 84
main, READ: TLSv1.2 Application Data, length = 202
main, WRITE: TLSv1.2 Application Data, length = 29
main, called close()
main, called closeInternal(true)
main, SEND TLSv1.2 ALERT: warning, description = close_notify
main, WRITE: TLSv1.2 Alert, length = 26
main, called closeSocket(true)
main, called close()
main, called closeInternal(true)
main, called close()
main, called closeInternal(true)
Connection and query successful, server version is PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
main, READ: TLSv1.2 Application Data, length = 355
main, WRITE: TLSv1.2 Application Data, length = 87
main, READ: TLSv1.2 Application Data, length = 49
main, WRITE: TLSv1.2 Application Data, length = 108
main, READ: TLSv1.2 Application Data, length = 94
main, WRITE: TLSv1.2 Application Data, length = 84
main, READ: TLSv1.2 Application Data, length = 202
main, WRITE: TLSv1.2 Application Data, length = 29
main, called close()
main, called closeInternal(true)
main, SEND TLSv1.2 ALERT: warning, description = close_notify
main, WRITE: TLSv1.2 Alert, length = 26
main, called closeSocket(true)
main, called close()
main, called closeInternal(true)
main, called close()
main, called closeInternal(true)
Connection and query successful, server version is PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
Conclusion
This completes the exercise to show how to connect to PostgreSQL using client/auth using Java. Note that CRL checking is not done in this exercise. The code would need to be modified to accomplish this. We only supply the keystore.jks password and not the postgres password.
Friday, January 5, 2018
PostgreSQL SSL Configuration - Part I
This post describes how to setup PostgreSQL so that we can connect without providing a user password. Instead, we will use certificates to connect. This is required in some secure environments.
Part II of this post will describe how to connect to PostgreSQL using Java and certificates.
Before we, start we'll build a root CA and an intermediate CA. This way, we can generate certificates when we please.
This site was very clear and concise in detailing how to setup the above structure. We'll use RHEL 7.4 and PostgreSQL 9.6.5.
Note that you may customize your openssl.cnf for your own needs. Again, this is a test scenario. You would most likely be getting your certificates from a certified CA.
Once your root and intermediate CAs' are set up, generate a certificate signing request (CSR) for your PostgreSQL server:
# cd /root/ca
# openssl genrsa -out intermediate/private/server.key 2048
# chmod 400 intermediate/private/server.key
Create the certificate:
# openssl req -config intermediate/openssl.cnf \
-key intermediate/private/server.key \
-new -sha256 -out intermediate/csr/server.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [US]:
State or Province Name [CA]:
Locality Name [San Jose]:
Organization Name [Alice Ltd]:
Organizational Unit Name []:Alice Ltd Web Services
Common Name []:pgserver.domain.us
Email Address []:
Sign the certificate using the intermediate CA:
# openssl ca -config intermediate/openssl.cnf \
-extensions server_cert -days 375 -notext -md sha256 \
-in intermediate/csr/server.csr \
-out intermediate/certs/server.crt
Using configuration from intermediate/openssl.cnf
Enter pass phrase for /root/ca/intermediate/private/intermediate.key.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 4098 (0x1002)
Validity
...
...
...
X509v3 Key Usage: critical
Digital Signature, Key Encipherment
X509v3 Extended Key Usage:
TLS Web Server Authentication
Certificate is to be certified until Jan 13 20:55:45 2019 GMT (375 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
We can verify the certificate as follows:
# openssl x509 -noout -text -in intermediate/certs/server.crt
Certificate:
Data:
Version: 3 (0x2)
Serial Number: 4098 (0x1002)
Signature Algorithm: sha256WithRSAEncryption
...
...
...
The CA chain file should have been created if you followed the guide. If not, this is how you can do it:
# cd /root/ca/
# cat intermediate/certs/intermediate.cert.pem /root/ca/certs/ca.cert.pem > ca-chain.cert.pem
# chmod 444 intermediate/certs/ca-chain.cert.pem
As root, copy the ca-chain.cert.pem, server.crt and server.key files to the $PGDATA directory:
# export PGDATA=/var/lib/pgsql/9.6/data
# cd /root/ca
# cp intermediate/certs/ca-chain.cert.pem $PGDATA/root.crt
# cp intermediate/certs/server.crt $PGDATA
# cp intermediate/private/server.key $PGDATA
# cd $PGDATA
# chown postgres:postgres root.crt server.crt server.key
As the postgres user, make the following changes to the $PGDATA/postgresql.conf:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'
Restart the PostgreSQL server:
$ pg_ctl restart
Now create a certificate for the postges user:
# cd /root/ca
# openssl genrsa -out intermediate/private/postgresql.key 2048
# chmod 400 intermediate/private/postgresql.key
Create the certificate:
# cd /root/ca
# openssl req -config intermediate/openssl.cnf \
-key intermediate/private/postgresql.key \
-new -sha256 -out intermediate/csr/postgresql.csr
Country Name (2 letter code) [US]:
State or Province Name [CA]:
Locality Name [San Jose]:
Organization Name [Alice Ltd]:
Organizational Unit Name []:Alice Ltd Web Services
Common Name []:postgres.domain.us
Email Address []:
As before, sign the certificate request using the intermediate CA:
# cd /root/ca
# openssl ca -config intermediate/openssl.cnf \
-extensions server_cert -days 375 -notext -md sha256 \
-in intermediate/csr/postgresql.csr \
-out intermediate/certs/postgresql.crt
Using configuration from intermediate/openssl.cnf
Enter pass phrase for /root/ca/intermediate/private/intermediate.key.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 4099 (0x1003)
Validity
Not Before: Jan 5 16:52:54 2018 GMT
Not After : Jan 15 16:52:54 2019 GMT
...
...
...
TLS Web Server Authentication
Certificate is to be certified until Jan 15 16:52:54 2019 GMT (375 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
Make usre /var/lib/pgsql/.postgresql exists and is owned by postgres.
Next, copy the following to /var/lib/pgsql/.postgresql:
# cd /root/ca
# cp intermediate/certs/postgresql.crt /var/lib/pgsql/.postgresql
# cp intermediate/private/postgresql.key /var/lib/pgsql/.postgresql
# cp intermediate/certs/ca-chain.cert.pem /var/lib/pgsql/.postgresql/root.crt
# chown postgres:postgres /var/lib/pgsql/.postgresql/*
Our host IP is 10.10.10.123. This is reflected in the pg_hba.conf file.
As the postgres user, make the following changes to $PGDATA/pg_hba.conf and $PGDATA/pg_ident.conf:
pg_ident.conf
# MAPNAME SYSTEM-USERNAME PG-USERNAME
ssl-test postgres.domain.us postgres
pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
hostssl all all 10.10.10.123/32 cert clientcert=1 map=ssl-test
Now, we can test our SSL connection to the database:
$ cd /var/lib/pgsql/.postgresql
$ psql 'host=10.10.10.123 port=5432 dbname=postgres user=postgres sslmode=require sslcert=postgresql.crt sslkey=postgresql.key sslrootcert=root.crt'
psql (9.6.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=#
Logs should show something like the following:
Jan 5 12:14:20 pgs01 postgres[3817]: [6-1] 2018-01-05 12:14:20 EST [3817]: [1] user=postgres,db=postgres 2018-01-05 12:14:20.975 EST 00000 5a4fb26c.ee9 pgs01(37242) [unknown] 2018-01-05 12:14:20 EST LOG: connection authorized: user=postgres database=postgres SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)
Part II of this post will describe how to connect to PostgreSQL using Java and certificates.
Before we, start we'll build a root CA and an intermediate CA. This way, we can generate certificates when we please.
This site was very clear and concise in detailing how to setup the above structure. We'll use RHEL 7.4 and PostgreSQL 9.6.5.
Note that you may customize your openssl.cnf for your own needs. Again, this is a test scenario. You would most likely be getting your certificates from a certified CA.
Once your root and intermediate CAs' are set up, generate a certificate signing request (CSR) for your PostgreSQL server:
# cd /root/ca
# openssl genrsa -out intermediate/private/server.key 2048
# chmod 400 intermediate/private/server.key
Create the certificate:
# openssl req -config intermediate/openssl.cnf \
-key intermediate/private/server.key \
-new -sha256 -out intermediate/csr/server.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [US]:
State or Province Name [CA]:
Locality Name [San Jose]:
Organization Name [Alice Ltd]:
Organizational Unit Name []:Alice Ltd Web Services
Common Name []:pgserver.domain.us
Email Address []:
Sign the certificate using the intermediate CA:
# openssl ca -config intermediate/openssl.cnf \
-extensions server_cert -days 375 -notext -md sha256 \
-in intermediate/csr/server.csr \
-out intermediate/certs/server.crt
Using configuration from intermediate/openssl.cnf
Enter pass phrase for /root/ca/intermediate/private/intermediate.key.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 4098 (0x1002)
Validity
...
...
...
X509v3 Key Usage: critical
Digital Signature, Key Encipherment
X509v3 Extended Key Usage:
TLS Web Server Authentication
Certificate is to be certified until Jan 13 20:55:45 2019 GMT (375 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
We can verify the certificate as follows:
# openssl x509 -noout -text -in intermediate/certs/server.crt
Certificate:
Data:
Version: 3 (0x2)
Serial Number: 4098 (0x1002)
Signature Algorithm: sha256WithRSAEncryption
...
...
...
The CA chain file should have been created if you followed the guide. If not, this is how you can do it:
# cd /root/ca/
# cat intermediate/certs/intermediate.cert.pem /root/ca/certs/ca.cert.pem > ca-chain.cert.pem
# chmod 444 intermediate/certs/ca-chain.cert.pem
As root, copy the ca-chain.cert.pem, server.crt and server.key files to the $PGDATA directory:
# export PGDATA=/var/lib/pgsql/9.6/data
# cd /root/ca
# cp intermediate/certs/ca-chain.cert.pem $PGDATA/root.crt
# cp intermediate/certs/server.crt $PGDATA
# cp intermediate/private/server.key $PGDATA
# cd $PGDATA
# chown postgres:postgres root.crt server.crt server.key
As the postgres user, make the following changes to the $PGDATA/postgresql.conf:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'
Restart the PostgreSQL server:
$ pg_ctl restart
Now create a certificate for the postges user:
# cd /root/ca
# openssl genrsa -out intermediate/private/postgresql.key 2048
# chmod 400 intermediate/private/postgresql.key
Create the certificate:
# cd /root/ca
# openssl req -config intermediate/openssl.cnf \
-key intermediate/private/postgresql.key \
-new -sha256 -out intermediate/csr/postgresql.csr
Country Name (2 letter code) [US]:
State or Province Name [CA]:
Locality Name [San Jose]:
Organization Name [Alice Ltd]:
Organizational Unit Name []:Alice Ltd Web Services
Common Name []:postgres.domain.us
Email Address []:
As before, sign the certificate request using the intermediate CA:
# cd /root/ca
# openssl ca -config intermediate/openssl.cnf \
-extensions server_cert -days 375 -notext -md sha256 \
-in intermediate/csr/postgresql.csr \
-out intermediate/certs/postgresql.crt
Using configuration from intermediate/openssl.cnf
Enter pass phrase for /root/ca/intermediate/private/intermediate.key.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 4099 (0x1003)
Validity
Not Before: Jan 5 16:52:54 2018 GMT
Not After : Jan 15 16:52:54 2019 GMT
...
...
...
TLS Web Server Authentication
Certificate is to be certified until Jan 15 16:52:54 2019 GMT (375 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
Make usre /var/lib/pgsql/.postgresql exists and is owned by postgres.
Next, copy the following to /var/lib/pgsql/.postgresql:
# cd /root/ca
# cp intermediate/certs/postgresql.crt /var/lib/pgsql/.postgresql
# cp intermediate/private/postgresql.key /var/lib/pgsql/.postgresql
# cp intermediate/certs/ca-chain.cert.pem /var/lib/pgsql/.postgresql/root.crt
# chown postgres:postgres /var/lib/pgsql/.postgresql/*
Our host IP is 10.10.10.123. This is reflected in the pg_hba.conf file.
As the postgres user, make the following changes to $PGDATA/pg_hba.conf and $PGDATA/pg_ident.conf:
pg_ident.conf
# MAPNAME SYSTEM-USERNAME PG-USERNAME
ssl-test postgres.domain.us postgres
pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
hostssl all all 10.10.10.123/32 cert clientcert=1 map=ssl-test
Now, we can test our SSL connection to the database:
$ cd /var/lib/pgsql/.postgresql
$ psql 'host=10.10.10.123 port=5432 dbname=postgres user=postgres sslmode=require sslcert=postgresql.crt sslkey=postgresql.key sslrootcert=root.crt'
psql (9.6.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=#
Logs should show something like the following:
Jan 5 12:14:20 pgs01 postgres[3817]: [6-1] 2018-01-05 12:14:20 EST [3817]: [1] user=postgres,db=postgres 2018-01-05 12:14:20.975 EST 00000 5a4fb26c.ee9 pgs01(37242) [unknown] 2018-01-05 12:14:20 EST LOG: connection authorized: user=postgres database=postgres SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)
Tuesday, January 2, 2018
PostgreSQL Backup Script
This script backups a PostgreSQL cluster.
It is assumed that this cluster is STIG'd using DISA STIG guidelines. SSL is turned on and configured correctly.
The following parameters are set in postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /data/pgsql/9.6/wals/%f'
For this case:
PGDATA=/data/pgsql/9.6/data
BKUPDIR=/var/lib/pgsql/9.6/backup
For security reason, SSL is turned on. The following is set in postgresql.conf:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'
It is assumed that valid certs have been installed for PostgreSQL.
The pg_hba.conf file looks like this:
# TYPE DATABASE USER ADDRESS METHOD
hostssl all all 10.10.10.123/32 cert clientcert=1 map=ssl-test
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv4 network connections
hostnossl all all 0.0.0.0/0 reject
The pg_ident.hba file looks like this:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
ssl-test postgres.dmn.org postgres
The backup script is shown below:
#!/bin/sh
# pg_basebackup won't work with hostssl and cert clientcert=1
# so, we do the backup using the API
LABEL="Base Backup on $(/bin/hostname) on $(/bin/date +%Y%m%d%S)"
DT=`/bin/date +%Y%m%d%S`
BKUPDIR=/var/lib/pgsql/9.6/backup
HOST=pgs01.dmn.mil
STR="hostssl replication postgres 10.10.10.123/32 trust"
echo $STR
/bin/echo $STR >> /data/pgsql/9.6/data/pg_hba.conf
/usr/pgsql-9.6/bin/pg_ctl reload
/usr/pgsql-9.6/bin/pg_basebackup -D ${BKUPDIR}/${DT} --xlog-method=fetch --format=tar --write-recovery-conf --progress --verbose --username=postgres --no-password --gzip --host=${HOST} --label="${LABEL}"
/bin/sed -i '/hostssl replication postgres 10.10.10.123\/32 trust/d' /data/pgsql/9.6/data/pg_hba.conf
/usr/pgsql-9.6/bin/pg_ctl reload
Before the backup begins, the script adds the hostssl replication line defined by STR above and does a pg_ctl reload so that the changes in pg_hba.conf are picked by the postgres server. This allows a trusted connection to be made from the local server using SSL (or you could simply change this to 127.0.0.1).
The backup is executed using pg_basebackup.
After the backup is completed, the hostssl replication line is removed and the pg_hba.conf file is reloaded. This is done for STIG compliance.
The backup is run from root's cron once a week:
# PostgreSQL Weekly Full Backups on Sundays
0 0 * * 0 /bin/su -l postgres -c "/bin/sh /data/pgsql/bkup.sh"
It is assumed that this cluster is STIG'd using DISA STIG guidelines. SSL is turned on and configured correctly.
The following parameters are set in postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /data/pgsql/9.6/wals/%f'
For this case:
PGDATA=/data/pgsql/9.6/data
BKUPDIR=/var/lib/pgsql/9.6/backup
For security reason, SSL is turned on. The following is set in postgresql.conf:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'
It is assumed that valid certs have been installed for PostgreSQL.
The pg_hba.conf file looks like this:
# TYPE DATABASE USER ADDRESS METHOD
hostssl all all 10.10.10.123/32 cert clientcert=1 map=ssl-test
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv4 network connections
hostnossl all all 0.0.0.0/0 reject
The pg_ident.hba file looks like this:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
ssl-test postgres.dmn.org postgres
The backup script is shown below:
#!/bin/sh
# pg_basebackup won't work with hostssl and cert clientcert=1
# so, we do the backup using the API
LABEL="Base Backup on $(/bin/hostname) on $(/bin/date +%Y%m%d%S)"
DT=`/bin/date +%Y%m%d%S`
BKUPDIR=/var/lib/pgsql/9.6/backup
HOST=pgs01.dmn.mil
STR="hostssl replication postgres 10.10.10.123/32 trust"
echo $STR
/bin/echo $STR >> /data/pgsql/9.6/data/pg_hba.conf
/usr/pgsql-9.6/bin/pg_ctl reload
/usr/pgsql-9.6/bin/pg_basebackup -D ${BKUPDIR}/${DT} --xlog-method=fetch --format=tar --write-recovery-conf --progress --verbose --username=postgres --no-password --gzip --host=${HOST} --label="${LABEL}"
/bin/sed -i '/hostssl replication postgres 10.10.10.123\/32 trust/d' /data/pgsql/9.6/data/pg_hba.conf
/usr/pgsql-9.6/bin/pg_ctl reload
Before the backup begins, the script adds the hostssl replication line defined by STR above and does a pg_ctl reload so that the changes in pg_hba.conf are picked by the postgres server. This allows a trusted connection to be made from the local server using SSL (or you could simply change this to 127.0.0.1).
The backup is executed using pg_basebackup.
After the backup is completed, the hostssl replication line is removed and the pg_hba.conf file is reloaded. This is done for STIG compliance.
The backup is run from root's cron once a week:
# PostgreSQL Weekly Full Backups on Sundays
0 0 * * 0 /bin/su -l postgres -c "/bin/sh /data/pgsql/bkup.sh"
Subscribe to:
Posts (Atom)