DEV Community

Damir Mahamedov
Damir Mahamedov

Posted on

Setting Up PostgreSQL in Docker on Hetzner with SSL Certificates from Certbot

#postgres #docker #devops #security

Securing a PostgreSQL instance with SSL is essential for protecting your data, especially when deploying on cloud platforms like Hetzner. In this guide, we’ll set up PostgreSQL in Docker, configure SSL using Certbot certificates, and automate certificate renewal using a cron job to ensure minimal downtime for your database.

Prerequisites

Before proceeding, ensure you have:

  1. A Hetzner cloud server running a Linux distribution (e.g., Ubuntu).
  2. Docker and Docker Compose installed on your server.
  3. A domain name (e.g., db.example.com) pointing to your Hetzner server.
  4. Certbot installed for generating SSL certificates.

Hetzner Firewall Configuration

To ensure your server is accessible and secure, you need to configure the Hetzner firewall to open the necessary inbound ports. Here’s what you need to allow:

  1. Port 80 (HTTP): Required for Certbot to verify your domain when issuing SSL certificates.
  2. Port 22 (SSH): Required for SSH access to your server.
  3. Port 5432 (PostgreSQL): Required for PostgreSQL clients to connect to the database.

You also need to allow TCP outbound traffic on any port, which is necessary for network connectivity.

Configuring Hetzner Firewall

  1. Log in to your Hetzner Cloud Console.
  2. Go to the Firewall section and create a new firewall.
  3. Add the following inbound rules:
    • HTTP (Port 80): To allow Certbot to perform domain validation.
    • SSH (Port 22): To allow SSH access to your server.
    • PostgreSQL (Port 5432): To allow connections to your PostgreSQL database (from your IP or trusted IPs).
  4. Add the following outbound rule:
    • TCP (any port): To allow your server to communicate freely over the internet, which is needed for Certbot renewal and other outbound communications.
  5. Assign the firewall to your server.

Why Use SSL with PostgreSQL?

SSL (Secure Sockets Layer) ensures that data transmitted between PostgreSQL and its clients is encrypted. It protects against potential man-in-the-middle attacks and data theft by encrypting the communication channel.

Step 1: Install Docker and Docker Compose

If Docker and Docker Compose aren’t already installed on your Hetzner server, follow these steps.

Installing Docker

  • SSH into your Hetzner server.
  • Install Docker: 
sudo apt update
sudo apt install docker.io -y
Enter fullscreen mode Exit fullscreen mode
  • Enable Docker to start on boot and verify the installation: 
sudo systemctl start docker
sudo systemctl enable docker
Enter fullscreen mode Exit fullscreen mode

Installing Docker Compose

  • Install Docker Compose: 
sudo apt install docker-compose -y
Enter fullscreen mode Exit fullscreen mode

Step 2: Install Certbot and Generate SSL Certificates

Certbot simplifies generating SSL certificates from Let’s Encrypt. Here’s how to install and generate SSL certificates:

  • Install Certbot: 
sudo apt install certbot -y
Enter fullscreen mode Exit fullscreen mode
  • Generate SSL certificates for your domain: 
sudo certbot certonly --standalone -d db.example.com
Enter fullscreen mode Exit fullscreen mode

The certificates will be saved in /etc/letsencrypt/live/db.example.com/. The important files for PostgreSQL are:

  • fullchain.pem (public certificate)
  • privkey.pem (private key)

Automate Certificate Renewal Using Cron

Let’s Encrypt certificates expire every 90 days, so it’s crucial to set up automated certificate renewal. Certbot can handle this automatically using cron, ensuring that your SSL certificates are renewed without manual intervention.

  • Open the cron file: 
sudo crontab -e
Enter fullscreen mode Exit fullscreen mode
  • Add the following cron job to renew the certificates and restart the PostgreSQL container: 
0 3 * * * /usr/bin/certbot renew --quiet && docker-compose -f /path/to/your/docker-compose.yml restart postgres
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • 0 3 * * *: This cron job will run every day at 3:00 AM. Adjust the time as needed.
  • /usr/bin/certbot renew --quiet: This runs Certbot’s renewal process in quiet mode, meaning it will renew certificates only if they are about to expire (i.e., within 30 days of expiration).
  • docker-compose -f /path/to/your/docker-compose.yml restart postgres: After renewal, this command restarts the PostgreSQL container to apply the new SSL certificates.

Save and exit the cron file. Now, Certbot will automatically renew the certificates, and PostgreSQL will restart to load the new certificates with no downtime for your application.

Step 3: Configure Docker Compose for PostgreSQL

Now we’ll configure PostgreSQL using Docker Compose, mounting SSL certificates, and setting up environment variables.

The docker-compose.yml File

Create a docker-compose.yml file to define the PostgreSQL service:

nano docker-compose.yml
Enter fullscreen mode Exit fullscreen mode

Here’s the configuration:

services:
  postgres:
    image: postgres:16
    container_name: postgres_db
    restart: always
    env_file:
      - ./.env
    environment:
      - POSTGRES_USER=$DB_USERNAME
      - POSTGRES_PASSWORD=$DB_PASSWORD
      - POSTGRES_DB=$DB_NAME
    volumes:
      - ./pg_data:/var/lib/postgresql/data
      - ./pg_hba.conf:/var/lib/postgresql/data/pg_hba.conf
      - /etc/letsencrypt/live/db.example.com/fullchain.pem:/var/lib/postgresql/fullchain.pem
        - /etc/letsencrypt/live/db.example.com/privkey.pem:/var/lib/postgresql/privkey.pem
    networks:
      - db-net
    ports:
      - "5432:5432"
    command: >
      postgres -c shared_buffers=2GB
               -c work_mem=16MB
               -c maintenance_work_mem=256MB
               -c effective_cache_size=6GB
               -c ssl=on
               -c ssl_cert_file='/var/lib/postgresql/fullchain.pem'
               -c ssl_key_file='/var/lib/postgresql/privkey.pem'
               -c listen_addresses='*'

volumes:
  pg_data:

networks:
  db-net:
    driver: bridge
Enter fullscreen mode Exit fullscreen mode

Explanation of Memory and Performance Options

  1. -c shared_buffers=2GB
    The shared_buffers setting determines how much memory PostgreSQL will use to cache frequently accessed data. Setting this higher can significantly improve performance by reducing disk I/O since the data will be stored in memory instead of being read from disk repeatedly.

    Default: The default value is usually very low, often 128MB or 256MB, which is insufficient for production systems.

    Recommended Setting: A general recommendation is to allocate about 25–40% of the total available RAM to shared_buffers. In this example, 2GB has been allocated, which is suitable for systems with at least 8GB of RAM.

  2. -c work_mem=16MB
    The work_mem setting specifies the amount of memory allocated for each query operation that requires memory (e.g., sorting, hash tables). Each connection and each query can use its own chunk of work_mem.

    Default: Typically around 4MB.

    Recommended Setting: Increasing this to 16MB allows queries that involve sorting or creating hash tables to perform faster by reducing the need to write temporary data to disk.

    Note: Be cautious with this setting, as high values combined with many concurrent users can lead to excessive memory usage.

  3. -c maintenance_work_mem=256MB
    The maintenance_work_mem setting controls the memory used for maintenance tasks such as VACUUM, CREATE INDEX, and ALTER TABLE. These operations can be memory-intensive, and allocating more memory here can speed up these operations.

    Default: Often set around 64MB.

    Recommended Setting: For production environments, especially those with large tables, increasing this to 256MB helps PostgreSQL complete maintenance tasks faster.

  4. -c effective_cache_size=6GB
    The effective_cache_size parameter is a guideline that PostgreSQL uses to estimate the amount of memory available for disk caching by the operating system. This setting doesn’t allocate memory but helps PostgreSQL make decisions about which queries to optimize and which indexes to use.

    Default: Often set to 4GB.

    Recommended Setting: The value should generally be about 50–75% of the system’s total memory. In this case, with 6GB, PostgreSQL will assume there is enough memory to cache large chunks of data, leading to more efficient queries.

The .env File

Create a .env file in the same directory as your docker-compose.yml:

nano .env
Enter fullscreen mode Exit fullscreen mode

Add the following environment variables:

DB_USERNAME=your_db_user
DB_PASSWORD=your_secure_password
DB_NAME=your_database_name
Enter fullscreen mode Exit fullscreen mode

Step 4: Configure pg_hba.conf to Restrict Access by IP Address

The pg_hba.conf file is used to control access to PostgreSQL. In this step, we’ll configure it to only allow connections from a specific IP address (99.99.99.99) using SSL and block all other IP addresses.

Create the pg_hba.conf file:

nano pg_hba.conf
Enter fullscreen mode Exit fullscreen mode

Add the following configuration:

# Allow only the specific IP address 99.99.99.99 to connect via SSL
hostssl    all             all             99.99.99.99/32      scram-sha-256

# Block all other IP addresses
hostssl    all             all             0.0.0.0/0           reject
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • hostssl: Enforces SSL for all connections.
  • 99.99.99.99/32: Allows only connections from the IP address 99.99.99.99.
  • scram-sha-256: Uses SCRAM-SHA-256 authentication for secure password hashing. Please don’t use md5.
  • 0.0.0.0/0: Blocks all other IP addresses from accessing the database.

Step 5: Adjust File Permissions for SSL Certificates

PostgreSQL requires strict permissions on the private key file (privkey.pem). It must be owned by the postgres user inside the Docker container, and the permissions must be set to 0600 (read/write only for the owner).

Set the appropriate permissions on the host machine:

sudo chown 999:999 /etc/letsencrypt/live/db.goachievo.com/privkey.pem
sudo chmod 600 /etc/letsencrypt/live/db.goachievo.com/privkey.pem
Enter fullscreen mode Exit fullscreen mode

PostgreSQL also needs read access to the public certificate file:

sudo chown 999:999 /etc/letsencrypt/live/db.goachievo.com/fullchain.pem
sudo chmod 644 /etc/letsencrypt/live/db.goachievo.com/fullchain.pem
Enter fullscreen mode Exit fullscreen mode

Why 999?
Inside the Docker container, the postgres user typically has the UID and GID of 999. This ensures that PostgreSQL can read the private key.

Step 6: Start PostgreSQL

With everything set up, start the PostgreSQL container:

docker-compose up -d
Enter fullscreen mode Exit fullscreen mode

Docker will pull the PostgreSQL image, create the necessary volumes, and start the database with SSL enabled and access restricted to the specified IP address.

Step 7: Verify SSL and Connection Restrictions

Once PostgreSQL is running, verify that SSL is enabled and that the connection restrictions are in place.

Connect to the PostgreSQL instance and run:

SHOW ssl;
Enter fullscreen mode Exit fullscreen mode

It should return on, indicating that SSL is enabled.

Check the PostgreSQL logs to verify SSL connections and confirm that only the allowed IP address is connecting:
docker logs postgres_db

  1. Test the connection from the allowed IP (99.99.99.99) and ensure that connections from other IP addresses are blocked.

Conclusion

In this article, we’ve successfully set up PostgreSQL in Docker with SSL certificates from Certbot, restricted access to a specific IP address using pg_hba.conf, and ensured secure encrypted connections. By using cron for automated certificate renewal, you’ve also ensured that your SSL certificates remain up to date with minimal manual intervention.

Feel free to share your thoughts and improvements in the comments section below!

If you enjoyed this article or found these tools useful, make sure to follow me on Medium for more insights and tips on coding and development. I regularly share helpful content to make your coding journey smoother.

Follow me on X (Twitter), where I share more interesting thoughts, updates, and discussions about programming and tech! Don’t miss out — click those follow buttons.

You can also follow me on LinkedIn for professional insights, updates on my latest projects, and discussions about coding, tech trends, and more. Don’t miss out on valuable content that can help you level up your development skills — let’s connect!

Top comments (0)