Thread

1. Securing PostgreSQL for rootless containers

   Yogesh Sharma <yogesh.sharma@catprosystems.com> — 2025-02-24T19:51:33Z

   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
   

2. Re: Securing PostgreSQL for rootless containers

   Yogesh Sharma <yogesh.sharma@catprosystems.com> — 2025-02-25T21:02:59Z

   On 2/24/25 14:51, Yogesh Sharma wrote:
   >
   > 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.
   
   I meant to say
   
   
   This patch has **no** effect on current use of socket unless systemd 
   socket are used. Code is also guarded when postgres is not compiled with 
   systemd flag.
   
   
   -- 
   Kind Regards,
   Yogesh Sharma
   Open Source Enthusiast and Advocate
   Member:
   - PostgreSQL Contributors Team (AWS) https://aws.amazon.com
   - PG Infra https://www.postgresql.org/about/governance/sysadmin/
   - PG RPMs  https://yum.postgresql.org/contact/