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.

Was this content helpful?

White Paper

Free System Design on AWS E-Book

Download this early release of O'Reilly's latest cloud infrastructure e-book: System Design on AWS.

Free System Design on AWS E-Book
Start building today

Dragonfly is fully compatible with the Redis ecosystem and requires no code changes to implement.