Grid Logo
X (Twitter)
Start project

PostgreSQL SSL Certificate Setup

By default, communication to PostgreSQL is unencrypted, which means data transmitted between your application and the database can be intercepted. This guide walks you through configuring SSL certificates for your PostgreSQL database deployment on Grid to secure your database connections.

Why SSL Certificates Matter

SSL certificates protect your database connections from man-in-the-middle (MITM) attacks. Without SSL encryption, attackers can intercept, read, and modify data transmitted between your application and PostgreSQL database.

SSL Certificate and Man-in-the-Middle Attack Protection Diagram

Security Risk

Without SSL encryption, sensitive data like passwords, personal information, and business data can be intercepted by attackers. Always use SSL/TLS encryption for production databases.

1
Step 1: Navigate to PostgreSQL Data Directory

Navigate to the persistent volume where PostgreSQL configuration and data files are stored:

Execution Environment

Run Step 1 in the Grid Shell at console.ongrid.run.

cd /var/lib/postgresql/data/

Persistent Volume

The /var/lib/postgresql/data directory is a persistent volume. All PostgreSQL configuration files, data, and SSL certificates are stored in the pgdata subdirectory, ensuring they persist across container restarts and deployments.

2
Step 2: Configure PostgreSQL for SSL

Add SSL configuration to the PostgreSQL configuration file in the persistent volume:

Execution Environment

Run Step 2 in the Grid Shell at console.ongrid.run.

printf "ssl = on ssl_cert_file = 'server.crt' ssl_key_file = 'server.key' " >> /var/lib/postgresql/data/pgdata/postgresql.conf

Verify the configuration was added correctly:

tail postgresql.conf

3
Step 3: Generate SSL Certificates

Execution Environment

Run Step 3 in your local terminal (outside of the Grid Shell / container). Generate the certificates on your local machine, then copy them to the container in Step 4.

Generate SSL certificates on your local machine. You can create them in any directory:

Create a Certificate Authority (CA) root certificate:

openssl genrsa -out root.key 4096 openssl req -x509 -new -nodes -key root.key \ -sha256 -days 3650 \ -out root.crt

Generate the server private key and certificate signing request:

openssl genrsa -out server.key 2048 openssl req -new -key server.key -out server.csr

Sign the server certificate with the CA:

openssl x509 -req -in server.csr \ -CA root.crt -CAkey root.key \ -CAcreateserial \ -out server.crt \ -days 3650 \ -sha256

Alternative Command

You can also use this single-line command:

openssl x509 -req -in server.csr -CA root.crt -CAkey root.key -CAcreateserial -out server.crt -days 3650 -sha256

4
Step 4: Copy Certificates to Container and Set Permissions

Execution Environment

Run Step 4 in the Grid Shell at console.ongrid.run.

Copy the certificates generated in Step 3 to the persistent volume at /var/lib/postgresql/data/pgdata/ using the cat command with heredoc:

Add the private key:

cat > /var/lib/postgresql/data/pgdata/server.key << 'EOF' -----BEGIN PRIVATE KEY----- [paste your key content here] -----END PRIVATE KEY----- EOF

Add the certificate:

cat > /var/lib/postgresql/data/pgdata/server.crt << 'EOF' -----BEGIN CERTIFICATE----- [paste your certificate content here] -----END CERTIFICATE----- EOF

After copying the files, set proper permissions:

chmod 600 /var/lib/postgresql/data/pgdata/server.keychown postgres:postgres /var/lib/postgresql/data/pgdata/server.keychmod 644 /var/lib/postgresql/data/pgdata/server.crt

Security Note

The private key (server.key) must have restricted permissions (600) and be owned by the postgres user for security.

5
Step 5: Restart PostgreSQL Service

Restart PostgreSQL to apply the SSL configuration changes:

Execution Environment

Run Step 5 in the Grid Shell at console.ongrid.run.

su - postgres /usr/lib/postgresql/15/bin/pg_ctl restart -D /var/lib/postgresql/data/pgdata

Note

The PostgreSQL version path may vary (e.g., /usr/lib/postgresql/15/bin/). Adjust according to your PostgreSQL version.

6
Step 6: Test SSL Connection

Verify that SSL is working by connecting to your database with SSL mode:

Execution Environment

Run Step 6 in your local terminal, using the connection details provided by your deployment.

psql "host=provider.akash-palmito.org port=5432 user=admin dbname=mydb sslmode=require"

Replace the connection parameters with your actual values:

  • host: Your PostgreSQL host address
  • port: PostgreSQL port (default: 5432)
  • user: Database username
  • dbname: Database name
  • sslmode: SSL connection mode (require, verify-ca, or verify-full)

Success Indicators

  • Connection established without errors
  • SSL connection status shown in PostgreSQL logs
  • No SSL-related warnings in connection output

SSL Connection Modes

require

Requires SSL connection but does not verify the certificate. Suitable for testing environments.

verify-ca

Verifies that the server certificate is signed by a trusted CA, but does not verify the hostname.

verify-full

Verifies both the certificate and the hostname. Provides the highest level of security. Recommended for production.