postgres password appears in process table

[freedge]

Container platform

OCP 4

Version

quay.io/sclorg/postgresql-15-c9s:latest from awx-operator

OS version of the container image

CentOS Stream 9

Bugzilla, Jira

No response

Description

this image runs psql with the password set on the command line. As a result it appears in the process table and is recorded by auditing tools.

eg

postgresql-container/10/root/usr/share/container-scripts/postgresql/start/set_passwords.sh

Line 6 in d0cecca

_psql --set=username="$POSTGRESQL_USER" \

as deployed by awx-operator, the postgres container will execute a

psql        --set ON_ERROR_STOP=1 --set=username=awx --set=password=ZTH7V8R1wg2GwI..

Reproducer

cd 16 && ; podman build -t db -f ./Dockerfile.c9s
sudo auditctl  -a exit,always -F arch=x86_64 -S execve
podman run -ti -v /var/lib/pgsql/data --name db  -e POSTGRESQL_USER=awx -e POSTGRESQL_PASSWORD=lepassword -e POSTGRESQL_DATABASE=awx -e POSTGRESQL_MASTER_USER=lemaster -e POSTGRESQL_MASTER_PASSWORD=lemaster -e POSTGRESQL_ADMIN_PASSWORD=more --rm db
sudo grep psql /var/log/audit/audit.log | grep lepassword
type=EXECVE msg=audit(1713081027.200:82065): argc=5 a0="psql" a1="--set" a2="ON_ERROR_STOP=1" a3="--set=username=awx" a4="--set=password=lepassword"

something in this fashion would work

--- a/16/root/usr/share/container-scripts/postgresql/start/set_passwords.sh
+++ b/16/root/usr/share/container-scripts/postgresql/start/set_passwords.sh
@@ -1,23 +1,21 @@
 #!/bin/bash

-_psql () { psql --set ON_ERROR_STOP=1 "$@" ; }
+_psql () { setsid psql --set ON_ERROR_STOP=1 "$@" ; }

 if [[ ",$postinitdb_actions," = *,simple_db,* ]]; then
-_psql --set=username="$POSTGRESQL_USER" \
-      --set=password="$POSTGRESQL_PASSWORD" \
-<<< "ALTER USER :\"username\" WITH ENCRYPTED PASSWORD :'password';"
+(echo "${POSTGRESQL_PASSWORD}" ; echo "${POSTGRESQL_PASSWORD}"
+) | _psql --set=username="$POSTGRESQL_USER" \
+      -f <(echo '\password :username')
 fi

[pkubatrh]

Thanks for the report. This makes sense to fix. Let's take a look.

[hhorak]

The suggested solution is quite clever indeed. I've been thinking a bit how to approach this with a bit more straightforward solution and without shell hacks, and found out possibility to get an env variable value this way:

postgres=# \set mypass `echo $POSTGRESQL_ADMIN_PASSWORD`
postgres=# select :'mypass';
 ?column? 
----------
 test
(1 row)

However, I'm not sure whether this way is safe from the SQL injection perspective - something worth re-checking on postgresql forum?

Another way would be to install python3-psycopg2 and write a simple python script that would do what we need -- installing it into the container would only be a few hundreds of kB more.