Currently, when we run an ehrql job, we need to give it network access so it can access the db.
We don't want to give it unrestricted access, and the only way to do that is to create a custom docker network, and attach some iptables rules to that, then run the container with that network
This is all a bit complex and brittle, and the failure modes are likely insecure.
Instead, we could leverage systemd socket support and socat to expose the db over a unix socket, which we can then just mount into the ehrql container. There would no need to grant network access container, and we could remove all the docker network and iptables complexity.
Something like this should work:
db-proxy.socket:
Description=TPP db socket
[Socket]
ListenStream=/srv/high_privacy/run/db.socket
SocketUser=opensafely
SocketGroup=opensafely
SocketMode=0660
[Install]
WantedBy=sockets.target
db-proxy.service:
Description=TPP db proxy
Requires=db-proxy.socket
After=db-proxy.socket
[Service]
Type=simple
User=opensafely
Group=opensafely
ExecStart=/usr/bin/socat STDIO TCP:$DB_IP:$DB_PORT
StandardInput=socket
We are moving complexity around here, as we have a forked socat proxying each ehrql db connection. But socat is small, fast and battle tested reliable, so I think it may be a better solution than the current one. And I really like that we control network access via mounting explicit services as sockets, rather than allow but then block of the current approach.
We could also use this approach if we need other network services in containers, e.g. if we want to expose the otel-collector to allow db telemetry, we could just add a new socket proxy and mount that in too: opensafely-core/ehrql#2481
Currently, when we run an ehrql job, we need to give it network access so it can access the db.
We don't want to give it unrestricted access, and the only way to do that is to create a custom docker network, and attach some iptables rules to that, then run the container with that network
This is all a bit complex and brittle, and the failure modes are likely insecure.
Instead, we could leverage systemd socket support and
socatto expose the db over a unix socket, which we can then just mount into the ehrql container. There would no need to grant network access container, and we could remove all the docker network and iptables complexity.Something like this should work:
db-proxy.socket:
db-proxy.service:
We are moving complexity around here, as we have a forked socat proxying each ehrql db connection. But socat is small, fast and battle tested reliable, so I think it may be a better solution than the current one. And I really like that we control network access via mounting explicit services as sockets, rather than allow but then block of the current approach.
We could also use this approach if we need other network services in containers, e.g. if we want to expose the otel-collector to allow db telemetry, we could just add a new socket proxy and mount that in too: opensafely-core/ehrql#2481