Skip to content

Commit 77ea4f9

Browse files
jeff-davisjeff-davis
committed
Grant memory views to pg_read_all_stats.
Grant privileges on views pg_backend_memory_contexts and pg_shmem_allocations to the role pg_read_all_stats. Also grant on the underlying functions that those views depend on. Author: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> Reviewed-by: Nathan Bossart <bossartn@amazon.com> Discussion: https://postgr.es/m/CALj2ACWAZo3Ar_EVsn2Zf9irG+hYK3cmh1KWhZS_Od45nd01RA@mail.gmail.com
1 parent 8af57ad commit 77ea4f9

File tree

5 files changed

+81
-3
lines changed

5 files changed

+81
-3
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9916,7 +9916,8 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
99169916

99179917
<para>
99189918
By default, the <structname>pg_backend_memory_contexts</structname> view can be
9919-
read only by superusers.
9919+
read only by superusers or members of the <literal>pg_read_all_stats</literal>
9920+
role.
99209921
</para>
99219922
</sect1>
99229923

@@ -12746,7 +12747,8 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
1274612747

1274712748
<para>
1274812749
By default, the <structname>pg_shmem_allocations</structname> view can be
12749-
read only by superusers.
12750+
read only by superusers or members of the <literal>pg_read_all_stats</literal>
12751+
role.
1275012752
</para>
1275112753
</sect1>
1275212754

‎src/backend/catalog/system_views.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -621,13 +621,17 @@ CREATE VIEW pg_shmem_allocations AS
621621
SELECT * FROM pg_get_shmem_allocations();
622622

623623
REVOKE ALL ON pg_shmem_allocations FROM PUBLIC;
624+
GRANT SELECT ON pg_shmem_allocations TO pg_read_all_stats;
624625
REVOKE EXECUTE ON FUNCTION pg_get_shmem_allocations() FROM PUBLIC;
626+
GRANT EXECUTE ON FUNCTION pg_get_shmem_allocations() TO pg_read_all_stats;
625627

626628
CREATE VIEW pg_backend_memory_contexts AS
627629
SELECT * FROM pg_get_backend_memory_contexts();
628630

629631
REVOKE ALL ON pg_backend_memory_contexts FROM PUBLIC;
632+
GRANT SELECT ON pg_backend_memory_contexts TO pg_read_all_stats;
630633
REVOKE EXECUTE ON FUNCTION pg_get_backend_memory_contexts() FROM PUBLIC;
634+
GRANT EXECUTE ON FUNCTION pg_get_backend_memory_contexts() TO pg_read_all_stats;
631635

632636
-- Statistics views
633637

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 202110271
56+
#define CATALOG_VERSION_NO 202110272
5757

5858
#endif

‎src/test/regress/expected/privileges.out

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2413,3 +2413,50 @@ REVOKE TRUNCATE ON lock_table FROM regress_locktable_user;
24132413
-- clean up
24142414
DROP TABLE lock_table;
24152415
DROP USER regress_locktable_user;
2416+
-- test to check privileges of system views pg_shmem_allocations and
2417+
-- pg_backend_memory_contexts.
2418+
-- switch to superuser
2419+
\c -
2420+
CREATE ROLE regress_readallstats;
2421+
SELECT has_table_privilege('regress_readallstats','pg_backend_memory_contexts','SELECT'); -- no
2422+
has_table_privilege
2423+
---------------------
2424+
f
2425+
(1 row)
2426+
2427+
SELECT has_table_privilege('regress_readallstats','pg_shmem_allocations','SELECT'); -- no
2428+
has_table_privilege
2429+
---------------------
2430+
f
2431+
(1 row)
2432+
2433+
GRANT pg_read_all_stats TO regress_readallstats;
2434+
SELECT has_table_privilege('regress_readallstats','pg_backend_memory_contexts','SELECT'); -- yes
2435+
has_table_privilege
2436+
---------------------
2437+
t
2438+
(1 row)
2439+
2440+
SELECT has_table_privilege('regress_readallstats','pg_shmem_allocations','SELECT'); -- yes
2441+
has_table_privilege
2442+
---------------------
2443+
t
2444+
(1 row)
2445+
2446+
-- run query to ensure that functions within views can be executed
2447+
SET ROLE regress_readallstats;
2448+
SELECT COUNT(*) >= 0 AS ok FROM pg_backend_memory_contexts;
2449+
ok
2450+
----
2451+
t
2452+
(1 row)
2453+
2454+
SELECT COUNT(*) >= 0 AS ok FROM pg_shmem_allocations;
2455+
ok
2456+
----
2457+
t
2458+
(1 row)
2459+
2460+
RESET ROLE;
2461+
-- clean up
2462+
DROP ROLE regress_readallstats;

‎src/test/regress/sql/privileges.sql

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1476,3 +1476,28 @@ REVOKE TRUNCATE ON lock_table FROM regress_locktable_user;
14761476
-- clean up
14771477
DROP TABLE lock_table;
14781478
DROP USER regress_locktable_user;
1479+
1480+
-- test to check privileges of system views pg_shmem_allocations and
1481+
-- pg_backend_memory_contexts.
1482+
1483+
-- switch to superuser
1484+
\c -
1485+
1486+
CREATE ROLE regress_readallstats;
1487+
1488+
SELECT has_table_privilege('regress_readallstats','pg_backend_memory_contexts','SELECT'); -- no
1489+
SELECT has_table_privilege('regress_readallstats','pg_shmem_allocations','SELECT'); -- no
1490+
1491+
GRANT pg_read_all_stats TO regress_readallstats;
1492+
1493+
SELECT has_table_privilege('regress_readallstats','pg_backend_memory_contexts','SELECT'); -- yes
1494+
SELECT has_table_privilege('regress_readallstats','pg_shmem_allocations','SELECT'); -- yes
1495+
1496+
-- run query to ensure that functions within views can be executed
1497+
SET ROLE regress_readallstats;
1498+
SELECT COUNT(*) >= 0 AS ok FROM pg_backend_memory_contexts;
1499+
SELECT COUNT(*) >= 0 AS ok FROM pg_shmem_allocations;
1500+
RESET ROLE;
1501+
1502+
-- clean up
1503+
DROP ROLE regress_readallstats;

0 commit comments

Comments
 (0)