Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Enabling SSL for PostgreSQL in Docker
#!/bin/bash
set -euo pipefail
openssl req -new -text -passout pass:abcd -subj /CN=localhost -out server.req -keyout privkey.pem
openssl rsa -in privkey.pem -passin pass:abcd -out server.key
openssl req -x509 -in server.req -text -key server.key -out server.crt
chmod 600 server.key
test $(uname -s) == Linux && chown 70 server.key
docker run -d --name postgres -e POSTGRES_HOST_AUTH_METHOD=trust -v "$(pwd)/server.crt:/var/lib/postgresql/server.crt:ro" -v "$(pwd)/server.key:/var/lib/postgresql/server.key:ro" postgres:12-alpine -c ssl=on -c ssl_cert_file=/var/lib/postgresql/server.crt -c ssl_key_file=/var/lib/postgresql/server.key
sleep 1
docker run --rm -it --link postgres postgres:12-alpine psql -h postgres -U postgres
@andrewtheis

This comment has been minimized.

Copy link

@andrewtheis andrewtheis commented May 9, 2018

Does this still work with latest docker / Postgres? I'm using a modified version of line 9 but the container doesn't seem to be booting up.

@jillesvangurp

This comment has been minimized.

Copy link

@jillesvangurp jillesvangurp commented Aug 14, 2018

I spent an hour getting this working. I ended up moving the keys outside the data directory. If I don't do this, postgres fails to launch because the data dir is not empty. I'm guessing it is attempting to run initdb on startup. Works with 10.5.

I'm using docker-compose:

  bridge_db:
    image: postgres:alpine
    command: -c ssl=on -c ssl_cert_file=/var/lib/postgresql/server.crt -c ssl_key_file=/var/lib/postgresql/server.key
    environment:
      POSTGRES_DB: mydb
      POSTGRES_USER: user
      POSTGRES_PASSWORD: secret
    volumes:
      - ./ca/server.crt:/var/lib/postgresql/server.crt
      - ./ca/server.key:/var/lib/postgresql/server.key
@tlhunter

This comment has been minimized.

Copy link

@tlhunter tlhunter commented Dec 26, 2018

mkdir -p postgres

docker run -d \
  --name postgres \
  -v $PWD/postgres:/var/lib/postgresql/data \
  -v $PWD/server.crt:/var/lib/postgresql/server.crt \
  -v $PWD/server.key:/var/lib/postgresql/server.key \
  postgres:9-alpine \
  -c ssl=on \
  -c ssl_cert_file=/var/lib/postgresql/server.crt \
  -c ssl_key_file=/var/lib/postgresql/server.key
@mrw34

This comment has been minimized.

Copy link
Owner Author

@mrw34 mrw34 commented Feb 12, 2019

Updated for PostgreSQL 11

@naren-ambati

This comment has been minimized.

Copy link

@naren-ambati naren-ambati commented Jul 31, 2019

It was so helpful.

I'm not expert in DB and I have a QQ. I am using Postgres 9.6. After executing above commands I went into my postgres container and saw the postgresql.conf is having "ssl=off" but in postmaster.opts I can see all the variables I passed ie; certs and ssl=on.
Postmaster will make my container ssl enabled even though postgresql.conf has ssl=off?
Is my postgres container still ssl enabled?

@mrw34

This comment has been minimized.

Copy link
Owner Author

@mrw34 mrw34 commented Jul 31, 2019

@naren-ambati: To verify that your container is SSL-enabled you try connecting with sslmode=require (see here).

@naren-ambati

This comment has been minimized.

Copy link

@naren-ambati naren-ambati commented Jul 31, 2019

Forgot to mention in my last comment, I was using ssl=off, I'm getting below errors when I'm using ssl=on.

FATAL:  could not load server certificate file "/var/lib/postgresql/server.test.chain.pem": no start line
LOG:  database system is shut down

There shouldn't be any issues with cert as they were automatically created. Also, just to let you know I can't change my owner and group as I don't have enough Permissions.

For your Comment on making sure ssl enabled, I went inside container and tried that but it is saying pqsl command not found. I'm getting all weird issues.

@yegor256

This comment has been minimized.

Copy link

@yegor256 yegor256 commented Oct 22, 2019

I'm getting "private key file "/var/lib/postgresql/server.key" must be owned by the database user or root"

This helped: https://stackoverflow.com/questions/55072221/deploying-postgresql-docker-with-ssl-certificate-and-key-with-volumes

@mrw34

This comment has been minimized.

Copy link
Owner Author

@mrw34 mrw34 commented Nov 4, 2019

I'm getting "private key file "/var/lib/postgresql/server.key" must be owned by the database user or root"

This helped: https://stackoverflow.com/questions/55072221/deploying-postgresql-docker-with-ssl-certificate-and-key-with-volumes

Thanks @yegor256 - fixed. Was due to differing file permission propagation in Docker on macOS vs Linux.

@epiccoolguy

This comment has been minimized.

Copy link

@epiccoolguy epiccoolguy commented May 9, 2020

Nice gist! Currently the postgres image will exit by default if there's no password set: Error: Database is uninitialized and superuser password is not specified.

Adding -e POSTGRES_PASSWORD=mysecretpassword to the postgres command and -e PGPASSWORD=mysecretpassword to the psql command solves this.

Here's the commit that changed this behaviour: docker-library/postgres@42ce743

@mrw34

This comment has been minimized.

Copy link
Owner Author

@mrw34 mrw34 commented May 9, 2020

@epiccoolguy: Thanks! Fixed by modifying POSTGRES_HOST_AUTH_METHOD (though obviously not recommended in production...).

@ipmb

This comment has been minimized.

Copy link

@ipmb ipmb commented Jan 21, 2021

The vanilla/debian image already include a self-signed SSL cert, so you can do this:

docker run \
  --rm \
  -e POSTGRES_PASSWORD=password \
  postgres:12 \
  -c ssl=on \
  -c ssl_cert_file=/etc/ssl/certs/ssl-cert-snakeoil.pem \
  -c ssl_key_file=/etc/ssl/private/ssl-cert-snakeoil.key
@lstellway

This comment has been minimized.

Copy link

@lstellway lstellway commented Mar 9, 2021

Thank you all for the reference 🙏
Worked great for me.

One other cosmetic detail for a docker-compose.yml configuration is to break the command into multiple lines for better legibility.

version: "3.8"
services:
  postgres:
    command: >
      -c ssl=on 
      -c ssl_cert_file=/var/lib/postgresql/server.crt 
      -c ssl_key_file=/var/lib/postgresql/server.key
...

I also have a bash script that is a wrapper around OpenSSL and makes it easy to generate self-signed certificates (and authorities). The directions here are great, but just thought it could be useful for some others.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment