From 25fff40798fc4ac11a241bfd9ab0c45c085e2212 Mon Sep 17 00:00:00 2001 From: Simon Riggs Date: Thu, 30 Mar 2017 14:18:53 -0400 Subject: 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 --- doc/src/sgml/catalogs.sgml | 8 +++++--- doc/src/sgml/func.sgml | 23 ++++++++++++++--------- doc/src/sgml/pgbuffercache.sgml | 5 +++-- doc/src/sgml/pgfreespacemap.sgml | 5 +++-- doc/src/sgml/pgrowlocks.sgml | 7 +++++++ doc/src/sgml/pgstatstatements.sgml | 9 +++++---- doc/src/sgml/pgstattuple.sgml | 3 ++- doc/src/sgml/pgvisibility.sgml | 5 ++++- doc/src/sgml/user-manag.sgml | 36 ++++++++++++++++++++++++++++++++++++ 9 files changed, 79 insertions(+), 22 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index ac39c639edc..65ba919e7b8 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -10221,15 +10221,17 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx text 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 include directives in configuration files + examined by a user who is neither a superuser or a member of + pg_read_all_settings); helpful when using + include directives in configuration files sourceline integer 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 + pg_read_all_settings). diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 78508d74ece..076be587eac 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -19408,9 +19408,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 pg_database_size, you must have CONNECT permission on the specified database - (which is granted by default). To use pg_tablespace_size, - you must have 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 pg_read_all_stats + role. To use pg_tablespace_size, you must have + CREATE permission on the specified tablespace, or be a member + of the pg_read_all_stats role unless it is the default tablespace for + the current database. @@ -19736,7 +19738,8 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup()); setof record List the name, size, and last modification time of files in the log - directory. Access may be granted to non-superuser roles. + directory. Access is granted to members of the pg_monitor + role and may be granted to other non-superuser roles. @@ -19746,7 +19749,8 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup()); setof record List the name, size, and last modification time of files in the WAL - directory. Access may be granted to non-superuser roles. + directory. Access is granted to members of the pg_monitor + role and may be granted to other non-superuser roles. @@ -19807,8 +19811,8 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup()); pg_ls_logdir returns the name, size, and last modified time (mtime) of each file in the log directory. By default, only superusers - can use this function, but access may be granted to others using - GRANT. + and members of the pg_monitor role can use this function. + Access may be granted to others using GRANT. @@ -19817,8 +19821,9 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup()); pg_ls_waldir returns the name, size, and last modified time (mtime) of each file in the write ahead log (WAL) directory. By - default only superusers can use this function, but access may be granted - to others using GRANT. + default only superusers and members of the pg_monitor role + can use this function. Access may be granted to others using + GRANT. diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index b261a4dbe0a..4e53009ae07 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -24,8 +24,9 @@ - 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 + pg_read_all_stats role. Access may be granted to others + using GRANT. diff --git a/doc/src/sgml/pgfreespacemap.sgml b/doc/src/sgml/pgfreespacemap.sgml index f2f99d571ef..43e154a2f3a 100644 --- a/doc/src/sgml/pgfreespacemap.sgml +++ b/doc/src/sgml/pgfreespacemap.sgml @@ -16,8 +16,9 @@ - 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 + pg_stat_scan_tables role. Access may be granted to others + using GRANT. diff --git a/doc/src/sgml/pgrowlocks.sgml b/doc/src/sgml/pgrowlocks.sgml index d73511579c4..65d532e0810 100644 --- a/doc/src/sgml/pgrowlocks.sgml +++ b/doc/src/sgml/pgrowlocks.sgml @@ -12,6 +12,13 @@ locking information for a specified table. + + By default use is restricted to superusers, members of the + pg_stat_scan_tables role, and users with + SELECT permissions on the table. + + + Overview diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index 082994cae00..2d55d3bdad4 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -226,10 +226,11 @@ - For security reasons, non-superusers are not allowed to see the SQL - text or queryid 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 + pg_read_all_stats role are allowed to see the SQL text and + queryid of queries executed by other users. + Other users can see the statistics, however, if the view has been installed + in their database. diff --git a/doc/src/sgml/pgstattuple.sgml b/doc/src/sgml/pgstattuple.sgml index 62b1a6f4794..141d8e225fb 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 GRANT 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 pg_stat_scan_tables role are granted access by default. See the description of the command for specifics. diff --git a/doc/src/sgml/pgvisibility.sgml b/doc/src/sgml/pgvisibility.sgml index fd486696fc1..d466a3bce86 100644 --- a/doc/src/sgml/pgvisibility.sgml +++ b/doc/src/sgml/pgvisibility.sgml @@ -140,7 +140,10 @@ - By default, these functions are executable only by superusers. + By default, these functions are executable only by superusers and members of the + pg_stat_scan_tables role, with the exception of + pg_truncate_visibility_map(relation regclass) which can only + be executed by superusers. diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml index 7eaefe58c2f..914f1505abd 100644 --- a/doc/src/sgml/user-manag.sgml +++ b/doc/src/sgml/user-manag.sgml @@ -515,14 +515,50 @@ DROP ROLE doomed_role; + + pg_read_all_settings + Read all configuration variables, even those normally visible only to + superusers. + + + pg_read_all_stats + Read all pg_stat_* views and use various statistics related extensions, + even those normally visible only to superusers. + + + pg_stat_scan_tables + Execute monitoring functions that may take AccessShareLocks on tables, + potentially for a long time. + pg_signal_backend Send signals to other backends (eg: cancel query, terminate). + + pg_monitor + Read/execute various monitoring views and functions. + This role is a member of pg_read_all_settings, + pg_read_all_stats and + pg_stat_scan_tables. + + + The pg_monitor, pg_read_all_settings, + pg_read_all_stats and pg_stat_scan_tables + roles are intended to allow administrators to easily configure a role for the + purpose of monitoring the database server. They grant a set of common privileges + allowing the role to read various useful configuration settings, statistics and + other system information normally restricted to superusers. + + + + Care should be taken when granting these roles to ensure they are only used where + needed to perform the desired monitoring. + + Administrators can grant access to these roles to users using the GRANT command: -- cgit v1.2.3