Using a SSL Connection v39
An EDB*Plus connection to the Advanced Server database can be accomplished using secure sockets layer (SSL) connectivity.
Using SSL requires various prerequisite configuration steps performed on the database server involved with the SSL connection as well as creation of the Java truststore and keystore on the host that will run EDB*Plus.
The Java truststore is the file containing the Certificate Authority (CA) certificates with which the Java client (EDB*Plus) uses to verify the authenticity of the server to which it is initiating an SSL connection.
The Java keystore is the file containing private and public keys and their corresponding certificates. The keystore is required for client authentication to the server, which is used for the EDB*Plus connection.
The following is material to which you can refer to for guidance in setting up the SSL connections:
For information on setting up SSL connectivity to the Advanced Server database, see the section about secure TCP connections with SSL in Chapter 18 “Server Setup and Operation” in the PostgreSQL Core Documentation located at:
For information on JDBC client connectivity using SSL, see the section on configuring the client in Chapter 4 “Using SSL” in The PostgreSQL JDBC Interface located at:
The following sections provide information for the configuration steps of using SSL.
- Configuring SSL on Advanced Server
- Configuring SSL for the EDB*Plus client
- Requesting SSL connection to the Advanced Server database
This section provides an example of configuring SSL on a database server to demonstrate the use of SSL with EDB*Plus. A self-signed certificate is used for this purpose.
Step 1: Create the certificate signing request (CSR).
In the following example the generated certificate signing request file is
server.csr. The private key is generated as file
When creating the certificate, the value specified for the common name field (designated as
CN=enterprisedb in this example) must be the database user name that is specified when connecting to EDB*Plus.
In addition, user name maps can be used as defined in the
pg_ident.conf file to permit more flexibility for the common name and database user name. Steps 8 and 9 describe the use of user name
Step 2: Generate the self-signed certificate.
The following generates a self-signed certificate to file
server.crt using the certificate signing request file,
server.csr, and the private key,
server.key, as input.
Step 3: Make a copy of the server certificate (
server.crt) to be used as the root Certificate Authority (CA) file (
Step 4: Delete the now redundant certificate signing request (
Step 5: Move or copy the certificate and private key files to the Advanced Server data directory (for example,
Step 6: Set the file ownership and permissions on the certificate files and private key file.
Set the ownership to the operating system account that owns the data sub-directory of the database server. Set the permissions so that no other groups or accounts other than the owner can access these files.
Step 7: In the
postgresql.conf file, make the following modifications.
Step 8: Modify the
pg_hba.conf file to enable SSL usage on the desired database to which EDB*Plus is to make the SSL connection.
pg_hba.conf file, the
hostssl type indicates the entry is used to validate SSL connection attempts from the client (EDB*Plus).
The authentication method is set to cert with the option
clientcert=1 in order to require an SSL certificate from the client against which authentication is performed using the common name of the certificate (
enterprisedb in this example).
map=sslusers option specifies that a mapping named
sslusers defined in the
pg_ident.conf file is to be used for authentication. This mapping allows a connection to the database if the common name from the certificate and the database user name attempting the connection match the
SYSTEM-USERNAME/PG-USERNAME pair listed in the
The following is an example of the settings in the
pg_hba.conf file if the database
(edb) must use SSL connections.
Step 9: The following shows the user name maps in the
pg_ident.conf file related to the
pg_hba.conf file by the
map=sslusers option. These user name maps permit you to specify database user names
enterprisedb when connecting with EDB*Plus.
Step 10: Restart the database server after you have made the changes to the configuration files.
After you have configured SSL on the database server, the following steps provide an example of generating certificate and keystore files for EDB*Plus (the JDBC client).
Step 1: Using files
server.key located under the database server data sub-directory, create copies of these files and move them to the host where EDB*Plus is to be running.
Store these files in the desired directory to contain the trusted certificate and keystore files to be generated in the following steps. The suggested location is to create a
.postgresql sub-directory under the home user account that will invoke EDB*Plus. Thus, these files will be under the
~/.postgresql directory of the user account that will run EDB*Plus.
For this example, assume file
edb.crt is a copy of
edb.key is a copy of
Step 2: Create an additional copy of
Step 3: Create a Distinguished Encoding Rules (DER) format of file
edb_root.crt. The generated DER format of this file is
edb_root.crt.der. The DER format of the file is required for the
keytool program used in Step 4.
Step 4: Use the
keytool program to create a keystore file
edb_root.crt.der as the input. This process adds the certificate of the Postgres database server to the keystore file.
The file name
postgresql.keystore is recommended so that it can be accessed in its default directory location
~/.postgresql postgresql.keystore, which is under the home directory of the user account invoking EDB*Plus. Also note that the file name suffix can be
.jks instead of
.keystore (thus, file name
postgresql.jks). However, in the remainder of these examples, file name
postgresql.keystore is used.
For Windows only: The path is
keytool program can be found under the
bin subdirectory of the Java Runtime Environment installation.
You will be prompted for a new password. Save this password as it must be specified with the PGSSLCERTPASS environment variable.
Step 5: Create a
PKCS #12 format of the keystore file
(postgresql.p12) using files
edb.key as input.
The file name
postgresql.p12 is recommended so that it can be accessed in its default directory location
~/.postgresql/postgresql.p12, which is under the home directory of the user account invoking EDB*Plus.
For Windows only: The path is
You will be prompted for a new password. Save this password as it must be specified with the PGSSLKEYPASS environment variable.
Step 6: If the
postgresql.p12 files are not already in the
~/.postgresql directory, move or copy them to that location.
For Windows only: The directory is
Step 7: If the default location
~/.postgresql is not used, then the full path (including the file name) to the
postgresql.keystore file must be set with the
PGSSLCERT environment variable, and the full path (including the file name) to file
postgresql.p12 must be set with the
PGSSLKEY environment variable before invoking EDB*Plus.
In addition, if the generated file from Step 4 was not named
postgresql.jks then, use the
PGSSLCERT environment variable to designate the file name and its location. Similarly, if the generated file from Step 5 was not named
postgresql.p12 then, use the
PGSSLKEY environment variable to designate the file name and its location.
Be sure the following topics have been addressed in order to perform an SSL connection:
- The trusted certificate and keystore files have been generated for both the database server and the client host to be invoking EDB*Plus.
postgresql.conffile for the database server contains the updated configuration parameters.
pg_hba.conffile for the database server contains the required entry for permitting the SSL connection.
- For the client host, either the client’s certificate and keystore files have been placed in the user account’s
~/.postgresqldirectory or the environment variables
PGSSLKEYare set before invoking EDB*Plus.
PGSSLCERTPASSenvironment variable is set with a password.
PGSSLKEYPASSenvironment variable is set with a password
When invoking EDB*Plus, include the
?ssl=true parameter in the database connection string as shown for the
connectstring option in Using EDB*Plus.
The following is an example where EDB*Plus is invoked from a host that is remote to the database server.
postgresql.conf file of the database server contains the following modified parameters:
pg_hba.conf file of the database server contains the following entry for connecting from EDB*Plus on the remote host:
On the remote host where EDB*Plus is to be invoked, the Linux user account named
user contains the certificate and keystore files in its
Logged into Linux with the account named
user, EDB*Plus is successfully invoked with the
Alternatively, without placing the certificate and keystore files in
~/.postgresql, but in a different directory, EDB*Plus can be invoked in the following manner:
Note that in both cases the database user name used to log into EDB*Plus is
enterprisedb as this is the user specified for the common name field when creating the certificate in Step 1 of Configuring SSL on Advanced Server.
Other database user names can be used if the
pg_hba.conf file with the
map option and the
pg_ident.conf file are used as described in Steps 8 and 9 of Configuring SSL on Advanced Server.