Re: PostgreSQL 12 error unrecognized configuration parameter zbx_tmp.wal_json_res - Mailing list pgsql-general

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