This is Google's cache of https://blog.ls42.de/post/postgres-ssl-with-caddy/. It is a snapshot of the page as it appeared on 14 Dec 2018 09:51:16 GMT. The current page could have changed in the meantime. Learn more.
Full versionText-only versionView source
Tip: To quickly find your search term on this page, press Ctrl+F or ⌘-F (Mac) and use the find bar.
PostgreSQL with Let's Encrypt
Home
Posts
About me
Disclosure | Impressum
PostgreSQL with Let's Encrypt February 12, 2018

Summary
I decided to play around with PostgreSQL and created a setup where it is running inside Docker and Caddy is taking care of generating Let’s Encrypt certificates. I’ll show the necessary steps and post the configuration snippets here.

Lets assume PostgreSQL will be reachable via the FQDN db.example.com. I also assume that you have Caddy configured and running on your linux server.

Caddy Configuration
Caddy is usually delivering websites. What it also does, is automatically generating SSL certificates via the ACME protocol. In Layman’s terms: It asks the Let’s Encrypt institution to give us a SSL certificate that is considered as secure by most current operation systems [citation needed].

But we can use Caddy for this feature alone: Just get the certificate. It still listens on the ports 80 and 443, but always delivers a 404 error if not further configured. In my current webserver setup this is particularly useful, because it is running already and I know how it works. I don’t need to have another tool like Certbot installed.

Given DNS is already configured, you have to tell Caddy to handle the ACME request. This is the corresponding /etc/caddy/Caddyfile:

[...]
db.example.com
[...]
That’s it so far. Restart Caddy, I do it with sudo systemctl caddy restart. Caddy might take a while, but eventually will have the certificates generated and be listening on port 80 and 443 for the hostname db.example.com. When you access those, you’ll receive a 404 error. That’s fine, because were not interested in HTTP(S) traffic at all, we want to secure port 5432, the default port for the PostgreSQL daemon.

The default location for Caddy certificates is /etc/ssl/caddy. Given our hostname of db.example.com they relevant files can be located here:

/etc/ssl/caddy/acme/acme-v01.api.letsencrypt.org/sites/db.example.com/db.example.com.crt
/etc/ssl/caddy/acme/acme-v01.api.letsencrypt.org/sites/db.example.com/db.example.com.key
Configuration of Caddy has concluded.

Docker Configuration
Let me start with a version of a docker-compose.yml that I use for the PostgreSQL container:

db:
  image: 'postgres:alpine'
  restart: always
  hostname: 'db.example.com'
  container_name: 'db'
  environment:
    POSTGRES_PASSWORD: "hey-ho-lets-go"
  ports:
    - 0.0.0.0:5432:5432
  volumes:
    - db:/var/lib/postgresql/data
    - /etc/ssl/caddy/acme/acme-v01.api.letsencrypt.org/sites/db.example.com:/var/lib/postgresql/data/ssl
This tells docker to do the following (brief version):

Create a new container from the postgres:alpine image and name it db
Create a volume named db and mount it inside the container at /var/lib/postgresql/data
Mount a host directory inside the container. This is the location of our SSL files and it will be mounted inside the postgres data directory
Save the file somewhere and run docker-compose up -d in that directory. You can see the new volume and container with the following commands:

# docker volume ls
DRIVER              VOLUME NAME
local               db
# docker container ls
NAMES                  IMAGE                         STATUS                CONTAINER ID
db                     postgres:alpine               Up About an hour      ca111ecef743
PostgreSQL Configuration
Enter the container and change a few settings.

# docker exec -ti -u postgres -w /var/lib/postgresql/data db /bin/sh
$ echo "host    all             all       0.0.0.0/0               trust" >> pg_hba.conf
$ echo "ssl = on" >> postgresql.conf
$ echo "ssl_cert_file = 'ssl/db.example.com.crt'" >> postgresql.conf
$ echo "ssl_key_file = 'ssl/db.example.com.key'" >> postgresql.conf
Lines starting with $ are inside the container. We did the following:

Enter the container as the postgres user
Tell PostgreSQL that connection from all interfaces for all users are okay. You should consider setting up a dedicated user for external access and change the second all in that line to that users name
Tell PostgreSQL to enable SSL and where it can find the certificate and and the key
After that leave the container again and restart it:

$ exit
# docker restart db
If all went well you should now be able to connect to your PostgreSQL database from any host, with a valid SSL certificate. I used Postico and it worked flawlessly.

Conclusion
Using Docker and Caddy I’m able to run a secure PostgreSQL installation and connect from anywhere without worrying (too much) about security issues. I consider the times where I have to use snakeoil certificates over.

If you want to talk about this drop me an e-mail or leave a comment on lobste.rs.

Thanks!

© All rights reserved. Powered by Hugo and Minimal