Per the discussion at
https://www.postgresql.org/message-id/CA%2BOCxoyYxO%2BJmzv2Micj4uAaQdAi6nq0w25BPQgLLxsrvTmREw%40mail.gmail.com,
attached is a patch that implements the following:
- Adds a default role called pg_monitor
- Gives members of the pg_monitor role full access to:
pg_ls_logdir() and pg_ls_waldir()
pg_stat_* views and functions
pg_tablespace_size() and pg_database_size()
Contrib modules:
pg_buffercache,
pg_freespacemap,
pgrowlocks,
pg_stat_statements,
pgstattuple and
pg_visibility (but NOT pg_truncate_visibility_map() )
- Adds a default role called pg_read_all_gucs
- Allows members of pg_read_all_gucs to, well, read all GUCs
- Grants pg_read_all_gucs to pg_monitor
Note that updates to contrib modules followed the strategy recently
used in changes to pgstattuple following discussion here, in which the
installation SQL script is left at the prior version, and an update
script is added and default version number bumped to match that of the
upgrade script.
Patch includes doc updates, and is dependent on my pg_ls_logdir() and
pg_ls_waldir() patch
(https://www.postgresql.org/message-id/CA+OCxow-X=D2fWdKy+HP+vQ1LtrgbsYQ=cshzzbqyft5joy...@mail.gmail.com).
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile
index 497dbeb229..18f7a87452 100644
--- a/contrib/pg_buffercache/Makefile
+++ b/contrib/pg_buffercache/Makefile
@@ -4,8 +4,9 @@ MODULE_big = pg_buffercache
OBJS = pg_buffercache_pages.o $(WIN32RES)
EXTENSION = pg_buffercache
-DATA = pg_buffercache--1.2.sql pg_buffercache--1.1--1.2.sql \
- pg_buffercache--1.0--1.1.sql pg_buffercache--unpackaged--1.0.sql
+DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql \
+ pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql \
+ pg_buffercache--unpackaged--1.0.sql
PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time"
ifdef USE_PGXS
diff --git a/contrib/pg_buffercache/pg_buffercache--1.2--1.3.sql
b/contrib/pg_buffercache/pg_buffercache--1.2--1.3.sql
new file mode 100644
index 0000000000..b37ef0112e
--- /dev/null
+++ b/contrib/pg_buffercache/pg_buffercache--1.2--1.3.sql
@@ -0,0 +1,7 @@
+/* contrib/pg_buffercache/pg_buffercache--1.2--1.3.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_buffercache UPDATE TO '1.3'" to load this file.
\quit
+
+GRANT EXECUTE ON FUNCTION pg_buffercache_pages() TO pg_monitor;
+GRANT SELECT ON pg_buffercache TO pg_monitor;
diff --git a/contrib/pg_buffercache/pg_buffercache.control
b/contrib/pg_buffercache/pg_buffercache.control
index a4d664f3fa..8c060ae9ab 100644
--- a/contrib/pg_buffercache/pg_buffercache.control
+++ b/contrib/pg_buffercache/pg_buffercache.control
@@ -1,5 +1,5 @@
# pg_buffercache extension
comment = 'examine the shared buffer cache'
-default_version = '1.2'
+default_version = '1.3'
module_pathname = '$libdir/pg_buffercache'
relocatable = true
diff --git a/contrib/pg_freespacemap/Makefile b/contrib/pg_freespacemap/Makefile
index 7bc0e9555d..0a2f000ec6 100644
--- a/contrib/pg_freespacemap/Makefile
+++ b/contrib/pg_freespacemap/Makefile
@@ -4,8 +4,8 @@ MODULE_big = pg_freespacemap
OBJS = pg_freespacemap.o $(WIN32RES)
EXTENSION = pg_freespacemap
-DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.0--1.1.sql \
- pg_freespacemap--unpackaged--1.0.sql
+DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.1--1.2.sql \
+ pg_freespacemap--1.0--1.1.sql pg_freespacemap--unpackaged--1.0.sql
PGFILEDESC = "pg_freespacemap - monitoring of free space map"
ifdef USE_PGXS
diff --git a/contrib/pg_freespacemap/pg_freespacemap--1.1--1.2.sql
b/contrib/pg_freespacemap/pg_freespacemap--1.1--1.2.sql
new file mode 100644
index 0000000000..490bb3bf46
--- /dev/null
+++ b/contrib/pg_freespacemap/pg_freespacemap--1.1--1.2.sql
@@ -0,0 +1,7 @@
+/* contrib/pg_freespacemap/pg_freespacemap--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_freespacemap UPDATE TO '1.2'" to load this file.
\quit
+
+GRANT EXECUTE ON FUNCTION pg_freespace(regclass, bigint) TO pg_monitor;
+GRANT EXECUTE ON FUNCTION pg_freespace(regclass) TO pg_monitor;
diff --git a/contrib/pg_freespacemap/pg_freespacemap.control
b/contrib/pg_freespacemap/pg_freespacemap.control
index 764db30d18..ac8fc5050a 100644
--- a/contrib/pg_freespacemap/pg_freespacemap.control
+++ b/contrib/pg_freespacemap/pg_freespacemap.control
@@ -1,5 +1,5 @@
# pg_freespacemap extension
comment = 'examine the free space map (FSM)'
-default_version = '1.1'
+default_version = '1.2'
module_pathname = '$libdir/pg_freespacemap'
relocatable = true
diff --git a/contrib/pg_stat_statements/Makefile
b/contrib/pg_stat_statements/Makefile
index 298951a5f5..39b368b70e 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -4,9 +4,10 @@ MODULE_big = pg_stat_statements
OBJS = pg_stat_statements.o $(WIN32RES)
EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.3--1.4.sql \
- pg_stat_statements--1.2--1.3.sql pg_stat_statements--1.1--1.2.sql \
- pg_stat_statements--1.0--1.1.sql pg_stat_statements--unpackaged--1.0.sql
+DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.4--1.5.sql \
+ pg_stat_statements--1.3--1.4.sql pg_stat_statements--1.2--1.3.sql \
+ pg_stat_statements--1.1--1.2.sql pg_stat_statements--1.0--1.1.sql \
+ pg_stat_statements--unpackaged--1.0.sql
PGFILEDESC = "pg_stat_statements - execution statistics of SQL statements"
LDFLAGS_SL += $(filter -lm, $(LIBS))
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.4--1.5.sql
b/contrib/pg_stat_statements/pg_stat_statements--1.4--1.5.sql
new file mode 100644
index 0000000000..1c2db3cc1a
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.4--1.5.sql
@@ -0,0 +1,6 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.4--1.5.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.5'" to load this
file. \quit
+
+GRANT EXECUTE ON FUNCTION pg_stat_statements_reset() TO pg_monitor;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c
b/contrib/pg_stat_statements/pg_stat_statements.c
index 62dec8768a..c315ba966d 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -62,6 +62,7 @@
#include <unistd.h>
#include "access/hash.h"
+#include "catalog/pg_authid.h"
#include "executor/instrument.h"
#include "funcapi.h"
#include "mb/pg_wchar.h"
@@ -1386,7 +1387,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
MemoryContext per_query_ctx;
MemoryContext oldcontext;
Oid userid = GetUserId();
- bool is_superuser = superuser();
+ bool is_superuser = false;
char *qbuffer = NULL;
Size qbuffer_size = 0;
Size extent = 0;
@@ -1394,6 +1395,9 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
HASH_SEQ_STATUS hash_seq;
pgssEntry *entry;
+ /* For the purposes of this module, we consider pg_monitor members to
be superusers */
+ is_superuser = (superuser() || is_member_of_role(GetUserId(),
DEFAULT_ROLE_MONITOR));
+
/* hash table must exist already */
if (!pgss || !pgss_hash)
ereport(ERROR,
diff --git a/contrib/pg_stat_statements/pg_stat_statements.control
b/contrib/pg_stat_statements/pg_stat_statements.control
index 24038f56b1..193fcdfafa 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.control
+++ b/contrib/pg_stat_statements/pg_stat_statements.control
@@ -1,5 +1,5 @@
# pg_stat_statements extension
comment = 'track execution statistics of all SQL statements executed'
-default_version = '1.4'
+default_version = '1.5'
module_pathname = '$libdir/pg_stat_statements'
relocatable = true
diff --git a/contrib/pg_visibility/Makefile b/contrib/pg_visibility/Makefile
index 379591a098..1aa44308d0 100644
--- a/contrib/pg_visibility/Makefile
+++ b/contrib/pg_visibility/Makefile
@@ -4,7 +4,8 @@ MODULE_big = pg_visibility
OBJS = pg_visibility.o $(WIN32RES)
EXTENSION = pg_visibility
-DATA = pg_visibility--1.1.sql pg_visibility--1.0--1.1.sql
+DATA = pg_visibility--1.1.sql pg_visibility--1.1--1.2.sql \
+ pg_visibility--1.0--1.1.sql
PGFILEDESC = "pg_visibility - page visibility information"
ifdef USE_PGXS
diff --git a/contrib/pg_visibility/pg_visibility--1.1--1.2.sql
b/contrib/pg_visibility/pg_visibility--1.1--1.2.sql
new file mode 100644
index 0000000000..9e0da46416
--- /dev/null
+++ b/contrib/pg_visibility/pg_visibility--1.1--1.2.sql
@@ -0,0 +1,13 @@
+/* contrib/pg_visibility/pg_visibility--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_visibility UPDATE TO '1.2'" to load this file.
\quit
+
+-- Allow use of monitoring functions by pg_monitor members
+GRANT EXECUTE ON FUNCTION pg_visibility_map(regclass, bigint) TO pg_monitor;
+GRANT EXECUTE ON FUNCTION pg_visibility(regclass, bigint) TO pg_monitor;
+GRANT EXECUTE ON FUNCTION pg_visibility_map(regclass) TO pg_monitor;
+GRANT EXECUTE ON FUNCTION pg_visibility(regclass) TO pg_monitor;
+GRANT EXECUTE ON FUNCTION pg_visibility_map_summary(regclass) TO pg_monitor;
+GRANT EXECUTE ON FUNCTION pg_check_frozen(regclass) TO pg_monitor;
+GRANT EXECUTE ON FUNCTION pg_check_visible(regclass) TO pg_monitor;
diff --git a/contrib/pg_visibility/pg_visibility.control
b/contrib/pg_visibility/pg_visibility.control
index f93ed0176e..3cffa08b01 100644
--- a/contrib/pg_visibility/pg_visibility.control
+++ b/contrib/pg_visibility/pg_visibility.control
@@ -1,5 +1,5 @@
# pg_visibility extension
comment = 'examine the visibility map (VM) and page-level visibility info'
-default_version = '1.1'
+default_version = '1.2'
module_pathname = '$libdir/pg_visibility'
relocatable = true
diff --git a/contrib/pgrowlocks/pgrowlocks.c b/contrib/pgrowlocks/pgrowlocks.c
index 4d49610641..0b31c3a70e 100644
--- a/contrib/pgrowlocks/pgrowlocks.c
+++ b/contrib/pgrowlocks/pgrowlocks.c
@@ -28,6 +28,7 @@
#include "access/relscan.h"
#include "access/xact.h"
#include "catalog/namespace.h"
+#include "catalog/pg_authid.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "storage/bufmgr.h"
@@ -98,9 +99,11 @@ pgrowlocks(PG_FUNCTION_ARGS)
relrv =
makeRangeVarFromNameList(textToQualifiedNameList(relname));
rel = heap_openrv(relrv, AccessShareLock);
- /* check permissions: must have SELECT on table */
- aclresult = pg_class_aclcheck(RelationGetRelid(rel),
GetUserId(),
-
ACL_SELECT);
+ /* check permissions: must have SELECT on table or be in
pg_monitor */
+ aclresult = (pg_class_aclcheck(RelationGetRelid(rel),
GetUserId(),
+
ACL_SELECT) ||
+ is_member_of_role(GetUserId(), DEFAULT_ROLE_MONITOR));
+
if (aclresult != ACLCHECK_OK)
aclcheck_error(aclresult, ACL_KIND_CLASS,
RelationGetRelationName(rel));
diff --git a/contrib/pgstattuple/pgstattuple--1.4--1.5.sql
b/contrib/pgstattuple/pgstattuple--1.4--1.5.sql
index 84e112e1c2..ad97206e17 100644
--- a/contrib/pgstattuple/pgstattuple--1.4--1.5.sql
+++ b/contrib/pgstattuple/pgstattuple--1.4--1.5.sql
@@ -17,6 +17,7 @@ AS 'MODULE_PATHNAME', 'pgstattuple_v1_5'
LANGUAGE C STRICT PARALLEL SAFE;
REVOKE EXECUTE ON FUNCTION pgstattuple(text) FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION pgstattuple(text) TO pg_monitor;
CREATE OR REPLACE FUNCTION pgstatindex(IN relname text,
OUT version INT,
@@ -33,6 +34,7 @@ AS 'MODULE_PATHNAME', 'pgstatindex_v1_5'
LANGUAGE C STRICT PARALLEL SAFE;
REVOKE EXECUTE ON FUNCTION pgstatindex(text) FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION pgstatindex(text) TO pg_monitor;
CREATE OR REPLACE FUNCTION pg_relpages(IN relname text)
RETURNS BIGINT
@@ -40,6 +42,7 @@ AS 'MODULE_PATHNAME', 'pg_relpages_v1_5'
LANGUAGE C STRICT PARALLEL SAFE;
REVOKE EXECUTE ON FUNCTION pg_relpages(text) FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION pg_relpages(text) TO pg_monitor;
/* New stuff in 1.1 begins here */
@@ -51,6 +54,7 @@ AS 'MODULE_PATHNAME', 'pgstatginindex_v1_5'
LANGUAGE C STRICT PARALLEL SAFE;
REVOKE EXECUTE ON FUNCTION pgstatginindex(regclass) FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION pgstatginindex(regclass) TO pg_monitor;
/* New stuff in 1.2 begins here */
@@ -68,6 +72,7 @@ AS 'MODULE_PATHNAME', 'pgstattuplebyid_v1_5'
LANGUAGE C STRICT PARALLEL SAFE;
REVOKE EXECUTE ON FUNCTION pgstattuple(regclass) FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION pgstattuple(regclass) TO pg_monitor;
CREATE OR REPLACE FUNCTION pgstatindex(IN relname regclass,
OUT version INT,
@@ -84,6 +89,7 @@ AS 'MODULE_PATHNAME', 'pgstatindexbyid_v1_5'
LANGUAGE C STRICT PARALLEL SAFE;
REVOKE EXECUTE ON FUNCTION pgstatindex(regclass) FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION pgstatindex(regclass) TO pg_monitor;
CREATE OR REPLACE FUNCTION pg_relpages(IN relname regclass)
RETURNS BIGINT
@@ -91,6 +97,7 @@ AS 'MODULE_PATHNAME', 'pg_relpagesbyid_v1_5'
LANGUAGE C STRICT PARALLEL SAFE;
REVOKE EXECUTE ON FUNCTION pg_relpages(regclass) FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION pg_relpages(regclass) TO pg_monitor;
/* New stuff in 1.3 begins here */
@@ -109,6 +116,7 @@ AS 'MODULE_PATHNAME', 'pgstattuple_approx_v1_5'
LANGUAGE C STRICT PARALLEL SAFE;
REVOKE EXECUTE ON FUNCTION pgstattuple_approx(regclass) FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION pgstattuple_approx(regclass) TO pg_monitor;
/* New stuff in 1.5 begins here */
@@ -125,3 +133,4 @@ AS 'MODULE_PATHNAME', 'pgstathashindex'
LANGUAGE C STRICT PARALLEL SAFE;
REVOKE EXECUTE ON FUNCTION pgstathashindex(regclass) FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION pgstathashindex(regclass) TO pg_monitor;
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 96cb9185c2..a4fc96eb36 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -10016,15 +10016,17 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts
ppx
<entry><type>text</type></entry>
<entry>Configuration file the current value was set in (null for
values set from sources other than configuration files, or when
- examined by a non-superuser);
- helpful when using <literal>include</> directives in configuration
files</entry>
+ examined by a user who is neither a superuser or a member of
+ <literal>pg_read_all_gucs</literal>); helpful when using
+ <literal>include</> directives in configuration files</entry>
</row>
<row>
<entry><structfield>sourceline</structfield></entry>
<entry><type>integer</type></entry>
<entry>Line number within the configuration file the current value was
set at (null for values set from sources other than configuration files,
- or when examined by a non-superuser)
+ or when examined by a user who is neither a superuser or a member of
+ <literal>pg_read_all_gucs</literal>).
</entry>
</row>
<row>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 8d60f13b80..4b674f8361 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19053,9 +19053,11 @@ postgres=# SELECT * FROM
pg_walfile_name_offset(pg_stop_backup());
accept the OID or name of a database or tablespace, and return the total
disk space used therein. To use <function>pg_database_size</function>,
you must have <literal>CONNECT</> permission on the specified database
- (which is granted by default). To use <function>pg_tablespace_size</>,
- you must have <literal>CREATE</> permission on the specified tablespace,
- unless it is the default tablespace for the current database.
+ (which is granted by default), or be a member of the <literal>pg_monitor</>
+ role. To use <function>pg_tablespace_size</>, you must have
+ <literal>CREATE</> permission on the specified tablespace, or be a member
+ of the <literal>pg_monitor</> role unless it is the default tablespace for
+ the current database.
</para>
<para>
@@ -19335,7 +19337,9 @@ postgres=# SELECT * FROM
pg_walfile_name_offset(pg_stop_backup());
database cluster directory and the <varname>log_directory</> can be
accessed. Use a relative path for files in the cluster directory,
and a path matching the <varname>log_directory</> configuration setting
- for log files. Use of these functions is restricted to superusers.
+ for log files. Use of these functions is restricted to superusers, with
+ the exception of <function>pg_ls_logdir</> and <function>pg_ls_waldir</>
+ which may also be used by members of the <literal>pg_monitor</> role.
</para>
<table id="functions-admin-genfile-table">
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index b261a4dbe0..2515bc9704 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -24,8 +24,9 @@
</para>
<para>
- By default public access is revoked from both of these, just in case there
- are security issues lurking.
+ By default use is restricted to superusers and members of the
+ <literal>pg_monitor</literal> role, just in case there are security
+ issues lurking.
</para>
<sect2>
diff --git a/doc/src/sgml/pgfreespacemap.sgml b/doc/src/sgml/pgfreespacemap.sgml
index f2f99d571e..f912d5a425 100644
--- a/doc/src/sgml/pgfreespacemap.sgml
+++ b/doc/src/sgml/pgfreespacemap.sgml
@@ -16,8 +16,9 @@
</para>
<para>
- By default public access is revoked from the functions, just in case
- there are security issues lurking.
+ By default use is restricted to superusers and members of the
+ <literal>pg_monitor</literal> role, just in case there are security
+ issues lurking.
</para>
<sect2>
diff --git a/doc/src/sgml/pgrowlocks.sgml b/doc/src/sgml/pgrowlocks.sgml
index d73511579c..db83f3194b 100644
--- a/doc/src/sgml/pgrowlocks.sgml
+++ b/doc/src/sgml/pgrowlocks.sgml
@@ -12,6 +12,13 @@
locking information for a specified table.
</para>
+ <para>
+ By default use is restricted to superusers, members of the
+ <literal>pg_monitor</literal> role, and users with
+ <literal>SELECT</literal> permissions on the table.
+ </para>
+
+
<sect2>
<title>Overview</title>
diff --git a/doc/src/sgml/pgstatstatements.sgml
b/doc/src/sgml/pgstatstatements.sgml
index d4f09fc2a3..d127a2af92 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -226,10 +226,11 @@
</table>
<para>
- For security reasons, non-superusers are not allowed to see the SQL
- text or <structfield>queryid</structfield> of queries executed by other
users.
- They can see the statistics, however, if the view has been installed in
their
- database.
+ For security reasons, only superusers and members of the
+ <literal>pg_monitor<literal> role are allowed to see the SQL text and
+ <structfield>queryid</structfield> of queries executed by other users.
+ Other users can see the statistics, however, if the view has been installed
+ in their database.
</para>
<para>
diff --git a/doc/src/sgml/pgstattuple.sgml b/doc/src/sgml/pgstattuple.sgml
index 62b1a6f479..61bdad3d2f 100644
--- a/doc/src/sgml/pgstattuple.sgml
+++ b/doc/src/sgml/pgstattuple.sgml
@@ -16,7 +16,8 @@
As these functions return detailed page-level information, only the superuser
has EXECUTE privileges on them upon installation. After the functions have
been installed, users may issue <command>GRANT</command> commands to change
- the privileges on the functions to allow non-superusers to execute them. See
+ the privileges on the functions to allow non-superusers to execute them.
Members
+ of the <literal>pg_monitor</literal> role are granted access by default. See
the description of the <xref linkend="sql-grant"> command for specifics.
</para>
diff --git a/doc/src/sgml/pgvisibility.sgml b/doc/src/sgml/pgvisibility.sgml
index fd486696fc..1869a15df5 100644
--- a/doc/src/sgml/pgvisibility.sgml
+++ b/doc/src/sgml/pgvisibility.sgml
@@ -140,7 +140,10 @@
</variablelist>
<para>
- By default, these functions are executable only by superusers.
+ By default, these functions are executable only by superusers and members
of the
+ <literal>pg_monitor</literal> role, with the exception of
+ <function>pg_truncate_visibility_map(relation regclass)</function> which
can only
+ be executed by superusers.
</para>
</sect2>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 7eaefe58c2..af65392858 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -516,6 +516,15 @@ DROP ROLE doomed_role;
</thead>
<tbody>
<row>
+ <entry>pg_monitor</entry>
+ <entry>Read/execute various monitoring views and functions.
+ This role is a member of <literal>pg_read_all_gucs</literal></entry>
+ </row>
+ <row>
+ <entry>pg_read_all_gucs</entry>
+ <entry>Read all configuration variables, even those normally visible
only to superusers.</entry>
+ </row>
+ <row>
<entry>pg_signal_backend</entry>
<entry>Send signals to other backends (eg: cancel query,
terminate).</entry>
</row>
diff --git a/src/backend/catalog/system_views.sql
b/src/backend/catalog/system_views.sql
index 4b67102439..80ba9f2580 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1099,3 +1099,7 @@ REVOKE EXECUTE ON FUNCTION
pg_stat_reset_single_function_counters(oid) FROM publ
REVOKE EXECUTE ON FUNCTION pg_ls_logdir() FROM public;
REVOKE EXECUTE ON FUNCTION pg_ls_waldir() FROM public;
+GRANT EXECUTE ON FUNCTION pg_ls_logdir() TO pg_monitor;
+GRANT EXECUTE ON FUNCTION pg_ls_waldir() TO pg_monitor;
+
+GRANT pg_read_all_gucs TO pg_monitor;
diff --git a/src/backend/replication/walreceiver.c
b/src/backend/replication/walreceiver.c
index 18d9d7e4ec..58a849f9af 100644
--- a/src/backend/replication/walreceiver.c
+++ b/src/backend/replication/walreceiver.c
@@ -50,6 +50,7 @@
#include "access/timeline.h"
#include "access/transam.h"
#include "access/xlog_internal.h"
+#include "catalog/pg_authid.h"
#include "catalog/pg_type.h"
#include "funcapi.h"
#include "libpq/pqformat.h"
@@ -1396,7 +1397,7 @@ pg_stat_get_wal_receiver(PG_FUNCTION_ARGS)
/* Fetch values */
values[0] = Int32GetDatum(walrcv->pid);
- if (!superuser())
+ if (!superuser() && !is_member_of_role(GetUserId(),
DEFAULT_ROLE_MONITOR))
{
/*
* Only superusers can see details. Other users only get the
pid value
diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index ec3a28cd0d..cb86878f1b 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -18,6 +18,7 @@
#include "access/htup_details.h"
#include "catalog/catalog.h"
#include "catalog/namespace.h"
+#include "catalog/pg_authid.h"
#include "catalog/pg_tablespace.h"
#include "commands/dbcommands.h"
#include "commands/tablespace.h"
@@ -89,11 +90,17 @@ calculate_database_size(Oid dbOid)
char pathname[MAXPGPATH];
AclResult aclresult;
- /* User must have connect privilege for target database */
+ /*
+ * User must have connect privilege for target database
+ * or be a member of pg_monitor
+ */
aclresult = pg_database_aclcheck(dbOid, GetUserId(), ACL_CONNECT);
- if (aclresult != ACLCHECK_OK)
+ if (aclresult != ACLCHECK_OK &&
+ !is_member_of_role(GetUserId(), DEFAULT_ROLE_MONITOR))
+ {
aclcheck_error(aclresult, ACL_KIND_DATABASE,
get_database_name(dbOid));
+ }
/* Shared storage in pg_global is not counted */
@@ -173,11 +180,12 @@ calculate_tablespace_size(Oid tblspcOid)
AclResult aclresult;
/*
- * User must have CREATE privilege for target tablespace, either
- * explicitly granted or implicitly because it is default for current
- * database.
+ * User must be a member of pg_monitor or have CREATE privilege for
+ * target tablespace, either explicitly granted or implicitly because
+ * it is default for current database.
*/
- if (tblspcOid != MyDatabaseTableSpace)
+ if (tblspcOid != MyDatabaseTableSpace &&
+ !is_member_of_role(GetUserId(), DEFAULT_ROLE_MONITOR))
{
aclresult = pg_tablespace_aclcheck(tblspcOid, GetUserId(),
ACL_CREATE);
if (aclresult != ACLCHECK_OK)
diff --git a/src/backend/utils/adt/pgstatfuncs.c
b/src/backend/utils/adt/pgstatfuncs.c
index a987d0d621..b60c74bde0 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -15,6 +15,7 @@
#include "postgres.h"
#include "access/htup_details.h"
+#include "catalog/pg_authid.h"
#include "catalog/pg_type.h"
#include "common/ip.h"
#include "funcapi.h"
@@ -648,8 +649,9 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
nulls[18] = nulls[19] = nulls[20] = nulls[21] =
nulls[22] = true;
}
- /* Values only available to role member */
- if (has_privs_of_role(GetUserId(), beentry->st_userid))
+ /* Values only available to role member or pg_monitor */
+ if (has_privs_of_role(GetUserId(), beentry->st_userid) ||
+ is_member_of_role(GetUserId(), DEFAULT_ROLE_MONITOR))
{
SockAddr zero_clientaddr;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 0707f66631..a5ed9fba6b 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -34,6 +34,7 @@
#include "access/xact.h"
#include "access/xlog_internal.h"
#include "catalog/namespace.h"
+#include "catalog/pg_authid.h"
#include "commands/async.h"
#include "commands/prepare.h"
#include "commands/user.h"
@@ -6667,10 +6668,12 @@ GetConfigOption(const char *name, bool missing_ok, bool
restrict_superuser)
}
if (restrict_superuser &&
(record->flags & GUC_SUPERUSER_ONLY) &&
- !superuser())
+ !superuser() &&
+ !is_member_of_role(GetUserId(), DEFAULT_ROLE_READ_ALL_GUCS))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("must be superuser to examine \"%s\"",
name)));
+ errmsg("must be superuser or a member of
pg_monitor to examine \"%s\"",
+ name)));
switch (record->vartype)
{
@@ -6715,10 +6718,13 @@ GetConfigOptionResetString(const char *name)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("unrecognized configuration parameter
\"%s\"", name)));
- if ((record->flags & GUC_SUPERUSER_ONLY) && !superuser())
+ if ((record->flags & GUC_SUPERUSER_ONLY) &&
+ !superuser() &&
+ !is_member_of_role(GetUserId(), DEFAULT_ROLE_READ_ALL_GUCS))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("must be superuser to examine \"%s\"",
name)));
+ errmsg("must be superuser or a member of
pg_monitor to examine \"%s\"",
+ name)));
switch (record->vartype)
{
@@ -8005,10 +8011,13 @@ GetConfigOptionByName(const char *name, const char
**varname, bool missing_ok)
errmsg("unrecognized configuration parameter
\"%s\"", name)));
}
- if ((record->flags & GUC_SUPERUSER_ONLY) && !superuser())
+ if ((record->flags & GUC_SUPERUSER_ONLY) &&
+ !superuser() &&
+ !is_member_of_role(GetUserId(), DEFAULT_ROLE_READ_ALL_GUCS))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("must be superuser to examine \"%s\"",
name)));
+ errmsg("must be superuser or a member of
pg_monitor to examine \"%s\"",
+ name)));
if (varname)
*varname = record->name;
@@ -8034,7 +8043,9 @@ GetConfigOptionByNum(int varnum, const char **values,
bool *noshow)
if (noshow)
{
if ((conf->flags & GUC_NO_SHOW_ALL) ||
- ((conf->flags & GUC_SUPERUSER_ONLY) && !superuser()))
+ ((conf->flags & GUC_SUPERUSER_ONLY) &&
+ !superuser() &&
+ !is_member_of_role(GetUserId(),
DEFAULT_ROLE_READ_ALL_GUCS)))
*noshow = true;
else
*noshow = false;
diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h
index def71edaa8..bc9968996a 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -99,10 +99,14 @@ typedef FormData_pg_authid *Form_pg_authid;
* ----------------
*/
DATA(insert OID = 10 ( "POSTGRES" t t t t t t t -1 _null_ _null_));
+DATA(insert OID = 3355 ( "pg_monitor" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3356 ( "pg_read_all_gucs" f t f f f f f -1 _null_ _null_));
DATA(insert OID = 4200 ( "pg_signal_backend" f t f f f f f -1 _null_ _null_));
#define BOOTSTRAP_SUPERUSERID 10
+#define DEFAULT_ROLE_MONITOR 3355
+#define DEFAULT_ROLE_READ_ALL_GUCS 3356
#define DEFAULT_ROLE_SIGNAL_BACKENDID 4200
#endif /* PG_AUTHID_H */
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
