Simple patch to implement $SUBJECT attached. pg_signal_backend seems like the appropriate predefined role, because pg_log_backend_memory_contexts() is implemented by a sending signal.
Regards, Jeff Davis
From 473e0bb2e1ae0d56dbc6b0262c16b10c6c0454cc Mon Sep 17 00:00:00 2001 From: Jeff Davis <j...@j-davis.com> Date: Fri, 22 Oct 2021 13:40:35 -0700 Subject: [PATCH] Allow pg_signal_backend members to call pg_log_backend_memory_contexts(). --- src/backend/catalog/system_functions.sql | 5 +++++ src/backend/utils/adt/mcxtfuncs.c | 6 ----- src/include/catalog/catversion.h | 2 +- src/test/regress/expected/misc_functions.out | 23 ++++++++++++++++++-- src/test/regress/sql/misc_functions.sql | 15 +++++++++++-- 5 files changed, 40 insertions(+), 11 deletions(-) diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql index a416e94d371..2cd5e3fe17b 100644 --- a/src/backend/catalog/system_functions.sql +++ b/src/backend/catalog/system_functions.sql @@ -699,6 +699,8 @@ REVOKE EXECUTE ON FUNCTION pg_ls_dir(text) FROM public; REVOKE EXECUTE ON FUNCTION pg_ls_dir(text,boolean,boolean) FROM public; +REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer) FROM PUBLIC; + -- -- We also set up some things as accessible to standard roles. -- @@ -713,6 +715,9 @@ GRANT EXECUTE ON FUNCTION pg_ls_tmpdir() TO pg_monitor; GRANT EXECUTE ON FUNCTION pg_ls_tmpdir(oid) TO pg_monitor; +GRANT EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer) + TO pg_signal_backend; + GRANT pg_read_all_settings TO pg_monitor; GRANT pg_read_all_stats TO pg_monitor; diff --git a/src/backend/utils/adt/mcxtfuncs.c b/src/backend/utils/adt/mcxtfuncs.c index 0d52613bc32..e06280ad712 100644 --- a/src/backend/utils/adt/mcxtfuncs.c +++ b/src/backend/utils/adt/mcxtfuncs.c @@ -177,12 +177,6 @@ pg_log_backend_memory_contexts(PG_FUNCTION_ARGS) int pid = PG_GETARG_INT32(0); PGPROC *proc; - /* Only allow superusers to log memory contexts. */ - if (!superuser()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be a superuser to log memory contexts"))); - proc = BackendPidGetProc(pid); /* diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 3253b8751b1..039f6338604 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202109101 +#define CATALOG_VERSION_NO 202110230 #endif diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out index e845042d38d..38e36fec43e 100644 --- a/src/test/regress/expected/misc_functions.out +++ b/src/test/regress/expected/misc_functions.out @@ -138,14 +138,33 @@ HINT: No function matches the given name and argument types. You might need to -- -- Memory contexts are logged and they are not returned to the function. -- Furthermore, their contents can vary depending on the timing. However, --- we can at least verify that the code doesn't fail. +-- we can at least verify that the code doesn't fail, and that the +-- permissions are set properly. -- -SELECT * FROM pg_log_backend_memory_contexts(pg_backend_pid()); +SELECT pg_log_backend_memory_contexts(pg_backend_pid()); pg_log_backend_memory_contexts -------------------------------- t (1 row) +CREATE ROLE testrole1 IN ROLE pg_signal_backend; +CREATE ROLE testrole2; +SELECT has_function_privilege('testrole1', + 'pg_log_backend_memory_contexts(integer)', 'EXECUTE'); -- yes + has_function_privilege +------------------------ + t +(1 row) + +SELECT has_function_privilege('testrole2', + 'pg_log_backend_memory_contexts(integer)', 'EXECUTE'); -- no + has_function_privilege +------------------------ + f +(1 row) + +DROP ROLE testrole1; +DROP ROLE testrole2; -- -- Test some built-in SRFs -- diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql index a398349afc6..abf8b33b11c 100644 --- a/src/test/regress/sql/misc_functions.sql +++ b/src/test/regress/sql/misc_functions.sql @@ -35,9 +35,20 @@ SELECT num_nulls(); -- -- Memory contexts are logged and they are not returned to the function. -- Furthermore, their contents can vary depending on the timing. However, --- we can at least verify that the code doesn't fail. +-- we can at least verify that the code doesn't fail, and that the +-- permissions are set properly. -- -SELECT * FROM pg_log_backend_memory_contexts(pg_backend_pid()); + +SELECT pg_log_backend_memory_contexts(pg_backend_pid()); + +CREATE ROLE testrole1 IN ROLE pg_signal_backend; +CREATE ROLE testrole2; +SELECT has_function_privilege('testrole1', + 'pg_log_backend_memory_contexts(integer)', 'EXECUTE'); -- yes +SELECT has_function_privilege('testrole2', + 'pg_log_backend_memory_contexts(integer)', 'EXECUTE'); -- no +DROP ROLE testrole1; +DROP ROLE testrole2; -- -- Test some built-in SRFs -- 2.17.1