yttrx mastodon remote postgres

Preparing for being able to run the database off host

Currently everything runs on a single host, connecting through a docker bridge, and the postgresql instance just trusts every connection. This is secured by the fact that the database container isn’t bridged to an outside network, so only other containers are able to connect to it. Unfortunately, we want to be able to connect from the outside.

Updating the docker-compose file

First we update the docker compose file to expose connections from the outside:

   db:
     restart: always
     image: postgres:14-alpine
     shm_size: 256mb
     networks:
       - internal_network
+      - external_network
     healthcheck:
       test: ['CMD', 'pg_isready', '-U', 'postgres']
     volumes:
       - ./postgres14:/var/lib/postgresql/data
+    ports:
+      - 5432:5432
 #    environment:
 #      - 'POSTGRES_HOST_AUTH_METHOD=trust'

We see that we’ve added the external_network bridge, and mapped the external port into the container.

Updating the pg_hba.conf file

At the bottom of out pg_hba.conf file we comment out the line:

host all all all trust

and replace it with

host all all 172.18.0.0/16 trust

Why did we select this subnet? First we find the ID of our networks:

docker network ls
NETWORK ID     NAME                    DRIVER    SCOPE
ef294b792bcc   bridge                  bridge    local
527d8402ea0b   host                    host      local
8f97aef86b4a   live_external_network   bridge    local
c2e24131c0f2   live_internal_network   bridge    local
a7cd620112a4   none                    null      local

And we then get the IP ranges of live_internal_network:

docker network inspect -f '\{\{range .IPAM.Config}}\{\{.Subnet}}\{\{end}}' c2e24131c0f2
172.18.0.0/16

This tells us that all internal containers will be using an address in this network, so let’s add that to our pg_hba.conf file:

host all all 172.18.0.0/16 trust

We restart everything, and confirm that our services all work (they do).

Connecting from remote

We try connecting from a remote computer, and see:

psql -h tusky.masto.yttrx.com -U mastodon mastodon_production
psql: error: connection to server at "tusky.masto.yttrx.com" (138.68.5.70), port 5432 failed: FATAL:  no pg_hba.conf entry for host "138.68.239.53", user "mastodon", database "mastodon_production", no encryption

We need to add another entry for our host:

host all mastodon 138.68.239.53/32 md5

and run docker restart live-db-1, and test again:

psql -h tusky.masto.yttrx.com -U mastodon mastodon_production
Password for user mastodon:
psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1), server 14.6)
Type "help" for help.

mastodon_production=>

Great! Let’s check it from inside of our admin connection

Checking if SSL is enabled

docker exec -it live-db-1 psql -U postgres
psql (14.6)
Type "help" for help.

postgres=# select datname, usename, ssl, client_addr
from pg_stat_ssl
inner join pg_stat_activity on pg_stat_ssl.pid = pg_stat_activity.pid;
       datname       | usename  | ssl |  client_addr
---------------------+----------+-----+---------------
 mastodon_production | mastodon | f   | 172.18.0.4
 mastodon_production | mastodon | f   | 172.18.0.4
 mastodon_production | mastodon | f   | 172.18.0.4
 mastodon_production | mastodon | f   | 172.18.0.4
 mastodon_production | mastodon | f   | 138.68.239.53
 mastodon_production | mastodon | f   | 172.18.0.4
 mastodon_production | mastodon | f   | 172.18.0.4
 postgres            | postgres | f   |
(8 rows)

postgres=#

Oh man, SSL isn’t enabled.

Updating postgresql.conf file

We update postgresql.conf and add the line:

ssl = on

But we’ll need to create some certs as well.

Creating SSL certs

openssl genrsa -des3 -out server.key 1024
openssl rsa -in privkey.pem -out server.key
openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj '/C=US/ST=California/L=PaloAlto/O=yttrx/CN=tusky.masto.yttrx.com/[email protected]'

Now we restart and…

postgres=# select datname, usename, ssl, client_addr
from pg_stat_ssl
inner join pg_stat_activity on pg_stat_ssl.pid = pg_stat_activity.pid;
       datname       | usename  | ssl |  client_addr
---------------------+----------+-----+---------------
 mastodon_production | mastodon | f   | 172.18.0.4
 mastodon_production | mastodon | f   | 172.18.0.4
 mastodon_production | mastodon | f   | 172.18.0.4
 mastodon_production | mastodon | f   | 172.18.0.4
 postgres            | postgres | f   |
 mastodon_production | mastodon | t   | 138.68.239.53
(6 rows)

postgres=#

We have an SSL connection with our remote host! Great :)

We’re now ready to start spinning up additional sidekiq workers on other hosts if need be.