Skip to content

Instantly share code, notes, and snippets.

@mrw34
Last active March 26, 2024 00:24
Show Gist options
  • Star 76 You must be signed in to star a gist
  • Fork 15 You must be signed in to fork a gist
  • Save mrw34/c97bb03ea1054afb551886ffc8b63c3b to your computer and use it in GitHub Desktop.
Save mrw34/c97bb03ea1054afb551886ffc8b63c3b to your computer and use it in GitHub Desktop.
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
@mrw34
Copy link
Author

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
Copy link

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
Copy link
Author

mrw34 commented May 9, 2020

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

@ipmb
Copy link

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
Copy link

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.

@tashian
Copy link

tashian commented Nov 2, 2021

Just wanted to share an alternative, in case you are wanting to run TLS on PostgreSQL in production with an internal PKI. I've created a self-contained Docker image that does full certificate automation (including automated renewal) for PostgreSQL 14. See smallstep/docker-tls.

@suikast42
Copy link

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.

look at https://github.com/cloudflare/cfssl ;-)

@suikast42
Copy link

I. I've created a self-contained Docker image that does full certificate automation (including automated renewal) for PostgreSQL 14. S

That's looking very well. As I unterstand, this setup needs a external PKI server, right ?

@tashian
Copy link

tashian commented Nov 11, 2021

@suikast42 Yes, that's true. The self-signed cert that you created in this script will expire after a month. The CA server can renew the certificate. For the CA, you can set up smallstep/certificates open source CA server, or sign up for a hosted CA (it's free for small homelabs).

@suikast42
Copy link

@suikast42 Yes, that's true. The self-signed cert that you created in this script will expire after a month. The CA server can renew the certificate. For the CA, you can set up smallstep/certificates open source CA server, or sign up for a hosted CA (it's free for small homelabs).

Look like cert-manager outside of k8s. Looking good.

@codeninja
Copy link

@Istellway you are a savior. Thank you!

@slidenerd
Copy link

@MahmoudMousaHamad
Copy link

Are the permissions different when using debian-based image?

@Aaqu
Copy link

Aaqu commented Jan 27, 2024

Are the permissions different when using debian-based image?

you found solution?

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