On Sun, Dec 31, 2017 at 8:19 PM, Stephen Frost <sfrost@snowman.net> wrote:
Greetings,
This patch adds a new default role called 'pg_access_server_files' which allows an administrator to GRANT to a non-superuser role the ability to access server-side files through PostgreSQL (as the user the database is running as). By itself, having this role allows a non-superuser to use server-side COPY and to use file_fdw (if installed by a superuser and GRANT'd USAGE on it).
Further, this patch moves the privilege check for the remaining misc file functions from explicit superuser checks to the GRANT system, similar to what's done for pg_ls_logdir() and others. Lastly, these functions are changed to allow a user with the 'pg_access_server_files' role to be able to access files outside of the PG data directory.
This follows on and continues what was recently done with the lo_import/export functions. There's other superuser checks to replace with grant'able default roles, but those probably make more sense as independent patches. I continue to be of the opinion that it'd be nice to have more fine-grained control over these functions to limit the access granted, but nothing here prevents that from being done and this at least allows some movement away from having to have roles with superuser access.
Would it make sense to separate out:
* write from read. E.g. a pg_write_server_files/pg_read_server_files? ISTM that will turn into a pretty common request...
* execute from read/write, so COPY FROM PROGRAM etc would be a separate role?
I realize we don't want to go overboard with the number of roles here, but at least separating read from write seems useful.