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

Commit25fff40

Browse files
Default monitoring roles
Three nologin roles with non-overlapping privs are created by default* pg_read_all_settings - read all GUCs.* pg_read_all_stats - pg_stat_*, pg_database_size(), pg_tablespace_size()* pg_stat_scan_tables - may lock/scan tablesTop level role - pg_monitor includes all of the above by default, plus othersAuthor: Dave PageReviewed-by: Stephen Frost, Robert Haas, Peter Eisentraut, Simon Riggs
1 parente984ef5 commit25fff40

30 files changed

+196
-55
lines changed

‎contrib/pg_buffercache/Makefile

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4,8 +4,9 @@ MODULE_big = pg_buffercache
44
OBJS = pg_buffercache_pages.o$(WIN32RES)
55

66
EXTENSION = pg_buffercache
7-
DATA = pg_buffercache--1.2.sql pg_buffercache--1.1--1.2.sql\
8-
pg_buffercache--1.0--1.1.sql pg_buffercache--unpackaged--1.0.sql
7+
DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql\
8+
pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql\
9+
pg_buffercache--unpackaged--1.0.sql
910
PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time"
1011

1112
ifdefUSE_PGXS
Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,7 @@
1+
/* contrib/pg_buffercache/pg_buffercache--1.2--1.3.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION pg_buffercache UPDATE TO '1.3'" to load this file. \quit
5+
6+
GRANT EXECUTEON FUNCTION pg_buffercache_pages() TO pg_monitor;
7+
GRANTSELECTON pg_buffercache TO pg_monitor;
Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# pg_buffercache extension
22
comment = 'examine the shared buffer cache'
3-
default_version = '1.2'
3+
default_version = '1.3'
44
module_pathname = '$libdir/pg_buffercache'
55
relocatable = true

‎contrib/pg_freespacemap/Makefile

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4,8 +4,8 @@ MODULE_big = pg_freespacemap
44
OBJS = pg_freespacemap.o$(WIN32RES)
55

66
EXTENSION = pg_freespacemap
7-
DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.0--1.1.sql\
8-
pg_freespacemap--unpackaged--1.0.sql
7+
DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.1--1.2.sql\
8+
pg_freespacemap--1.0--1.1.sql pg_freespacemap--unpackaged--1.0.sql
99
PGFILEDESC = "pg_freespacemap - monitoring of free space map"
1010

1111
ifdefUSE_PGXS
Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,7 @@
1+
/* contrib/pg_freespacemap/pg_freespacemap--1.1--1.2.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION pg_freespacemap UPDATE TO '1.2'" to load this file. \quit
5+
6+
GRANT EXECUTEON FUNCTION pg_freespace(regclass,bigint) TO pg_stat_scan_tables;
7+
GRANT EXECUTEON FUNCTION pg_freespace(regclass) TO pg_stat_scan_tables;
Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# pg_freespacemap extension
22
comment = 'examine the free space map (FSM)'
3-
default_version = '1.1'
3+
default_version = '1.2'
44
module_pathname = '$libdir/pg_freespacemap'
55
relocatable = true

‎contrib/pg_stat_statements/Makefile

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -4,9 +4,10 @@ MODULE_big = pg_stat_statements
44
OBJS = pg_stat_statements.o$(WIN32RES)
55

66
EXTENSION = pg_stat_statements
7-
DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.3--1.4.sql\
8-
pg_stat_statements--1.2--1.3.sql pg_stat_statements--1.1--1.2.sql\
9-
pg_stat_statements--1.0--1.1.sql pg_stat_statements--unpackaged--1.0.sql
7+
DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.4--1.5.sql\
8+
pg_stat_statements--1.3--1.4.sql pg_stat_statements--1.2--1.3.sql\
9+
pg_stat_statements--1.1--1.2.sql pg_stat_statements--1.0--1.1.sql\
10+
pg_stat_statements--unpackaged--1.0.sql
1011
PGFILEDESC = "pg_stat_statements - execution statistics of SQL statements"
1112

1213
LDFLAGS_SL +=$(filter -lm,$(LIBS))
Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,6 @@
1+
/* contrib/pg_stat_statements/pg_stat_statements--1.4--1.5.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION pg_stat_statements UPDATE TO '1.5'" to load this file. \quit
5+
6+
GRANT EXECUTEON FUNCTION pg_stat_statements_reset() TO pg_read_all_stats;

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -62,6 +62,7 @@
6262
#include<unistd.h>
6363

6464
#include"access/hash.h"
65+
#include"catalog/pg_authid.h"
6566
#include"executor/instrument.h"
6667
#include"funcapi.h"
6768
#include"mb/pg_wchar.h"
@@ -1391,14 +1392,17 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
13911392
MemoryContextper_query_ctx;
13921393
MemoryContextoldcontext;
13931394
Oiduserid=GetUserId();
1394-
boolis_superuser=superuser();
1395+
boolis_allowed_role=false;
13951396
char*qbuffer=NULL;
13961397
Sizeqbuffer_size=0;
13971398
Sizeextent=0;
13981399
intgc_count=0;
13991400
HASH_SEQ_STATUShash_seq;
14001401
pgssEntry*entry;
14011402

1403+
/* Superusers or members of pg_read_all_stats members are allowed */
1404+
is_allowed_role=is_member_of_role(GetUserId(),DEFAULT_ROLE_READ_ALL_STATS);
1405+
14021406
/* hash table must exist already */
14031407
if (!pgss|| !pgss_hash)
14041408
ereport(ERROR,
@@ -1541,7 +1545,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
15411545
values[i++]=ObjectIdGetDatum(entry->key.userid);
15421546
values[i++]=ObjectIdGetDatum(entry->key.dbid);
15431547

1544-
if (is_superuser||entry->key.userid==userid)
1548+
if (is_allowed_role||entry->key.userid==userid)
15451549
{
15461550
if (api_version >=PGSS_V1_2)
15471551
values[i++]=Int64GetDatumFast(queryid);
Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# pg_stat_statements extension
22
comment = 'track execution statistics of all SQL statements executed'
3-
default_version = '1.4'
3+
default_version = '1.5'
44
module_pathname = '$libdir/pg_stat_statements'
55
relocatable = true

‎contrib/pg_visibility/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,8 @@ MODULE_big = pg_visibility
44
OBJS = pg_visibility.o$(WIN32RES)
55

66
EXTENSION = pg_visibility
7-
DATA = pg_visibility--1.1.sql pg_visibility--1.0--1.1.sql
7+
DATA = pg_visibility--1.1.sql pg_visibility--1.1--1.2.sql\
8+
pg_visibility--1.0--1.1.sql
89
PGFILEDESC = "pg_visibility - page visibility information"
910

1011
REGRESS = pg_visibility
Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,13 @@
1+
/* contrib/pg_visibility/pg_visibility--1.1--1.2.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION pg_visibility UPDATE TO '1.2'" to load this file. \quit
5+
6+
-- Allow use of monitoring functions by pg_monitor members
7+
GRANT EXECUTEON FUNCTION pg_visibility_map(regclass,bigint) TO pg_stat_scan_tables;
8+
GRANT EXECUTEON FUNCTION pg_visibility(regclass,bigint) TO pg_stat_scan_tables;
9+
GRANT EXECUTEON FUNCTION pg_visibility_map(regclass) TO pg_stat_scan_tables;
10+
GRANT EXECUTEON FUNCTION pg_visibility(regclass) TO pg_stat_scan_tables;
11+
GRANT EXECUTEON FUNCTION pg_visibility_map_summary(regclass) TO pg_stat_scan_tables;
12+
GRANT EXECUTEON FUNCTION pg_check_frozen(regclass) TO pg_stat_scan_tables;
13+
GRANT EXECUTEON FUNCTION pg_check_visible(regclass) TO pg_stat_scan_tables;
Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# pg_visibility extension
22
comment = 'examine the visibility map (VM) and page-level visibility info'
3-
default_version = '1.1'
3+
default_version = '1.2'
44
module_pathname = '$libdir/pg_visibility'
55
relocatable = true

‎contrib/pgrowlocks/pgrowlocks.c

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,7 @@
2828
#include"access/relscan.h"
2929
#include"access/xact.h"
3030
#include"catalog/namespace.h"
31+
#include"catalog/pg_authid.h"
3132
#include"funcapi.h"
3233
#include"miscadmin.h"
3334
#include"storage/bufmgr.h"
@@ -98,9 +99,11 @@ pgrowlocks(PG_FUNCTION_ARGS)
9899
relrv=makeRangeVarFromNameList(textToQualifiedNameList(relname));
99100
rel=heap_openrv(relrv,AccessShareLock);
100101

101-
/* check permissions: must have SELECT on table */
102-
aclresult=pg_class_aclcheck(RelationGetRelid(rel),GetUserId(),
103-
ACL_SELECT);
102+
/* check permissions: must have SELECT on table or be in pg_stat_scan_tables */
103+
aclresult= (pg_class_aclcheck(RelationGetRelid(rel),GetUserId(),
104+
ACL_SELECT)||
105+
is_member_of_role(GetUserId(),DEFAULT_ROLE_STAT_SCAN_TABLES);
106+
104107
if (aclresult!=ACLCHECK_OK)
105108
aclcheck_error(aclresult,ACL_KIND_CLASS,
106109
RelationGetRelationName(rel));

‎contrib/pgstattuple/pgstattuple--1.4--1.5.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,7 @@ AS 'MODULE_PATHNAME', 'pgstattuple_v1_5'
1717
LANGUAGE C STRICT PARALLEL SAFE;
1818

1919
REVOKE EXECUTEON FUNCTION pgstattuple(text)FROM PUBLIC;
20+
GRANT EXECUTEON FUNCTION pgstattuple(text) TO pg_stat_scan_tables;
2021

2122
CREATE OR REPLACEFUNCTIONpgstatindex(IN relnametext,
2223
OUT versionINT,
@@ -33,13 +34,15 @@ AS 'MODULE_PATHNAME', 'pgstatindex_v1_5'
3334
LANGUAGE C STRICT PARALLEL SAFE;
3435

3536
REVOKE EXECUTEON FUNCTION pgstatindex(text)FROM PUBLIC;
37+
GRANT EXECUTEON FUNCTION pgstatindex(text) TO pg_stat_scan_tables;
3638

3739
CREATE OR REPLACEFUNCTIONpg_relpages(IN relnametext)
3840
RETURNSBIGINT
3941
AS'MODULE_PATHNAME','pg_relpages_v1_5'
4042
LANGUAGE C STRICT PARALLEL SAFE;
4143

4244
REVOKE EXECUTEON FUNCTION pg_relpages(text)FROM PUBLIC;
45+
GRANT EXECUTEON FUNCTION pg_relpages(text) TO pg_stat_scan_tables;
4346

4447
/* New stuff in 1.1 begins here*/
4548

@@ -51,6 +54,7 @@ AS 'MODULE_PATHNAME', 'pgstatginindex_v1_5'
5154
LANGUAGE C STRICT PARALLEL SAFE;
5255

5356
REVOKE EXECUTEON FUNCTION pgstatginindex(regclass)FROM PUBLIC;
57+
GRANT EXECUTEON FUNCTION pgstatginindex(regclass) TO pg_stat_scan_tables;
5458

5559
/* New stuff in 1.2 begins here*/
5660

@@ -68,6 +72,7 @@ AS 'MODULE_PATHNAME', 'pgstattuplebyid_v1_5'
6872
LANGUAGE C STRICT PARALLEL SAFE;
6973

7074
REVOKE EXECUTEON FUNCTION pgstattuple(regclass)FROM PUBLIC;
75+
GRANT EXECUTEON FUNCTION pgstattuple(regclass) TO pg_stat_scan_tables;
7176

7277
CREATE OR REPLACEFUNCTIONpgstatindex(IN relname regclass,
7378
OUT versionINT,
@@ -84,13 +89,15 @@ AS 'MODULE_PATHNAME', 'pgstatindexbyid_v1_5'
8489
LANGUAGE C STRICT PARALLEL SAFE;
8590

8691
REVOKE EXECUTEON FUNCTION pgstatindex(regclass)FROM PUBLIC;
92+
GRANT EXECUTEON FUNCTION pgstatindex(regclass) TO pg_stat_scan_tables;
8793

8894
CREATE OR REPLACEFUNCTIONpg_relpages(IN relname regclass)
8995
RETURNSBIGINT
9096
AS'MODULE_PATHNAME','pg_relpagesbyid_v1_5'
9197
LANGUAGE C STRICT PARALLEL SAFE;
9298

9399
REVOKE EXECUTEON FUNCTION pg_relpages(regclass)FROM PUBLIC;
100+
GRANT EXECUTEON FUNCTION pg_relpages(regclass) TO pg_stat_scan_tables;
94101

95102
/* New stuff in 1.3 begins here*/
96103

@@ -109,6 +116,7 @@ AS 'MODULE_PATHNAME', 'pgstattuple_approx_v1_5'
109116
LANGUAGE C STRICT PARALLEL SAFE;
110117

111118
REVOKE EXECUTEON FUNCTION pgstattuple_approx(regclass)FROM PUBLIC;
119+
GRANT EXECUTEON FUNCTION pgstattuple_approx(regclass) TO pg_stat_scan_tables;
112120

113121
/* New stuff in 1.5 begins here*/
114122

@@ -125,3 +133,4 @@ AS 'MODULE_PATHNAME', 'pgstathashindex'
125133
LANGUAGE C STRICT PARALLEL SAFE;
126134

127135
REVOKE EXECUTEON FUNCTION pgstathashindex(regclass)FROM PUBLIC;
136+
GRANT EXECUTEON FUNCTION pgstathashindex(regclass) TO pg_stat_scan_tables;

‎doc/src/sgml/catalogs.sgml

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -10221,15 +10221,17 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
1022110221
<entry><type>text</type></entry>
1022210222
<entry>Configuration file the current value was set in (null for
1022310223
values set from sources other than configuration files, or when
10224-
examined by a non-superuser);
10225-
helpful when using <literal>include</> directives in configuration files</entry>
10224+
examined by a user who is neither a superuser or a member of
10225+
<literal>pg_read_all_settings</literal>); helpful when using
10226+
<literal>include</> directives in configuration files</entry>
1022610227
</row>
1022710228
<row>
1022810229
<entry><structfield>sourceline</structfield></entry>
1022910230
<entry><type>integer</type></entry>
1023010231
<entry>Line number within the configuration file the current value was
1023110232
set at (null for values set from sources other than configuration files,
10232-
or when examined by a non-superuser)
10233+
or when examined by a user who is neither a superuser or a member of
10234+
<literal>pg_read_all_settings</literal>).
1023310235
</entry>
1023410236
</row>
1023510237
<row>

‎doc/src/sgml/func.sgml

Lines changed: 14 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -19408,9 +19408,11 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
1940819408
accept the OID or name of a database or tablespace, and return the total
1940919409
disk space used therein. To use <function>pg_database_size</function>,
1941019410
you must have <literal>CONNECT</> permission on the specified database
19411-
(which is granted by default). To use <function>pg_tablespace_size</>,
19412-
you must have <literal>CREATE</> permission on the specified tablespace,
19413-
unless it is the default tablespace for the current database.
19411+
(which is granted by default), or be a member of the <literal>pg_read_all_stats</>
19412+
role. To use <function>pg_tablespace_size</>, you must have
19413+
<literal>CREATE</> permission on the specified tablespace, or be a member
19414+
of the <literal>pg_read_all_stats</> role unless it is the default tablespace for
19415+
the current database.
1941419416
</para>
1941519417

1941619418
<para>
@@ -19736,7 +19738,8 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
1973619738
<entry><type>setof record</type></entry>
1973719739
<entry>
1973819740
List the name, size, and last modification time of files in the log
19739-
directory. Access may be granted to non-superuser roles.
19741+
directory. Access is granted to members of the <literal>pg_monitor</>
19742+
role and may be granted to other non-superuser roles.
1974019743
</entry>
1974119744
</row>
1974219745
<row>
@@ -19746,7 +19749,8 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
1974619749
<entry><type>setof record</type></entry>
1974719750
<entry>
1974819751
List the name, size, and last modification time of files in the WAL
19749-
directory. Access may be granted to non-superuser roles.
19752+
directory. Access is granted to members of the <literal>pg_monitor</>
19753+
role and may be granted to other non-superuser roles.
1975019754
</entry>
1975119755
</row>
1975219756
<row>
@@ -19807,8 +19811,8 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
1980719811
<para>
1980819812
<function>pg_ls_logdir</> returns the name, size, and last modified time
1980919813
(mtime) of each file in the log directory. By default, only superusers
19810-
can use this function, but access may be granted to others using
19811-
<command>GRANT</command>.
19814+
and members of the <literal>pg_monitor</> role can use this function.
19815+
Access may be granted to others using<command>GRANT</command>.
1981219816
</para>
1981319817

1981419818
<indexterm>
@@ -19817,8 +19821,9 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
1981719821
<para>
1981819822
<function>pg_ls_waldir</> returns the name, size, and last modified time
1981919823
(mtime) of each file in the write ahead log (WAL) directory. By
19820-
default only superusers can use this function, but access may be granted
19821-
to others using <command>GRANT</command>.
19824+
default only superusers and members of the <literal>pg_monitor</> role
19825+
can use this function. Access may be granted to others using
19826+
<command>GRANT</command>.
1982219827
</para>
1982319828

1982419829
<indexterm>

‎doc/src/sgml/pgbuffercache.sgml

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -24,8 +24,9 @@
2424
</para>
2525

2626
<para>
27-
By default public access is revoked from both of these, just in case there
28-
are security issues lurking.
27+
By default use is restricted to superusers and members of the
28+
<literal>pg_read_all_stats</literal> role. Access may be granted to others
29+
using <command>GRANT</command>.
2930
</para>
3031

3132
<sect2>

‎doc/src/sgml/pgfreespacemap.sgml

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -16,8 +16,9 @@
1616
</para>
1717

1818
<para>
19-
By default public access is revoked from the functions, just in case
20-
there are security issues lurking.
19+
By default use is restricted to superusers and members of the
20+
<literal>pg_stat_scan_tables</literal> role. Access may be granted to others
21+
using <command>GRANT</command>.
2122
</para>
2223

2324
<sect2>

‎doc/src/sgml/pgrowlocks.sgml

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,13 @@
1212
locking information for a specified table.
1313
</para>
1414

15+
<para>
16+
By default use is restricted to superusers, members of the
17+
<literal>pg_stat_scan_tables</literal> role, and users with
18+
<literal>SELECT</literal> permissions on the table.
19+
</para>
20+
21+
1522
<sect2>
1623
<title>Overview</title>
1724

‎doc/src/sgml/pgstatstatements.sgml

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -226,10 +226,11 @@
226226
</table>
227227

228228
<para>
229-
For security reasons, non-superusers are not allowed to see the SQL
230-
text or <structfield>queryid</structfield> of queries executed by other users.
231-
They can see the statistics, however, if the view has been installed in their
232-
database.
229+
For security reasons, only superusers and members of the
230+
<literal>pg_read_all_stats<literal> role are allowed to see the SQL text and
231+
<structfield>queryid</structfield> of queries executed by other users.
232+
Other users can see the statistics, however, if the view has been installed
233+
in their database.
233234
</para>
234235

235236
<para>

‎doc/src/sgml/pgstattuple.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,8 @@
1616
As these functions return detailed page-level information, only the superuser
1717
has EXECUTE privileges on them upon installation. After the functions have
1818
been installed, users may issue <command>GRANT</command> commands to change
19-
the privileges on the functions to allow non-superusers to execute them. See
19+
the privileges on the functions to allow non-superusers to execute them. Members
20+
of the <literal>pg_stat_scan_tables</literal> role are granted access by default. See
2021
the description of the <xref linkend="sql-grant"> command for specifics.
2122
</para>
2223

‎doc/src/sgml/pgvisibility.sgml

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -140,7 +140,10 @@
140140
</variablelist>
141141

142142
<para>
143-
By default, these functions are executable only by superusers.
143+
By default, these functions are executable only by superusers and members of the
144+
<literal>pg_stat_scan_tables</literal> role, with the exception of
145+
<function>pg_truncate_visibility_map(relation regclass)</function> which can only
146+
be executed by superusers.
144147
</para>
145148
</sect2>
146149

0 commit comments

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