Blog

In the previous blog, we explored the importance of securing data at rest, focusing on encryption methods and best practices to protect sensitive information stored within PostgreSQL databases.

As we continue our journey toward comprehensive data security, we now turn our attention to securing data in transit. Ensuring that data transmitted between clients and the database server is protected against interception and tampering is equally vital.

In this blog, we will discuss how to implement SSL (Secure Sockets Layer) for PostgreSQL, providing a robust mechanism to encrypt communication channels and enhance overall data security.

Assuming 192.168.113.133 is server and 192.168.113.137 is client

Step 1: Generate the Certificate Authority (CA) Private Key and Certificate

# Create directory to store all Certificates initially
mkdir -p certs
cd certs

# Generate the CA private key
openssl genpkey -algorithm RSA -out ca.key

# Generate the CA certificate
openssl req -new -x509 -days 365 -key ca.key -out ca.crt -subj "/CN=MyCA"

Step 2: Create the Server and Client Certificates

#Create the Server Certificate
openssl genpkey -algorithm RSA -out server.key
openssl req -new -key server.key -out server.csr -subj "/CN=192.168.113.133"
openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out server.crt -days 365
chmod 600 server.key

#Create the Client Certificate
openssl genpkey -algorithm RSA -out client.key
openssl req -new -key client.key -out client.csr -subj "/CN=postgres"
openssl x509 -req -in client.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out client.crt -days 365
chmod 600 client.key

Step 3: Configure the Server

## copy files to respective locations

cp server.crt server.key ca.crt /u01/pgsql/17
chmod 600 /u01/pgsql/17/server.key

## Edit postgresql.conf 

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

## Edit pg_hba.conf

hostssl    all    all    0.0.0.0/0    md5 clientcert=verify-full
hostssl    all    all    0.0.0.0/0    cert

Step 4: Copy relevant files to Client Side

## Make changes in client side
mkdir ~/.postgresql
scp client.crt client.key ca.crt 192.168.113.137:/home/postgres/.postgresql/
chmod 600 /home/postgres/.postgresql/client.key

Step 5: Login and check

It should fail because, postgresql will look out for postgresql.key rather than client.key, rename the files and try again or explicitly provide key name

mv client.key postgresql.key
mv client.crt postgresql.crt

(or)

psql "host=192.168.113.133 dbname=postgres user=postgres sslmode=verify-full sslcert=postgresql.crt sslkey=postgresql.key sslrootcert=root.crt"

Explore more

Create another user and test with the below pg_hab.conf

## check for auser from client for
1. hostssl all all 0.0.0.0/0 md5 clientcert=verify-full
2. hostssl all all 0.0.0.0/0 md5 clientcert=verify-ca
3. hostssl all all 0.0.0.0/0 cert

Conclusion

Securing data in transit is an essential part of protecting sensitive information. By implementing SSL, we create a robust security layer that safeguards data as it travels over networks. In our next blog, we will delve deeper into more advanced security measures and best practices to further enhance your database security.