Question: How do you configure SSL in PostgreSQL?

Answer

Secure Sockets Layer (SSL) encryption is crucial for protecting data as it moves between the client and the server in PostgreSQL. Here's how to configure SSL on a PostgreSQL server:

Step 1: Obtain SSL Certificates

First, you need an SSL certificate (server.crt) and a corresponding private key (server.key). These can be obtained from a Certificate Authority (CA) or created using tools like OpenSSL. For testing purposes, you can create a self-signed certificate:

openssl req -new -x509 -days 365 -nodes -text -out server.crt -keyout server.key -subj "/CN=your.server.com"
chmod 600 server.key
chown postgres:postgres server.key server.crt
mv server.crt /var/lib/pgsql/data/
mv server.key /var/lib/pgsql/data/

Step 2: Configure PostgreSQL to Use SSL

Modify the PostgreSQL configuration file (postgresql.conf), typically located in /var/lib/pgsql/data/ or /etc/postgresql/[version]/main/. Enable SSL by setting:

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

If you have a CA certificate file and wish to use it, you also need to specify:

ssl_ca_file = 'root.crt'

Step 3: Configure Client Authentication

Edit the pg_hba.conf file to require SSL for the desired connections. For example, to require SSL for all connections:

# TYPE  DATABASE USER ADDRESS METHOD
hostssl all      all  0.0.0.0/0 md5

Step 4: Restart PostgreSQL Server

After making changes, restart the PostgreSQL server to apply the new configuration:

sudo systemctl restart postgresql

Step 5: Verify SSL Connection

You can verify that SSL is working by connecting with psql using the sslmode option:

psql "host=your.server.com port=5432 dbname=mydb user=myuser sslmode=require"

This setup ensures that your PostgreSQL server and its clients communicate over encrypted connections, enhancing the security of your data transmission.