![iFixit Tears Down New M4 MacBook Air [Video]](https://www.iclarified.com/images/news/96717/96717/96717-640.jpg)
![Apple Officially Announces Return of 'Ted Lasso' for Fourth Season [Video]](https://www.iclarified.com/images/news/96710/96710/96710-640.jpg)
![[Update: Fix] Chromecast (2nd gen) and Audio can’t Cast in ‘Untrusted’ outage](https://i0.wp.com/9to5google.com/wp-content/uploads/sites/4/2019/08/chromecast_audio_1.jpg?resize=1200%2C628&quality=82&strip=all&ssl=1)
![[The AI Show Episode 139]: The Government Knows AGI Is Coming, Superintelligence Strategy, OpenAI’s $20,000 Per Month Agents & Top 100 Gen AI Apps](https://www.marketingaiinstitute.com/hubfs/ep%20139%20cover-2.png)
![[The AI Show Episode 138]: Introducing GPT-4.5, Claude 3.7 Sonnet, Alexa+, Deep Research Now in ChatGPT Plus & How AI Is Disrupting Writing](https://www.marketingaiinstitute.com/hubfs/ep%20138%20cover.png)
.jpg?#)
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: RDS (Amazon's managed database service) 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 disabled: No encryption at all. Only safe for local networks. allow: Prefers unencrypted connections but will use encryption if the server requires it. prefer: (Default for most clients) Tries to use encryption first but accepts unencrypted connections if necessary. require: Must use encryption. Won't connect without it but doesn't verify certificates. verify_ca: Uses encryption and checks if the server's certificate is signed by a trusted authority. 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: For one-way verification: You need the root CA certificate For 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.
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:
- RDS (Amazon's managed database service)
- 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
-
disabled: No encryption at all. Only safe for local networks. -
allow: Prefers unencrypted connections but will use encryption if the server requires it. -
prefer: (Default for most clients) Tries to use encryption first but accepts unencrypted connections if necessary. -
require: Must use encryption. Won't connect without it but doesn't verify certificates. -
verify_ca: Uses encryption and checks if the server's certificate is signed by a trusted authority. -
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:
- For one-way verification: You need the root CA certificate
- For 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.
