How to Turn On SSL for a PostgreSQL Database Server

What is this about?

PostgreSQL is a database program. It stores information in tables. By default, data between your app and the database may travel as plain text. SSL scrambles that data so strangers cannot read it. It is like putting a letter in a locked box.

Use this when your app and database are on different servers, or when you connect from home. Paths below are common on Ubuntu or Debian. They may differ on your system.

Warning: A wrong setting can stop PostgreSQL from starting. Back up the config files before you edit them.

Steps

1. Log in to your server with SSH as root.
2. Make a certificate and key. For testing, a self-signed pair works:

openssl req -new -x509 -days 365 -nodes -text -out server.crt -keyout server.key -subj "/CN=db.example.com"

This creates a certificate (server.crt) and a private key (server.key). For real use, a certificate from a trusted company is better. Change the name to your own host name.

3. Lock down the key, and give it to the postgres user:

chmod 600 server.key
chown postgres:postgres server.crt server.key

This lets only the database user read the key. PostgreSQL refuses a key that others can read.

4. Move both files into the data folder, or note their paths. Find the config file with sudo -u postgres psql -c "SHOW config_file;".
5. Open postgresql.conf and set:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

These lines turn SSL on and point to your files.

6. Open pg_hba.conf. This file decides who may connect. Use hostssl instead of host to force SSL:

hostssl all all 0.0.0.0/0 scram-sha-256

This accepts remote logins only over SSL. Use a narrower address range if you can.

7. Restart PostgreSQL:

systemctl restart postgresql

This applies the new settings.

8. Test it from another machine:

psql "host=db.example.com dbname=mydb user=myuser sslmode=require"

After login, the client shows a line saying SSL connection is on.

Quick recap

• SSL scrambles data between your app and PostgreSQL.
• Create a certificate and key, with safe permissions.
• Set ssl = on and use hostssl in pg_hba.conf.
• Restart and test with sslmode=require.