[#ZBX-20316] Not possible to use password based auth for PostgreSQL template

Comment by Vyacheslav Khaliev [ 2022 Jan 13 ]

Available in:

5.0.20rc1 45955d4287b
5.4.10rc1 62a297197fd
6.0.0rc1 (master) 5a82c06482c

Comment by Roman [ 2022 Sep 09 ]

Switch to `postgresql://` schema instead of arguments with values in not a good change. There is multiple issues with this change:

It's not possible connect via socket

# can't use socket with postgresql:// syntax
$ psql -qtAX postgresql://"zbx_monitor":""@"/run/postgresql/":"5432"/"postgres" -c "SELECT version();"
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "run/postgresql/:5432/postgres" does not exist

# perfectly fine with arguments
psql -qtAX -U"zbx_monitor" -h "/run/postgresql/" -p "5432" -d "postgres" -c "SELECT version();"
PostgreSQL 13.8 (Debian 13.8-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

Password in command line is visible to everyone, not only to zabbix user itself. And if we take one step back what is the problem when user who need to connect to DB can see DB password? User must know password to connect to DB if password authentication is used.

# executed as zabbix user
zabbix@dbhost$ psql -qtAX postgresql://"zbx_monitor":"foobar"@"localhost":"5432"/"postgres" -c "SELECT pg_sleep(20);"

# executed as otheruser
otheruser@dbhost$ ps axu | grep '[p]sql -qtAX'
zabbix   31352  1.0  0.2  26312 10836 pts/2    S+   21:03   0:00 /usr/lib/postgresql/13/bin/psql -qtAX postgresql://zbx_monitor:foobar@localhost:5432/postgres -c SELECT pg_sleep(20);

# note 'foobar' password is visible

Insecure example in pg_hba.conf is still here, and it allow any user to connect to DB server from localhost, everything that user is need to do - use zbx_monitor as username.

root@dbhost# grep zbx_monitor /var/lib/postgresql/13/main/pg_hba.conf
host    all             zbx_monitor     127.0.0.1/32            trust

someuser@dbhost$ psql -U zbx_monitor -h 127.0.0.1 -d myproddb
psql (13.8 (Debian 13.8-1.pgdg100+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

myproddb=> \dt
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | roles | table | postgres
(1 row)

myproddb=> \d roles
                                        Table "public.roles"
  Column   |          Type          | Collation | Nullable |                Default
-----------+------------------------+-----------+----------+----------------------------------------
 role_id   | integer                |           | not null | nextval('roles_role_id_seq'::regclass)
 role_name | character varying(255) |           | not null |
Indexes:
    "roles_pkey" PRIMARY KEY, btree (role_id)
    "roles_role_name_key" UNIQUE CONSTRAINT, btree (role_name)

# can't read data
myproddb=> select * from roles;
ERROR:  permission denied for table roles

Actually .pgpass (with correct file permissions) is most secure way to sore passwords as stated by PostgreSQL documentation. But it's not convenient - you can't set password in Zabbix WEB UI. Relatively secure way to pass password in command line but don't show it in process list - use environment variables. I.e.

someuser@dbhost$ PGHOST=localhost PGPORT=5432 PGUSER=zbx_monitor PGPASSWORD=foobar PGDATABASE=postgres psql -qtAX -c "SELECT pg_sleep(30);"

$ ps aux | grep "[p]sql -qtAX"
zabbix   15740  0.1  0.5  24972 11504 pts/1    S+   22:37   0:00 /usr/lib/postgresql/13/bin/psql -qtAX -d postgres -c SELECT pg_sleep(30);

$ cat /proc/15740/environ
cat: /proc/15740/environ: Permission denied

This way password will be only visible in Zabbix Agent log with DebugLevel=4 or higher, default is 3 and password won't be shown in logs. Btw with .pgpass password never end up in logs, but as already mentioned not very convenient to setup.

So UserParameter will look like this

UserParameter=pgsql.version[*], LANG=C.UTF-8 PGHOST="$1" PGPORT="$2" PGUSER="$3" PGPASSWORD="$4" PGDATABASE="$5" psql -qtAX -c "SELECT version();"
UserParameter=pgsql.dbstat[*], LANG=C.UTF-8 PGHOST="$1" PGPORT="$2" PGUSER="$3" PGPASSWORD="$4" PGDATABASE="$5" psql -qtAX -f "/var/lib/zabbix/postgresql/pgsql.dbstat.sql"

LANG=C.UTF-8 is useful to prevent parsing errors when server default locale is not English (not en_US.UTF-8 or similar).

[ZBX-20316] Not possible to use password based auth for PostgreSQL template Created: 2021 Dec 05 Updated: 2024 Apr 10 Resolved: 2022 Jan 25
Status:	Closed
Project:	ZABBIX BUGS AND ISSUES
Component/s:	Agent (G), Templates (T)
Affects Version/s:	5.0.18, 5.4.8, 6.0.0alpha7
Fix Version/s:	5.0.20rc1, 5.4.10rc1, 6.0.0beta3, 6.0 (plan)

[ZBX-20316] Not possible to use password based auth for PostgreSQL template Created: 2021 Dec 05 Updated: 2024 Apr 10 Resolved: 2022 Jan 25