Postgresl-Enable SSL

Secure connections for postgresql 17 on ubuntu noble
note: This SSL Cert expires after 10 years

• get config file location:

postgres=# show config_file;
-----------------------------------------
 /etc/postgresql/17/main/postgresql.conf

• get data directory location:

postgres=# show data_directory;
-----------------------------
 /var/lib/postgresql/17/main

This location is where you’ll create the following files:

  server.key - This is the private key file
  server.cert - This is the server certificate file
  root.cert - If you are not a CA, this is an [insecure workaround](https://stackoverflow.com/questions/74480918/is-a-root-certificate-ca-required-on-the-client-side-to-establish-an-https-conne)

$ cd ${data_directory}

• generate a 2048-bit RSA private key with AES encryption as follows.
  

openssl req  -nodes -new -x509 -days 3650 -keyout server.key -out  server.cert

sample output follows:

  Country Name (2 letter code) [AU]:US
  State or Province Name (full name) [Some-State]:CA
  Locality Name (eg, city) []:RWC
  Organization Name (eg, company) [Internet Widgits Pty Ltd]:amos
  Organizational Unit Name (eg, section) []:local
  Common Name (e.g. server FQDN or YOUR name) []:amos
  Email Address []:[email protected]

• replicate server to root certificate:

cp server.cert root.cert

• Configure PostgreSQL to use SSL

  vi ${DATA_DIRECTORY}/postgresql.conf

  # verify listen_addresses = '*' (not set to 'localhost')
  ssl_ca_file = 'root.cert'
  ssl_cert_file = 'server.cert'
  ssl_key_file = 'server.key'
  ssl_prefer_server_ciphers = on

• update ${CONFIG_DIR}/pg_hba.conf to allow SSL

  hostssl  all         all          0.0.0.0/0      md5

• restart to allow changes

  systemctl restart postgresql

• verify connection via query

  select * from pg_stat_ssl ;

• update client connections to prefer ssl:

  # Connection details
  host = '${hostname}'
  port = '5432'
  database = '${dbname}'
  user = 'demo'
  password = 'test123'
  sslmode = 'require'
  conn = demodb.connect(host=host, port=port, database=database,
    user=user, password=password, sslmode=sslmode)

ref:
“official” doc
ssl_cihers allowable switches