Re: PostgreSQL 12 error unrecognized configuration parameter zbx_tmp.wal_json_res

On 12/6/23 04:21, Arne Henrik Segtnan wrote:
>
> Hi all,
>
> We are currently running Zabbix 5.0 with PostgreSQL 12, and history and
> trend data partitioning.
> History and trend data housekeeping has been disabled in Zabbix.
>
> In the PostgreSQL logs, we get the following error:
>
> 2023-12-06 09:12:47 CET [3509536-5] zabbix@postgres STATEMENT: select
> current_setting('zbx_tmp.wal_json_res');
> 2023-12-06 09:17:47 CET [3516312-1] zabbix@postgres ERROR: permission
> denied for function pg_ls_waldir
> 2023-12-06 09:17:47 CET [3516312-2] zabbix@postgres CONTEXT: SQL
> statement "SELECT row_to_json(T) FROM (
> SELECT pg_wal_lsn_diff(pg_current_wal_lsn(),'0/00000000') AS WRITE,
> count(*) FROM pg_ls_waldir() AS COUNT
> ) T"
> PL/pgSQL function inline_code_block line 10 at SQL statement
> 2023-12-06 09:17:47 CET [3516312-3] zabbix@postgres STATEMENT: DO
> LANGUAGE plpgsql $$
> DECLARE
> ver integer;
> res text := '{"write":0,"count":0}';
> BEGIN
> SELECT current_setting('server_version_num') INTO ver;
>
> IF (SELECT NOT pg_is_in_recovery()) THEN
> IF (ver >= 100000) THEN
> SELECT row_to_json(T) INTO res FROM (
> SELECT pg_wal_lsn_diff(pg_current_wal_lsn(),'0/00000000') AS WRITE,
> count(*) FROM pg_ls_waldir() AS COUNT
> ) T;
>
> ELSE
> SELECT row_to_json(T) INTO res FROM (
> SELECT pg_xlog_location_diff(pg_current_xlog_location(),' 0/00000000')
> AS WRITE,
> count(*) FROM pg_ls_dir('pg_xlog') AS COUNT
> ) T;
> END IF;
> END IF;
>
> perform set_config('zbx_tmp.wal_json_res', res, false);
> END $$;
> 2023-12-06 09:17:47 CET [3516312-4] zabbix@postgres ERROR: unrecognized
> configuration parameter "zbx_tmp.wal_json_res"
> 2023-12-06 09:17:47 CET [3516312-5] zabbix@postgres STATEMENT: select
> current_setting('zbx_tmp.wal_json_res');
>
>
> This seems to be related to permissions-problem and missing/unknown
> configuration parameter.
> We found the following article describing how to set correct permission:
>
> https://github.com/bitnami/charts/issues/20247
> <https://github.com/bitnami/charts/issues/20247>
>
> Is this a known issue? Should we just perform the step according to
> procedure in above link to set permission, or will security be degraded
> as commented in article?


Or turn the DO into a full function created by a user with the necessary
privileges and use SECURITY DEFINER in the function definition to confer
those privileges to unprivileged user for the duration of the function
execution per:

https://www.postgresql.org/docs/12/sql-createfunction.html

>
> Please advise.
>
> Best regards,
> Arne H.
>
>

--
Adrian Klaver
adrian.klaver@aklaver.com