Securing PostgreSQL for rootless containers

Hello Hackers,

When running PostgreSQL in container as rootless and bridged network,
all connection will appear as local connection not matter what their
origin is and pg_hba.conf based allow/deny will not be effective.

One approach is to make PostgreSQL aware of systemd socket activation,
where systemd creates socket FDs and passes them to PostgreSQL. Thus
providing real connection originator. Many services have adopted systemd
socket activation and attached patch enables same for PostgreSQL.

This patch has effect on current use of socket unless systemd socket are
used. Code is also guarded when postgres is not compiled with systemd flag.

Attached patch is based on HEAD.

Here is a sample systemd .socket (
~/.config/systemd/user/PostgreSQL-18.socket )
8<------
[Unit]
Description=PostgreSQL Server Socket
Conflicts=postgresql-18.service

[Socket]
ListenStream=127.0.0.1:5432
ListenStream=192.168.1.100:5432
ListenStream=/tmp/.s.PGSQL.5432
ListenStream=/run/user/1000/.s.PGSQL.5432

[Install]
WantedBy=sockets.target
8<------

Match this name with quadlet .container name, for more details
https://docs.podman.io/en/latest/markdown/podman-systemd.unit.5.html

For local testing one  can also use systemd-socket-activate, for more
details
https://www.freedesktop.org/software/systemd/man/latest/systemd-socket-activate.html

Notes:
1. PostgreSQL config variables must match above sockets and order.
listen_addresses = '127.0.0.1,192.168.100.49'
unix_socket_directories = '/tmp/,/run/user/1000/'

Two TCP sockets and 2 unix sockets and in same order.

2. Since postgres container will be started on first connection,
"database system is starting up" message will be visible only for first
connection but not for subsequent connections.

make check-world passes with or without sytsemd and with socket activation.

--
Kind Regards,
Yogesh Sharma
PostgreSQL, Linux, and Networking Expert
Open Source Enthusiast and Advocate
PostgreSQL Contributors Team @ RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com