PostgreSQL: Windows, Encrypted Connection (SSL)

Post Date: 2020-12-04

PostgreSQL So your running PostgreSQL on windows, maybe even over a network of windows machines and you want to make sure those network connections are encrypted.  You're in the right spot!

We will do this via PowerShell and OpenSSL, read more below.

I'm sure you could do this with a purchased SSL, but we won't do it that way! We use good ol' openssl to generate our certs and that's where we will start.

Step 1: Install OpenSSL

  • Download from here: https://slproweb.com/products/Win32OpenSSL.html
  • If you're on a 64 bit version of windows do the "Win64 OpenSSL" latest (not the Light) download and install, note the install path.
    • For me this is C:\Program Files\OpenSSL-Win64
    • I choose the "The OpenSSL binaries (/bin) directory" option on install, this keeps everything under one directory

Step 2: Generate your root CA (I'm in PowerShell here)

Set-Alias openssl "C:\Program Files\OpenSSL-Win64\bin\openssl.exe"
mkdir Certs
cd Certs
openssl genrsa -out root.key 2048
openssl req -x509 -new -nodes -key root.key -sha256 -days 7300 -out root.crt

Fill in the fields corresponding to your organization

Country Name (2 letter code) [AU]: US
State or Province Name (full name) [Some-State]: Nebraska
Locality Name (eg, city) []: Omaha
Organization Name (eg, company): Method Development LLC
Organizational Unit Name (eg, section) []: Security
Common Name (e.g. server FQDN or YOUR name) []: MethodDevelopment
Email Address []: something@something.com

Now in your C:/Certs you should have 2 files (root.key, root.crt).  These will be used to generate working certificates, if you trust this cert (root.crt) then you trust any children generated from it.

Step 3: Generate Server Certificate (more PowerShell)

Here we are going to generate a certificate for the Postgres Server, for now let's have a common name for it "db.methoddev.com"

Generate CSR (the request)

openssl req -out db_methoddev_com.csr -new -newkey rsa:2048 -nodes -keyout db_methoddev_com.key -subj "/CN=db.methoddev.com"

Fullfill the request with our CA (root.crt), make it last 10 years (3650 days)

openssl x509 -req -in db_methoddev_com.csr -CA root.crt -CAkey root.key -CAcreateserial -out db_methoddev_com.crt -days 3650 -sha256

Now we have all of the cert files we need, should be a total of 6

  • root.crt
  • root.key
  • root.srl
  • db_methoddev_com.crt
  • db_methoddev_com.csr
  • db_methoddev_com.key

You will want to keep these in a secure place, maybe disconnected form the network, say a thumb drive somewhere?

Step 4: Install on Client

Let's assume that your client is a webserver that needs to talk to your Postgres via an encrypted connection.  You need to trust the cert that the Postgres server will be throwing out at you, easiest way to do that is to trust the root cert that you generated in Step 1.

  • Copy the root.crt to your computer
  • Right Click on the file -> install Certificate
  • Local Machine
  • Choose: Place all certificates in the following store -> Browse
  • Select: Trusted Root Certification Authorities -> OK
  • Next -> Finish

This will place your root CA cert into the trusted authorities for the OS.  Therefore any derived certs will automatically be trusted.  You trust yourself don't you?

Step 5: Install on Database Server

You should have 3 files now that would need to be put on the database server.

  • root.csr
  • db_methoddev_com.crt
  • db_methoddev_com.key

I usually just copy these to the PostgreSql Data folder for your  setup (for me that is D:\Data\PgSql\11) , the file we need to change is postgresql.conf file and we then enabled SSL connections with the following.

  • ssl = on
  • ssl_ca_file = 'root.crt'
  • ssl_cert_file = 'db_methoddev_com.crt'
  • ssl_key_file = 'db_methoddev_com.key'

Save the file and restart your PostgreSQL service (via services.msc)

Step 6: Force PostgreSQL SSL Connection

You have now enabled SSL on your PostgreSQL server, but you can now force the clients to use it.  This will vary based on your own infrastructure.  Editing the pg_hba.conf in the PostgreSql Data folder will do the trick.  Below is an example of my setup, 192.168.1.54 would be an example of a client IP address, with hostssl force only SSL connections.  While the hostnossl rejects everything that does not have SSL enabled.

hostnossl all all 0.0.0.0/0 reject
hostssl all all 192.168.1.54/32 scram-sha-256

Feedback? Better Idea? General Comment?