Re: Add default role 'pg_access_server_files'

On Sun, Dec 31, 2017 at 8:19 PM, Stephen Frost <sfrost(at)snowman(dot)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.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>