Use GRANT system to manage access to sensitive functions

Now that pg_dump will properly dump out any ACL changes made to
functions which exist in pg_catalog, switch to using the GRANT system
to manage access to those functions.

This means removing 'if (!superuser()) ereport()' checks from the
functions themselves and then REVOKEing EXECUTE right from 'public' for
these functions in system_views.sql.

Reviews by Alexander Korotkov, Jose Luis Tallon

diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
index 9092cf8fe333bb3cd65a953cae91d9484bf4b0a8..8c65df2b15a09adffd97a3e3bdfa260c55f5b8fa 100644 (file)
--- a/doc/src/sgml/backup.sgml
+++ b/doc/src/sgml/backup.sgml
@@ -826,7 +826,9 @@ test ! -f /mnt/server/archivedir/00000001000000A900000065 && cp pg_xlog/
    </listitem>
    <listitem>
     <para>
-     Connect to the database as a superuser and issue the command:
+     Connect to the database as a user with rights to run pg_start_backup
+     (superuser, or a user who has been granted EXECUTE on the function)
+     and issue the command:
 <programlisting>
 SELECT pg_start_backup('label');
 </programlisting>
@@ -877,7 +879,9 @@ SELECT pg_start_backup('label', true);
    </listitem>
    <listitem>
     <para>
-     Again connect to the database as a superuser, and issue the command:
+     Again connect to the database as a user with rights to run
+     pg_stop_backup (superuser, or a user who has been granted EXECUTE on
+     the function), and issue the command:
 <programlisting>
 SELECT pg_stop_backup();
 </programlisting>

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2ce89bb030fb399342d280603935278af6f5c5d2..15b6b4eb3d570c841a41692f4d58ea2b0a79fc19 100644 (file)
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17339,8 +17339,9 @@ SELECT set_config('log_statement_stats', 'off', false);
    <para>
     The functions shown in <xref
     linkend="functions-admin-signal-table"> send control signals to
-    other server processes.  Use of these functions is usually restricted
-    to superusers, with noted exceptions.
+    other server processes.  Use of these functions is restricted to
+    superusers by default but access may be granted to others with the
+    <command>GRANT</command>, with noted exceptions.
    </para>
 
    <table id="functions-admin-signal-table">
@@ -17490,7 +17491,7 @@ SELECT set_config('log_statement_stats', 'off', false);
         <literal><function>pg_create_restore_point(<parameter>name</> <type>text</>)</function></literal>
         </entry>
        <entry><type>pg_lsn</type></entry>
-       <entry>Create a named point for performing restore (restricted to superusers)</entry>
+       <entry>Create a named point for performing restore (restricted to superusers by default, but other users can be granted EXECUTE to run the function)</entry>
       </row>
       <row>
        <entry>
@@ -17518,21 +17519,21 @@ SELECT set_config('log_statement_stats', 'off', false);
         <literal><function>pg_start_backup(<parameter>label</> <type>text</> <optional>, <parameter>fast</> <type>boolean</> <optional>, <parameter>exclusive</> <type>boolean</> </optional></optional>)</function></literal>
         </entry>
        <entry><type>pg_lsn</type></entry>
-       <entry>Prepare for performing on-line backup (restricted to superusers or replication roles)</entry>
+       <entry>Prepare for performing on-line backup (restricted to superusers by default, but other users can be granted EXECUTE to run the function)</entry>
       </row>
       <row>
        <entry>
         <literal><function>pg_stop_backup()</function></literal>
         </entry>
        <entry><type>pg_lsn</type></entry>
-       <entry>Finish performing exclusive on-line backup (restricted to superusers or replication roles)</entry>
+       <entry>Finish performing exclusive on-line backup (restricted to superusers by default, but other users can be granted EXECUTE to run the function)</entry>
       </row>
       <row>
        <entry>
         <literal><function>pg_stop_backup(<parameter>exclusive</> <type>boolean</>)</function></literal>
         </entry>
        <entry><type>setof record</type></entry>
-       <entry>Finish performing exclusive or non-exclusive on-line backup (restricted to superusers or replication roles)</entry>
+       <entry>Finish performing exclusive or non-exclusive on-line backup (restricted to superusers by default, but other users can be granted EXECUTE to run the function)</entry>
       </row>
       <row>
        <entry>
@@ -17553,7 +17554,7 @@ SELECT set_config('log_statement_stats', 'off', false);
         <literal><function>pg_switch_xlog()</function></literal>
         </entry>
        <entry><type>pg_lsn</type></entry>
-       <entry>Force switch to a new transaction log file (restricted to superusers)</entry>
+       <entry>Force switch to a new transaction log file (restricted to superusers by default, but other users can be granted EXECUTE to run the function)</entry>
       </row>
       <row>
        <entry>
@@ -17821,7 +17822,7 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
         <literal><function>pg_xlog_replay_pause()</function></literal>
         </entry>
        <entry><type>void</type></entry>
-       <entry>Pauses recovery immediately (restricted to superusers).
+       <entry>Pauses recovery immediately (restricted to superusers by default, but other users can be granted EXECUTE to run the function).
        </entry>
       </row>
       <row>
@@ -17829,7 +17830,7 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
         <literal><function>pg_xlog_replay_resume()</function></literal>
         </entry>
        <entry><type>void</type></entry>
-       <entry>Restarts recovery if it was paused (restricted to superusers).
+       <entry>Restarts recovery if it was paused (restricted to superusers by default, but other users can be granted EXECUTE to run the function).
        </entry>
       </row>
      </tbody>

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 105d5415347fd05c4d776a10453c592251b9372d..5ab3accf9d2139d73c0b47ef8bde1a18cf0527b4 100644 (file)
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -2289,7 +2289,8 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
       <entry><type>void</type></entry>
       <entry>
        Reset all statistics counters for the current database to zero
-       (requires superuser privileges)
+       (requires superuser privileges by default, but EXECUTE for this
+       function can be granted to others.)
       </entry>
      </row>
 
@@ -2298,7 +2299,8 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
       <entry><type>void</type></entry>
       <entry>
        Reset some cluster-wide statistics counters to zero, depending on the
-       argument (requires superuser privileges).
+       argument (requires superuser privileges by default, but EXECUTE for
+       this functiion can be granted to others).
        Calling <literal>pg_stat_reset_shared('bgwriter')</> will zero all the
        counters shown in the <structname>pg_stat_bgwriter</> view.
        Calling <literal>pg_stat_reset_shared('archiver')</> will zero all the
@@ -2311,7 +2313,8 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
       <entry><type>void</type></entry>
       <entry>
        Reset statistics for a single table or index in the current database to
-       zero (requires superuser privileges)
+       zero (requires superuser privileges by default, but EXECUTE for this
+       function can be granted to others)
       </entry>
      </row>
 
@@ -2320,7 +2323,8 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
       <entry><type>void</type></entry>
       <entry>
        Reset statistics for a single function in the current database to
-       zero (requires superuser privileges)
+       zero (requires superuser privileges by default, but EXECUTE for this
+       function can be granted to others)
       </entry>
      </row>
     </tbody>

diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 6a2fffc64b1034fb661138989c920be8131b6770..d69a924a7baa864b045c749cf65d3a1cc252e232 100644 (file)
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -66,6 +66,9 @@ nonexclusive_base_backup_cleanup(int code, Datum arg)
  * contains the user-supplied label string (typically this would be used
  * to tell where the backup dump will be stored) and the starting time and
  * starting WAL location for the dump.
+ *
+ * Permission checking for this function is managed through the normal
+ * GRANT system.
  */
 Datum
 pg_start_backup(PG_FUNCTION_ARGS)
@@ -79,11 +82,6 @@ pg_start_backup(PG_FUNCTION_ARGS)
 
    backupidstr = text_to_cstring(backupid);
 
-   if (!superuser() && !has_rolreplication(GetUserId()))
-       ereport(ERROR,
-               (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-          errmsg("must be superuser or replication role to run a backup")));
-
    if (exclusive_backup_running || nonexclusive_backup_running)
        ereport(ERROR,
                (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
@@ -142,17 +140,15 @@ pg_start_backup(PG_FUNCTION_ARGS)
  * Note: this version is only called to stop an exclusive backup. The function
  *       pg_stop_backup_v2 (overloaded as pg_stop_backup in SQL) is called to
  *       stop non-exclusive backups.
+ *
+ * Permission checking for this function is managed through the normal
+ * GRANT system.
  */
 Datum
 pg_stop_backup(PG_FUNCTION_ARGS)
 {
    XLogRecPtr  stoppoint;
 
-   if (!superuser() && !has_rolreplication(GetUserId()))
-       ereport(ERROR,
-               (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-        (errmsg("must be superuser or replication role to run a backup"))));
-
    if (nonexclusive_backup_running)
        ereport(ERROR,
                (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
@@ -179,6 +175,9 @@ pg_stop_backup(PG_FUNCTION_ARGS)
  * Works the same as pg_stop_backup, except for non-exclusive backups it returns
  * the backup label and tablespace map files as text fields in as part of the
  * resultset.
+ *
+ * Permission checking for this function is managed through the normal
+ * GRANT system.
  */
 Datum
 pg_stop_backup_v2(PG_FUNCTION_ARGS)
@@ -205,11 +204,6 @@ pg_stop_backup_v2(PG_FUNCTION_ARGS)
                 errmsg("materialize mode required, but it is not " \
                        "allowed in this context")));
 
-   if (!superuser() && !has_rolreplication(GetUserId()))
-       ereport(ERROR,
-               (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-                (errmsg("must be superuser or replication role to run a backup"))));
-
    /* Build a tuple descriptor for our result type */
    if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
        elog(ERROR, "return type must be a row type");
@@ -285,17 +279,15 @@ pg_stop_backup_v2(PG_FUNCTION_ARGS)
 
 /*
  * pg_switch_xlog: switch to next xlog file
+ *
+ * Permission checking for this function is managed through the normal
+ * GRANT system.
  */
 Datum
 pg_switch_xlog(PG_FUNCTION_ARGS)
 {
    XLogRecPtr  switchpoint;
 
-   if (!superuser())
-       ereport(ERROR,
-               (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-            (errmsg("must be superuser to switch transaction log files"))));
-
    if (RecoveryInProgress())
        ereport(ERROR,
                (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
@@ -312,6 +304,9 @@ pg_switch_xlog(PG_FUNCTION_ARGS)
 
 /*
  * pg_create_restore_point: a named point for restore
+ *
+ * Permission checking for this function is managed through the normal
+ * GRANT system.
  */
 Datum
 pg_create_restore_point(PG_FUNCTION_ARGS)
@@ -320,11 +315,6 @@ pg_create_restore_point(PG_FUNCTION_ARGS)
    char       *restore_name_str;
    XLogRecPtr  restorepoint;
 
-   if (!superuser())
-       ereport(ERROR,
-               (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-                (errmsg("must be superuser to create a restore point"))));
-
    if (RecoveryInProgress())
        ereport(ERROR,
                (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
@@ -546,15 +536,13 @@ pg_xlogfile_name(PG_FUNCTION_ARGS)
 
 /*
  * pg_xlog_replay_pause - pause recovery now
+ *
+ * Permission checking for this function is managed through the normal
+ * GRANT system.
  */
 Datum
 pg_xlog_replay_pause(PG_FUNCTION_ARGS)
 {
-   if (!superuser())
-       ereport(ERROR,
-               (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-                (errmsg("must be superuser to control recovery"))));
-
    if (!RecoveryInProgress())
        ereport(ERROR,
                (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
@@ -568,15 +556,13 @@ pg_xlog_replay_pause(PG_FUNCTION_ARGS)
 
 /*
  * pg_xlog_replay_resume - resume recovery now
+ *
+ * Permission checking for this function is managed through the normal
+ * GRANT system.
  */
 Datum
 pg_xlog_replay_resume(PG_FUNCTION_ARGS)
 {
-   if (!superuser())
-       ereport(ERROR,
-               (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-                (errmsg("must be superuser to control recovery"))));
-
    if (!RecoveryInProgress())
        ereport(ERROR,
                (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index cef90c59be1a86636284c9c11c9fcaa6d500b697..d3cc848ea53c954a437abb114d4d636946abcdaf 100644 (file)
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1005,3 +1005,24 @@ RETURNS jsonb
 LANGUAGE INTERNAL
 STRICT IMMUTABLE
 AS 'jsonb_insert';
+
+-- The default permissions for functions mean that anyone can execute them.
+-- A number of functions shouldn't be executable by just anyone, but rather
+-- than use explicit 'superuser()' checks in those functions, we use the GRANT
+-- system to REVOKE access to those functions at initdb time.  Administrators
+-- can later change who can access these functions, or leave them as only
+-- available to superuser / cluster owner, if they choose.
+REVOKE EXECUTE ON FUNCTION pg_start_backup(text, boolean, boolean) FROM public;
+REVOKE EXECUTE ON FUNCTION pg_stop_backup() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_stop_backup(boolean) FROM public;
+REVOKE EXECUTE ON FUNCTION pg_create_restore_point(text) FROM public;
+REVOKE EXECUTE ON FUNCTION pg_switch_xlog() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_xlog_replay_pause() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_xlog_replay_resume() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_rotate_logfile() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_reload_conf() FROM public;
+
+REVOKE EXECUTE ON FUNCTION pg_stat_reset() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_stat_reset_shared(text) FROM public;
+REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_table_counters(oid) FROM public;
+REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_function_counters(oid) FROM public;

diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 1467355fdda2f9dd48a9baf9a0f9692170443822..41f43749b5020b7de227fdf582f42d74ea029148 100644 (file)
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -1217,6 +1217,9 @@ pgstat_drop_relation(Oid relid)
  * pgstat_reset_counters() -
  *
  * Tell the statistics collector to reset counters for our database.
+ *
+ * Permission checking for this function is managed through the normal
+ * GRANT system.
  * ----------
  */
 void
@@ -1227,11 +1230,6 @@ pgstat_reset_counters(void)
    if (pgStatSock == PGINVALID_SOCKET)
        return;
 
-   if (!superuser())
-       ereport(ERROR,
-               (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-                errmsg("must be superuser to reset statistics counters")));
-
    pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_RESETCOUNTER);
    msg.m_databaseid = MyDatabaseId;
    pgstat_send(&msg, sizeof(msg));
@@ -1241,6 +1239,9 @@ pgstat_reset_counters(void)
  * pgstat_reset_shared_counters() -
  *
  * Tell the statistics collector to reset cluster-wide shared counters.
+ *
+ * Permission checking for this function is managed through the normal
+ * GRANT system.
  * ----------
  */
 void
@@ -1251,11 +1252,6 @@ pgstat_reset_shared_counters(const char *target)
    if (pgStatSock == PGINVALID_SOCKET)
        return;
 
-   if (!superuser())
-       ereport(ERROR,
-               (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-                errmsg("must be superuser to reset statistics counters")));
-
    if (strcmp(target, "archiver") == 0)
        msg.m_resettarget = RESET_ARCHIVER;
    else if (strcmp(target, "bgwriter") == 0)
@@ -1274,6 +1270,9 @@ pgstat_reset_shared_counters(const char *target)
  * pgstat_reset_single_counter() -
  *
  * Tell the statistics collector to reset a single counter.
+ *
+ * Permission checking for this function is managed through the normal
+ * GRANT system.
  * ----------
  */
 void
@@ -1284,11 +1283,6 @@ pgstat_reset_single_counter(Oid objoid, PgStat_Single_Reset_Type type)
    if (pgStatSock == PGINVALID_SOCKET)
        return;
 
-   if (!superuser())
-       ereport(ERROR,
-               (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-                errmsg("must be superuser to reset statistics counters")));
-
    pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_RESETSINGLECOUNTER);
    msg.m_databaseid = MyDatabaseId;
    msg.m_resettype = type;

diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 6f7c40781616cf7980e9de3d8487d1f9f71eb254..ebc7bb388a7b3b66530b8f05ea895d26fcfbe9bd 100644 (file)
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -321,15 +321,13 @@ pg_terminate_backend(PG_FUNCTION_ARGS)
 
 /*
  * Signal to reload the database configuration
+ *
+ * Permission checking for this function is managed through the normal
+ * GRANT system.
  */
 Datum
 pg_reload_conf(PG_FUNCTION_ARGS)
 {
-   if (!superuser())
-       ereport(ERROR,
-               (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-                (errmsg("must be superuser to signal the postmaster"))));
-
    if (kill(PostmasterPid, SIGHUP))
    {
        ereport(WARNING,
@@ -343,15 +341,13 @@ pg_reload_conf(PG_FUNCTION_ARGS)
 
 /*
  * Rotate log file
+ *
+ * Permission checking for this function is managed through the normal
+ * GRANT system.
  */
 Datum
 pg_rotate_logfile(PG_FUNCTION_ARGS)
 {
-   if (!superuser())
-       ereport(ERROR,
-               (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-                (errmsg("must be superuser to rotate log files"))));
-
    if (!Logging_collector)
    {
        ereport(WARNING,