Setting Up Encrypted PostgreSQL Connection on AWS EC2

When you need to allow external connections to your database while keeping costs low, encrypting the connection is essential. This guide shows you the simplest way to set up encrypted connections for your development environment, specifically for PostgreSQL running in a container on AWS EC2.

There are 2 main ways to run PostgreSQL on AWS:

1. RDS (Amazon's managed database service)
2. EC2 (virtual machine) with Docker

For cost-conscious developers, using EC2 with Docker is usually cheaper during development.

Why Do We Need Encryption?

When connecting your database to external services, especially SaaS platforms, you often can't restrict access by IP address or domain. In these cases, enabling TLS (Transport Layer Security) encryption helps keep your data safe.

Project Structure

project/
  ├── docker-compose.yml
  └── .docker/
      └── postgres/
          ├── config/
          │   ├── postgresql.conf
          │   └── pg_hba.conf
          └── certs/
              ├── cert.pem
              └── key.pem

PostgreSQL version: 16

Understanding PostgreSQL Client Connection Types

PostgreSQL clients can connect in six different ways

1. disabled: No encryption at all. Only safe for local networks.
2. allow: Prefers unencrypted connections but will use encryption if the server requires it.
3. prefer: (Default for most clients) Tries to use encryption first but accepts unencrypted connections if necessary.
4. require: Must use encryption. Won't connect without it but doesn't verify certificates.
5. verify_ca: Uses encryption and checks if the server's certificate is signed by a trusted authority.
6. verify_full: The most secure option. Checks encryption, certificates, and ensures the server name matches the certificate.

Connection strings look like this:
postgresql://username:password@host:port/database

The most secure options are verify_ca and verify_full, but they require more setup in development:

• one-way verification: You need the root CA certificate
• two-way(mTLS) verification: You need both the client certificate and key in your connection string, the certificate/key MUST signed by root CA and key.

This guide focuses on setting up one-way verification for development environments since it's simpler while still providing good security.

Setting Up the Server(One-Way Verification)

1. Create Certificate and Key

# Set to 100 years for development use
openssl req -x509 -newkey rsa:4096 -sha256 -nodes -keyout key.pem -out cert.pem -days 36500

2. Configure PostgreSQL

Add these lines to postgresql.conf

ssl = on
ssl_cert_file = '/var/lib/postgresql/cert.pem'
ssl_key_file = '/var/lib/postgresql/key.pem'

3. Update Access Rules

Add these lines to pg_hba.conf

hostssl all all all scram-sha-256
hostnossl all all all reject

4. Set Up Docker Compose

services:
  postgres:
    image: postgres:16.3
    ports:
      - '5432:5432'
    environment:
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_USER=postgres
      - POSTGRES_DB=postgres
    volumes:
      - ./.docker/postgres/config/pg_hba.conf:/var/lib/postgresql/data/pg_hba.conf
      - ./.docker/postgres/config/postgresql.conf:/var/lib/postgresql/data/postgresql.conf
      - ./.docker/postgres/certs/cert.pem:/var/lib/postgresql/cert.pem:ro
      - ./.docker/postgres/certs/key.pem:/var/lib/postgresql/key.pem:ro
      - postgres:/var/lib/postgresql/data
    privileged: true

Connecting to Your Database

After setting everything up, just add sslmode=require to your connection string:

DATABASE_URL="postgresql://postgres:postgres@postgres:5432/database?sslmode=require"

That's all you need to establish an encrypted connection to your PostgreSQL database.

Setting Up the Server(mTLS)

For mTLS (mutual TLS), both the server and client need valid certificates signed by a trusted Certificate Authority (CA). Here's how to set it up

1. Create certifications for server and client

Your server.csr CN MUST be your postgreSQL hostname

# 1. Create Root CA
# root.key
openssl genrsa -out root.key 4096
# root.crt
openssl req -x509 -new -nodes -key root.key -sha256 -days 36500 -out root.crt -subj "/C=US/ST=California/L=San Francisco/O=MyCompany/OU=IT/CN=PostgreSQL Root CA/emailAddress=[e-mail]"

# 2. server.crt
# server.key
openssl genrsa -out server.key 4096
# server.csr
openssl req -new -key server.key -out server.csr -subj "/C=US/ST=California/L=San Francisco/O=MyCompany/OU=Database/CN=postgres/emailAddress=[e-mail]"
# sign server.crt with Root CA
openssl x509 -req -in server.csr -CA root.crt -CAkey root.key -CAcreateserial -out server.crt -days 36500 -sha256

# 3. client.crt
# client.key
openssl genrsa -out client.key 4096
# client.csr
openssl req -new -key client.key -out client.csr -subj "/C=US/ST=California/L=San Francisco/O=MyCompany/OU=Developers/CN=client/emailAddress=[e-mail]"
# sign client.crt with Root CA
openssl x509 -req -in client.csr -CA root.crt -CAkey root.key -CAcreateserial -out client.crt -days 36500 -sha256

2. Configure PostgreSQL

Add these lines to postgresql.conf

ssl = on
ssl_cert_file = '/var/lib/postgresql/server.crt'
ssl_key_file = '/var/lib/postgresql/server.key'
ssl_ca_file = '/var/lib/postgresql/root.crt'

3. Update Access Rules

Add these lines to pg_hba.conf

hostssl all all all cert clientcert=verify-full
hostnossl all all all reject

Connecting to Your Database

After setting everything up, just add sslmode=require to your connection string:

DATABASE_URL="postgresql://postgres:postgres@postgres:5432/database?sslmode=verify-full&sslcert=client.crt&sslkey=client.key&sslrootcert=root.crt"