Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit77ea4f9

Browse files
committed
Grant memory views to pg_read_all_stats.
Grant privileges on views pg_backend_memory_contexts andpg_shmem_allocations to the role pg_read_all_stats. Also grant on theunderlying 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 parent8af57ad commit77ea4f9

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 ALLON pg_shmem_allocationsFROM PUBLIC;
624+
GRANTSELECTON pg_shmem_allocations TO pg_read_all_stats;
624625
REVOKE EXECUTEON FUNCTION pg_get_shmem_allocations()FROM PUBLIC;
626+
GRANT EXECUTEON FUNCTION pg_get_shmem_allocations() TO pg_read_all_stats;
625627

626628
CREATEVIEWpg_backend_memory_contextsAS
627629
SELECT*FROM pg_get_backend_memory_contexts();
628630

629631
REVOKE ALLON pg_backend_memory_contextsFROM PUBLIC;
632+
GRANTSELECTON pg_backend_memory_contexts TO pg_read_all_stats;
630633
REVOKE EXECUTEON FUNCTION pg_get_backend_memory_contexts()FROM PUBLIC;
634+
GRANT EXECUTEON 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-
#defineCATALOG_VERSION_NO202110271
56+
#defineCATALOG_VERSION_NO202110272
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
DROPTABLE lock_table;
14781478
DROPUSER 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+
SELECTCOUNT(*)>=0AS okFROM pg_backend_memory_contexts;
1499+
SELECTCOUNT(*)>=0AS okFROM pg_shmem_allocations;
1500+
RESET ROLE;
1501+
1502+
-- clean up
1503+
DROP ROLE regress_readallstats;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp