DEV Community

Pacharapol Withayasakpunt
Pacharapol Withayasakpunt

Posted on

PostgreSQL does have SSL (asymmetric key files), but I couldn't get it to work

I don't want to lock it by merely password and username.

Here is what I adapted from how-to-secure-your-postgresql-database

First, SSH into remote server (Linode, in my case). Then,

$ sudo su - postgres
$ echo $PWD
Enter fullscreen mode Exit fullscreen mode
  1. Create a certificate signing request (CSR) and a key file.
$ openssl genrsa -out rootCA.key 1024
$ openssl req -x509 -new -key rootCA.key -days 365 -out rootCA.crt -subj '/C=XX/L=Default City/O=Default Company Ltd/CN=root'
Enter fullscreen mode Exit fullscreen mode
  1. Create a server certificate signed by the root certificate authority.
$ openssl genrsa -out server.key 1024
$ openssl req -new -key server.key -out server.csr -subj '/C=XX/L=Default City/O=Default Company Ltd/CN=polv.cc'
$ openssl x509 -req -in server.csr -CA rootCA.crt -CAkey rootCA.key -CAcreateserial -out server.crt -days 365
Enter fullscreen mode Exit fullscreen mode

(Note that CN in the second line above should be the server name for server validation.)

  1. Create a client certificate signed by the root certificate authority.
$ openssl genrsa -out postgresql.key 1024
$ openssl req -new -key postgresql.key -out postgresql.csr -subj '/C=XX/L=Default City/O=Default Company Ltd/CN=postgres'
$ openssl x509 -req -in postgresql.csr -CA rootCA.crt -CAkey rootCA.key -CAcreateserial -out postgresql.crt -days 365
Enter fullscreen mode Exit fullscreen mode
  1. Copy the server certification file (server.crt), server private key (server.key), and root certification authority (rootCA.crt) to either the PostgreSQL data directory or a secured location accessible by PostgreSQL superuser (postgres). Once copied, allow read-only privileges to PostgreSQL users.
$ chmod 0400 server.crt server.key rootCA.crt
Enter fullscreen mode Exit fullscreen mode
  1. Set the appropriate parameters in PostgreSQL to enable SSL mode.
$ psql
ALTER SYSTEM SET ssl TO 'ON';
ALTER SYSTEM SET ssl_ca_file TO '/var/lib/postgresql/rootCA.crt';
ALTER SYSTEM SET ssl_cert_file TO '/var/lib/postgresql/server.crt';
ALTER SYSTEM SET ssl_key_file TO '/var/lib/postgresql/server.key';
Enter fullscreen mode Exit fullscreen mode
  1. OpenSSL supports a wide range of ciphers. You will want to choose the cipher that suits your organizational standards. Once you have chosen the cipher you wish to use, set the following parameter in PostgreSQL.
psql
ALTER SYSTEM SET ssl_ciphers TO 'HIGH:MEDIUM:+3DES:!aNULL'; 
Enter fullscreen mode Exit fullscreen mode

Default cipher is: HIGH:MEDIUM:+3DES:!aNULL

Then,

$ exit
$ sudo systemctl restart postgresql@12-main.service 
Enter fullscreen mode Exit fullscreen mode

However, this command, as well as subsequent psql crashes, with ee key too small...

Please help me on PostgreSQL on this security! Or, is it not important?

Top comments (0)