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 tables

Top level role - pg_monitor includes all of the above by default, plus others

Author: Dave Page
Reviewed-by: Stephen Frost, Robert Haas, Peter Eisentraut, Simon Riggs

15 files changed, 69 insertions, 17 deletions

diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile
index 497dbeb2297..18f7a874524 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 00000000000..b37ef0112e3
--- /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 a4d664f3fa7..8c060ae9abf 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 7bc0e9555df..0a2f000ec66 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 00000000000..f558defadde
--- /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_stat_scan_tables;
+GRANT EXECUTE ON FUNCTION  pg_freespace(regclass) TO pg_stat_scan_tables;
diff --git a/contrib/pg_freespacemap/pg_freespacemap.control b/contrib/pg_freespacemap/pg_freespacemap.control
index 764db30d18e..ac8fc5050a9 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 298951a5f50..39b368b70eb 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 00000000000..9c76122a2b7
--- /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_read_all_stats;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index cd4c16e9d2c..c3002618524 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"
@@ -1391,7 +1392,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
 	MemoryContext per_query_ctx;
 	MemoryContext oldcontext;
 	Oid			userid = GetUserId();
-	bool		is_superuser = superuser();
+	bool		is_allowed_role = false;
 	char	   *qbuffer = NULL;
 	Size		qbuffer_size = 0;
 	Size		extent = 0;
@@ -1399,6 +1400,9 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
 	HASH_SEQ_STATUS hash_seq;
 	pgssEntry  *entry;
 
+	/* Superusers or members of pg_read_all_stats members are allowed */
+	is_allowed_role = is_member_of_role(GetUserId(), DEFAULT_ROLE_READ_ALL_STATS);
+
 	/* hash table must exist already */
 	if (!pgss || !pgss_hash)
 		ereport(ERROR,
@@ -1541,7 +1545,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
 		values[i++] = ObjectIdGetDatum(entry->key.userid);
 		values[i++] = ObjectIdGetDatum(entry->key.dbid);
 
-		if (is_superuser || entry->key.userid == userid)
+		if (is_allowed_role || entry->key.userid == userid)
 		{
 			if (api_version >= PGSS_V1_2)
 				values[i++] = Int64GetDatumFast(queryid);
diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control
index 24038f56b17..193fcdfafa0 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 bc429444260..21d787ddf78 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"
 
 REGRESS = pg_visibility
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 00000000000..a5a4fe7ca85
--- /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_stat_scan_tables;
+GRANT EXECUTE ON FUNCTION pg_visibility(regclass, bigint) TO pg_stat_scan_tables;
+GRANT EXECUTE ON FUNCTION pg_visibility_map(regclass) TO pg_stat_scan_tables;
+GRANT EXECUTE ON FUNCTION pg_visibility(regclass) TO pg_stat_scan_tables;
+GRANT EXECUTE ON FUNCTION pg_visibility_map_summary(regclass) TO pg_stat_scan_tables;
+GRANT EXECUTE ON FUNCTION pg_check_frozen(regclass) TO pg_stat_scan_tables;
+GRANT EXECUTE ON FUNCTION pg_check_visible(regclass) TO pg_stat_scan_tables;
diff --git a/contrib/pg_visibility/pg_visibility.control b/contrib/pg_visibility/pg_visibility.control
index f93ed0176ec..3cffa08b015 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 db9e0349a03..31b8626e3a0 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_stat_scan_tables */
+		aclresult = (pg_class_aclcheck(RelationGetRelid(rel), GetUserId(),
+									  ACL_SELECT) ||
+			is_member_of_role(GetUserId(), DEFAULT_ROLE_STAT_SCAN_TABLES);
+
 		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 84e112e1c2f..05ae51fa4bb 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_stat_scan_tables;
 
 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_stat_scan_tables;
 
 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_stat_scan_tables;
 
 /* 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_stat_scan_tables;
 
 /* 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_stat_scan_tables;
 
 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_stat_scan_tables;
 
 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_stat_scan_tables;
 
 /* 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_stat_scan_tables;
 
 /* 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_stat_scan_tables;