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, click on to read more.
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 : firstname.lastname@example.org
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
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.
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.csr'
- 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