SSL Connection to PostgreSQL

Establishing SSL Connection to PostgreSQL

Encrypt PostgreSQL connections to protect authentication credentials and database traffic from interception. This guide configures the PostgreSQL server for SSL, generates server and client certificates, and establishes a secure connection through pgAdmin.

In this article

PostgreSQL Server Configuration

Create an environment with a PostgreSQL database if one is not already available.

Create PostgreSQL database
Create an environment containing a PostgreSQL database.
1

Connect to PostgreSQL through SSH

Connect to the PostgreSQL database server through the platform SSH Gate. If SSH access is not configured yet, generate an SSH key pair, add the public key to the dashboard, and then connect to the account through SSH.

Connect to PostgreSQL via SSH
Connect to the PostgreSQL server through SSH.

SSL requires three files in the PostgreSQL data directory:

server.keyPrivate key for the PostgreSQL server.
server.crtServer certificate.
root.crtTrusted root certificate.

Generate Server Certificates

Create the Private Key

cd /var/lib/pgsql/data
openssl genrsa -des3 -out server.key 1024

Enter and confirm a pass phrase when prompted.

Generate SSL private key
Generate server.key in the PostgreSQL data directory.

Remove the pass phrase from the key so PostgreSQL can use it:

openssl rsa -in server.key -out server.key
Remove pass phrase from SSL key
Remove the pass phrase from server.key.

Set the required permissions and ownership:

chmod 400 server.key
chown postgres.postgres server.key
Change SSL key permissions
Restrict server.key permissions and assign it to the postgres user.

Create the Server Certificate

openssl req -new -key server.key -days 3650 -out server.crt -x509 \
-subj '/C=US/ST=California/L=PaloAlto/O=Jelastic/CN=mysite.com/emailAddress=mail@jelastic.com'

For production, replace the example subject values with the actual certificate identity information. The subject can also be entered interactively by omitting the -subj parameter.

Create server SSL certificate
Generate the PostgreSQL server certificate.

Because the example uses a self-signed certificate, copy the server certificate as the trusted root certificate:

cp server.crt root.crt
Copy server SSL certificate
Create root.crt from the self-signed server certificate.

Configure pg_hba.conf

Open /var/lib/pgsql/data/pg_hba.conf and configure local and SSL-authenticated connections:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "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 remote connections for authenticated users
hostssl all         webadmin    0.0.0.0/0             md5 clientcert=verify-full

If a PostgreSQL user other than the default webadmin is used, replace the username in the last line and use the same username in the later certificate and connection steps.

i

PostgreSQL 10 and earlier

The source documentation uses md5 clientcert=1 instead of clientcert=verify-full for PostgreSQL 10 and older.

hostssl all webadmin 0.0.0.0/0 md5 clientcert=1
Configure SSL through pg_hba.conf
Configure SSL-authenticated remote access in pg_hba.conf.

Enable SSL in postgresql.conf

Open postgresql.conf, locate the Security and Authentication section, enable SSL, and specify the root certificate:

ssl = on
ssl_ca_file = 'root.crt'
Enable SSL on PostgreSQL server
Enable SSL and define root.crt in postgresql.conf.

Save the changes and restart PostgreSQL:

sudo service postgresql restart
Restart PostgreSQL server
Restart PostgreSQL to apply the SSL configuration.

Create Client Certificates

Reconnect to the PostgreSQL server through SSH and generate a private key for the client:

openssl genrsa -des3 -out /tmp/postgresql.key 1024
openssl rsa -in /tmp/postgresql.key -out /tmp/postgresql.key
Generate client SSL key
Generate the client private key and remove its pass phrase.

Create a client certificate for the PostgreSQL user and sign it with root.crt and server.key:

openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr \
-subj '/C=US/ST=California/L=PaloAlto/O=Jelastic/CN=webadmin'

openssl x509 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key \
-out /tmp/postgresql.crt -CAcreateserial
!

Common Name must match the PostgreSQL user

The /CN= value in the client certificate must match the database username configured for SSL access, which is webadmin in the source example.

Create and sign PostgreSQL client certificate
Create and sign the client certificate for the PostgreSQL user.

Move these three files to the .postgresql folder on the client machine:

  • postgresql.key
  • postgresql.crt
  • root.crt

If the directory does not exist, create it:

mkdir ~/.postgresql

Optionally, restrict client-key access:

chmod 0400 ~/.postgresql/postgresql.key
PostgreSQL SSL certificates for client
Place the client key, client certificate, and root certificate in the client .postgresql directory.
✓

Clean up temporary files

Remove temporary client keys and certificate files from the database server after they have been transferred to the client.

Establish a Secure Connection through pgAdmin

A public IP address or endpoint must be attached to the PostgreSQL container. The source example uses an endpoint.

Open environment Settings, go to Endpoints, and add a new endpoint.

Attach endpoint to PostgreSQL
Attach an endpoint or use a public IP for remote PostgreSQL SSL access.

In pgAdmin 3, choose New Server Registration. In the Properties tab, specify:

NameAny convenient connection name, for example ssl-to-pgsql.
HostThe public IP or endpoint Access URL without the port.
Port5432 for a public IP, or the endpoint’s Public port.
UsernameThe PostgreSQL user configured for SSL, webadmin by default.
PasswordPassword for the selected PostgreSQL user.
Configure PostgreSQL connection in pgAdmin
Configure the PostgreSQL connection properties in pgAdmin.

Switch to the SSL tab and set SSL to require.

Configure SSL in pgAdmin
Set the pgAdmin SSL option to require.

Click OK. pgAdmin automatically loads the certificate files during the first connection and opens a secure PostgreSQL session.

Expected Result

The PostgreSQL server accepts SSL-authenticated remote connections using the configured server and client certificates. The client connects through a public IP or endpoint, and pgAdmin establishes the database session with SSL set to require.

Important Notes

  • The source tutorial uses self-signed certificates, but a custom SSL certificate can also be used.
  • Keep server.key restricted to the PostgreSQL owner with secure permissions.
  • The client certificate Common Name must match the PostgreSQL username.
  • Use clientcert=verify-full for the documented modern PostgreSQL configuration; the source uses clientcert=1 for PostgreSQL 10 and older.
  • SSL must be enabled in postgresql.conf and PostgreSQL must be restarted afterward.
  • A public IP or endpoint is required for the remote pgAdmin example.
  • Remove temporary certificate files from the database server after transferring them to the client.