Using an SSL connection v41
EDB*Plus can connect to the EDB Postgres Advanced Server database 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 creating the Java truststore and keystore on the host that runs EDB*Plus.
The Java truststore is the file containing the certificate authority (CA) certificates. The Java client (EDB*Plus) uses the certificate 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.
Refer to this material for guidance in setting up the SSL connections:
For information on setting up SSL connectivity to the EDB Postges Advanced Server database, see Secure TCP/IP Connections with SSL in the PostgreSQL core documentation.
For information on JDBC client connectivity using SSL, see Configuring the Client in the PostgreSQL JDBC Interface documentation.
This example configures SSL on a database server to show 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 (
CN=enterprisedb in this example) must be the host name that is specified when connecting to EDB*Plus.
In addition, you can use user name maps as defined in the
pg_ident.conf file to permit more flexibility for the common name and database user name, described in later steps.
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 use 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 EDB Postgres 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 subdirectory of the database server. Set the permissions so that no groups or accounts other than the owner can access these files.
Step 7: In the
postgresql.conf file, make the following changes.
Step 8: Modify the
pg_hba.conf file to enable SSL use on the database to which you want EDB*Plus 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=verify-full. This setting requires 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 to use a mapping named
sslusers defined in the
pg_ident.conf file 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 username maps in the
pg_ident.conf file related to the
pg_hba.conf file by the
map=sslusers option. These username maps permit you to specify database user names
enterprisedb when connecting with EDB*Plus.
Step 10: Restart the database server.
After you configure SSL on the database server, this example shows how to generate certificate and keystore files for EDB*Plus (the JDBC client).
Step 1: Using files
server.key located under the database server data subdirectory, create copies of these files and move them to the host for EDB*Plus.
Store these files in the directory to contain the trusted certificate and keystore files you generate. The suggested location is to create a
.postgresql subdirectory under the home user account that invokes EDB*Plus. Thus, these files are under the
~/.postgresql directory of the user account that runs 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 next.
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 you can access it in its default location
~/.postgresql postgresql.keystore, which is under the home directory of the user account invoking EDB*Plus. The file name suffix can be
.jks instead of
.keystore (that is,
postgresql.jks). In these examples, the 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 are prompted for a new password. Save this password as you must specify it 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 you can access it in its default location
~/.postgresql/postgresql.p12, which is under the home directory of the user account invoking EDB*Plus.
For Windows only: The path is
You're prompted for a new password. Save this password as you must specify it with the
PGSSLKEYPASS environment variable.
Step 6: If the
postgresql.p12 files aren't 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 isn't used, then you must set the full path (including the file name) to the
postgresql.keystore file with the
PGSSLCERT environment variable. You must also set the full path (including the file name) to file
postgresql.p12 with the
PGSSLKEY environment variable before invoking EDB*Plus.
In addition, if the generated file from Step 4 wasn't named
postgresql.jks, then use the
PGSSLCERT environment variable to set the file name and its location. Similarly, if the generated file from Step 5 wasn't named
postgresql.p12, then use the
PGSSLKEY environment variable to set the file name and its location.
To perform an SSL connection, be sure to address the following:
- The trusted certificate and keystore files were generated for both the database server and the client host 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 were placed in the user account’s
~/.postgresqldirectory or the environment variables
PGSSLKEYwere 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 in which EDB*Plus is invoked from a host that's 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 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, you can invoke EDB*Plus in the following manner:
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 EDB Postgres Advanced Server.
You can use other database user names 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 EDB Postgres Advanced Server.