1. Introduction
SSL (Secured Sockets Layer) also known as TLS (Transport Layer Security) is a standard security technology for establishing encrypted connection between a server and a client. SSL uses a combination of public key and symmetric key encryption to secure connection between two machines communicating over the Internet or an internal network.
SSL uses two sub-protocols: the record protocol and the handshake protocol. These protocols allow a client to authenticate and establish an encrypted connection. During initial handshake, a server presents its digital certificate to the client to authenticate the server’s identity. Server certificate follow the X.509 certificate format defined by the Public-Key Cryptography Standards (PKCS). The authentication process uses public-key encryption to validate the digital certificate and confirm the identity of the server.
Once the server has been authenticated, the client and server establish cipher settings and a shared key to encrypt the information exchanged while the session is in progress.
This guide describes the steps needed to enable SSL authentication for an EDB Postgres Advanced Server database.
The steps used in this guide were run on a CentOS 7.x server running EDB Postgres Advanced Server (EPAS) 9.5.
The guide also assumes that OpenSSL is already installed on the server. If needed, following package can be installed to enable openssl:
openssl-1.0.1e-51.el7_2.5.x86_64
2. Generating SSL Certificates for the database server
This section describes the steps required to generate server certificate, trusted root certificate, and private key for the EDB Postgres Advanced Server.
2.1 Login as enterprisedb user and change directory to the data directory:
# su – enterprisedb
$ cd /opt/PostgresPlus/9.5AS/data
2.2 Generate a private key (you must enter a pass phrase):
$ openssl genrsa -des3 -out server.key 1024
Generating RSA private key, 1024 bit long modulus ........++++++
................++++++
e is 65537 (0x10001)
Enter pass phrase for server.key:
Verifying - Enter pass phrase for server.key:
2.3 Remove the passphrase to allow automatic start-up of the server:
$ openssl rsa -in server.key -out server.key
Enter pass phrase for server.key:
writing RSA key
2.4 Remove group and other’s permission from the private key file:
$ chmod og-rwx server.key
2.5 Create a self-signed certificate.
A self-signed certificate can be used for testing, but for production servers, use a certificate signed by a certificate authority (CA).
Make sure to enter local hostname as the “Common Name”. The challenge password can be keft blank.
$ openssl req -new -key server.key -days 3650 -out server.crt -x509
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) [XX]:US
State or Province Name (full name) []:MA
Locality Name (eg, city) [Default City]:Boston
Organization Name (eg, company) [Default Company Ltd]:EDB Organizational Unit Name (eg, section) []:PS
Common Name (eg, your name or your server's hostname) []:snvm001 Email Address []:info@edb.com
2.6 For self-signed certificates, use the server certificate as the trusted root certificate:
$ cp server.crt root.crt
2.7 Create the private key postgresql.key for the client machine and remove the passphrase:
$ openssl genrsa -des3 -out /tmp/postgresql.key 1024
Generating RSA private key, 1024 bit long modulus .........++++++
................++++++
e is 65537 (0x10001)
Enter pass phrase for /tmp/postgresql.key:
Verifying - Enter pass phrase for /tmp/postgresql.key:
$ openssl rsa -in /tmp/postgresql.key -out /tmp/postgresql.key
Enter pass phrase for /tmp/postgresql.key:
writing RSA key
2.8 Create the certificate postgresql.crt.
The common name must be the database user name that will connect to the database:
$ openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.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) [XX]:US
State or Province Name (full name) []:MA
Locality Name (eg, city) [Default City]:Boston
Organization Name (eg, company) [Default Company Ltd]:EDB Organizational Unit Name (eg, section) []:PS
Common Name (eg, your name or your server's hostname) []:enterprisedb Email Address []:info@edb.com
Please enter the following 'extra' attributes to be sent with your certificate request
A challenge password []:
An optional company name []:
2.9 Sign it using the trusted root certificate:
$ openssl x509 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial
Signature ok subject=/C=US/ST=MA/L=Boston/O=EDB/OU=PS/CN=enterprisedb/emailAddres s=info@edb.com
Getting CA Private Key
Copy the three files generated in /tmp directory to the client machine along with the trusted root certificate (root.crt).
Make sure to delete files from /tmp directory after copying them to the client machine.
3. Prepare Database Server for SSL Authentication
3.1 Edit the postgresql.conf file to activate SSL:
# su – enterprisedb
$ cd /opt/PostgresPlus/9.5AS/data $ vi postgresql.conf
Uncomment & change following parameters:
ssl = on
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
ssl_prefer_server_ciphers = on ssl_cert_file = 'server.crt' ssl_key_file = 'server.key' ssl_ca_file = 'root.crt' ssl_crl_file = ''
Save the file and exit vi editor.
# (change requires restart) # allowed SSL ciphers
# (change requires restart) # (change requires restart) # (change requires restart) # (change requires restart) # (change requires restart) # (change requires restart)
3.2 Add following entry for the client machine in pg_hba.conf file:
$ vi pg_hba.conf
hostssl all enterprisedb <Client IP Address>/32 cert clientcert=1
3.3 Restart the server:
$ pg_ctl stop
$ pg_ctl start
4. Test SSL connection from client
Login to client machine and run psql to test the connection, if postgres is installed:
$ psql ‘host=<db host name> port=<dbport> dbname=<db name> user=enterprisedb sslmode=verify-full sslcert=<path>/postgresql.crt sslkey=<path>/postgresql.key sslrootcert=<path>/root.crt’
Where: path= the path where certificate and key files were copied on the client. Example:
Make sure the username is the db user name that was selected as “Common Name” while creating the private key.
5. Test SSL JDBC connection
5.1 In the java program, make sure to set following property
props.setProperty("ssl","true");
Or in the connection url:
String url = "jdbc:postgresql://localhost/test?user=fred&password=secret&ssl=true";
5.2 Recompile the java program after making the changes.
In order to make the server certificate available to Java, convert it to Java format: $ openssl x509 -in server.crt -out server.crt.der -outform der
5.3 Import the certificate into java’s system truststore:
$ keytool -keystore $JAVA_HOME/lib/security/cacerts -alias postgresql -import -file server.crt.der
If one doesn’t have access to the system cacerts trustore, then a personal truststore can be created as below:
$ keytool -keystore mystore -alias postgresql -import -file server.crt.der Test the java program, as below:
$ java -Djavax.net.ssl.trustStore=mystore com.mycompany.MyApp
Example:
$ java -classpath .:/opt/PostgresPlus/connectors/jdbc/edb-jdbc17.jar - Djavax.net.ssl.trustStore=mystore pg_test2 public
---EDB Postgres JDBC Testing---
EDB Postgres JDBC Driver Registered!
You made it!
In order to use certificate authentication (without a password), use following steps:
5.4 Covert the client certificate in DER format:
openssl x509 -in postgresql.crt -out postgresql.crt.der -outform der
5.5 convert the client key in DER format:
openssl pkcs8 -topk8 -outform DER -in postgresql.key -out postgresql.key.pk8 -nocrypt
5.6 Copy client files (postgresql.crt.der, postgresql.key.pk8) and root certificate to the client machine and use following properties in your java program to test it:
String url = "jdbc:edb://snvm001:5444/edbstore";
Properties props = new Properties();
props.setProperty("user","enterprisedb");
props.setProperty("ssl","true");
props.setProperty("sslmode","verify-full");
props.setProperty("sslcert","postgresql.crt.der");
props.setProperty("sslkey","postgresql.key.pk8");
props.setProperty("sslrootcert","root.crt");
5.7 Compile the java program and test it as below:
java -Djavax.net.ssl.trustStore=mystore -classpath .:./edb-jdbc18.jar pg_ssl public
---EDB Postgres JDBC Testing---
EDB Postgres JDBC Driver Registered!
You made it!
For troubleshooting connection issues, add -Djavax.net.debug=ssl to the java command.