Add default roles for file/program access

This patch adds new default roles named 'pg_read_server_files',
'pg_write_server_files', 'pg_execute_server_program' which
allow an administrator to GRANT to a non-superuser role the ability to
access server-side files or run programs through PostgreSQL (as the user
the database is running as).  Having one of these roles allows a
non-superuser to use server-side COPY to read, write, or with a program,
and to use file_fdw (if installed by a superuser and GRANT'd USAGE on
it) to read from files or run a program.

The existing misc file functions are also changed to allow a user with
the 'pg_read_server_files' default role to read any files on the
filesystem, matching the privileges given to that role through COPY and
file_fdw from above.

Reviewed-By: Michael Paquier
Discussion: https://postgr.es/m/20171231191939.GR2416%40tamriel.snowman.net

4 files changed, 57 insertions, 14 deletions

diff --git a/doc/src/sgml/file-fdw.sgml b/doc/src/sgml/file-fdw.sgml
index e2598a07da1..955a13ab7d9 100644
--- a/doc/src/sgml/file-fdw.sgml
+++ b/doc/src/sgml/file-fdw.sgml
@@ -186,9 +186,11 @@
  </para>
 
  <para>
-  Changing table-level options requires superuser privileges, for security
-  reasons: only a superuser should be able to control which file is read
-  or which program is run.  In principle non-superusers could be allowed to
+  Changing table-level options requires being a superuser or having the privileges
+  of the default role <literal>pg_read_server_files</literal> (to use a filename) or
+  the default role <literal>pg_execute_server_programs</literal> (to use a program),
+  for security reasons: only certain users should be able to control which file is
+  read or which program is run.  In principle regular users could be allowed to
   change the other options, but that's not supported at present.
  </para>
 
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6257563eaad..a86d3f40f17 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20119,10 +20119,21 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
     linkend="functions-admin-genfile-table"/> provide native access to
     files on the machine hosting the server. Only files within the
     database cluster directory and the <varname>log_directory</varname> can be
-    accessed.  Use a relative path for files in the cluster directory,
-    and a path matching the <varname>log_directory</varname> configuration setting
-    for log files.  Use of these functions is restricted to superusers
-    except where stated otherwise.
+    accessed unless the user is granted the role
+    <literal>pg_read_server_files</literal>.  Use a relative path for files in
+    the cluster directory, and a path matching the <varname>log_directory</varname>
+    configuration setting for log files.
+   </para>
+
+   <para>
+    Note that granting users the EXECUTE privilege on the
+    <function>pg_read_file()</function>, or related, functions allows them the
+    ability to read any file on the server which the database can read and
+    that those reads bypass all in-database privilege checks.  This means that,
+    among other things, a user with this access is able to read the contents of the
+    <literal>pg_authid</literal> table where authentication information is contained,
+    as well as read any file in the database.  Therefore, granting access to these
+    functions should be carefully considered.
    </para>
 
    <table id="functions-admin-genfile-table">
@@ -20140,7 +20151,7 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
        </entry>
        <entry><type>setof text</type></entry>
        <entry>
-        List the contents of a directory.
+        List the contents of a directory.  Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
        </entry>
       </row>
       <row>
@@ -20171,7 +20182,7 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
        </entry>
        <entry><type>text</type></entry>
        <entry>
-        Return the contents of a text file.
+        Return the contents of a text file.  Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
        </entry>
       </row>
       <row>
@@ -20180,7 +20191,7 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
        </entry>
        <entry><type>bytea</type></entry>
        <entry>
-        Return the contents of a file.
+        Return the contents of a file.  Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
        </entry>
       </row>
       <row>
@@ -20189,7 +20200,7 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
        </entry>
        <entry><type>record</type></entry>
        <entry>
-        Return information about a file.
+        Return information about a file.  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/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index af2a0e91b9a..344d391e4aa 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -444,8 +444,12 @@ COPY <replaceable class="parameter">count</replaceable>
     by the server, not by the client application, must be executable by the
     <productname>PostgreSQL</productname> user.
     <command>COPY</command> naming a file or command is only allowed to
-    database superusers, since it allows reading or writing any file that the
-    server has privileges to access.
+    database superusers or users who are granted one of the default roles
+    <literal>pg_read_server_files</literal>,
+    <literal>pg_write_server_files</literal>,
+    or <literal>pg_execute_server_program</literal>, since it allows reading
+    or writing any file or running a program that the server has privileges to
+    access.
    </para>
 
    <para>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 94fd4ebf582..81b44a8c417 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -535,6 +535,21 @@ DROP ROLE doomed_role;
        <entry>Send signals to other backends (eg: cancel query, terminate).</entry>
       </row>
       <row>
+       <entry>pg_read_server_files</entry>
+       <entry>Allow reading files from any location the database can access on the server with COPY and
+       other file-access functions.</entry>
+      </row>
+      <row>
+       <entry>pg_write_server_files</entry>
+       <entry>Allow writing to files in any location the database can access on the server with COPY and
+       other file-access functions.</entry>
+      </row>
+      <row>
+       <entry>pg_execute_server_program</entry>
+       <entry>Allow executing programs on the database server as the user the database runs as with
+       COPY and other functions which allow executing a server-side program.</entry>
+      </row>
+      <row>
        <entry>pg_monitor</entry>
        <entry>Read/execute various monitoring views and functions.
        This role is a member of <literal>pg_read_all_settings</literal>,
@@ -546,6 +561,16 @@ DROP ROLE doomed_role;
    </table>
 
   <para>
+  The <literal>pg_read_server_files</literal>, <literal>pg_write_server_files</literal> and
+  <literal>pg_execute_server_program</literal> roles are intended to allow administrators to have
+  trusted, but non-superuser, roles which are able to access files and run programs on the
+  database server as the user the database runs as.  As these roles are able to access any file on
+  the server filesystem, they bypass all database-level permission checks when accessing files
+  directly and they could be used to gain superuser-level access, therefore care should be taken
+  when granting these roles to users.
+  </para>
+
+  <para>
   The <literal>pg_monitor</literal>, <literal>pg_read_all_settings</literal>,
   <literal>pg_read_all_stats</literal> and <literal>pg_stat_scan_tables</literal>
   roles are intended to allow administrators to easily configure a role for the
@@ -556,7 +581,8 @@ DROP ROLE doomed_role;
 
   <para>
   Care should be taken when granting these roles to ensure they are only used where
-  needed to perform the desired monitoring.
+  needed and with the understanding that these roles grant access to privileged
+  information.
   </para>
 
   <para>