From 0fdc8495bff02684142a44ab3bc5b18a8ca1863a Mon Sep 17 00:00:00 2001 From: Stephen Frost Date: Fri, 6 Apr 2018 14:47:10 -0400 Subject: 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 --- doc/src/sgml/file-fdw.sgml | 8 +++++--- doc/src/sgml/func.sgml | 27 +++++++++++++++++++-------- doc/src/sgml/ref/copy.sgml | 8 ++++++-- doc/src/sgml/user-manag.sgml | 28 +++++++++++++++++++++++++++- 4 files changed, 57 insertions(+), 14 deletions(-) (limited to 'doc/src') 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 @@ - 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 pg_read_server_files (to use a filename) or + the default role pg_execute_server_programs (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. 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 log_directory can be - accessed. Use a relative path for files in the cluster directory, - and a path matching the log_directory 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 + pg_read_server_files. Use a relative path for files in + the cluster directory, and a path matching the log_directory + configuration setting for log files. + + + + Note that granting users the EXECUTE privilege on the + pg_read_file(), 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 + pg_authid 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. @@ -20140,7 +20151,7 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup()); setof text - 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. @@ -20171,7 +20182,7 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup()); text - 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. @@ -20180,7 +20191,7 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup()); bytea - 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. @@ -20189,7 +20200,7 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup()); record - 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. 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 count by the server, not by the client application, must be executable by the PostgreSQL user. COPY 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 + pg_read_server_files, + pg_write_server_files, + or pg_execute_server_program, since it allows reading + or writing any file or running a program that the server has privileges to + access. 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 @@ -534,6 +534,21 @@ DROP ROLE doomed_role; pg_signal_backend Send signals to other backends (eg: cancel query, terminate). + + pg_read_server_files + Allow reading files from any location the database can access on the server with COPY and + other file-access functions. + + + pg_write_server_files + Allow writing to files in any location the database can access on the server with COPY and + other file-access functions. + + + pg_execute_server_program + 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. + pg_monitor Read/execute various monitoring views and functions. @@ -545,6 +560,16 @@ DROP ROLE doomed_role;
+ + The pg_read_server_files, pg_write_server_files and + pg_execute_server_program 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. + + The pg_monitor, pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables @@ -556,7 +581,8 @@ DROP ROLE doomed_role; 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. -- cgit v1.2.3