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