Re: Add default role 'pg_access_server_files'

On Tue, Jan 2, 2018 at 1:08 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Magnus,
>
> * Magnus Hagander (magnus(at)hagander(dot)net) wrote:
> > On Sun, Dec 31, 2017 at 8:19 PM, Stephen Frost <sfrost(at)snowman(dot)net>
> wrote:
> > > 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...
>
> Ok.
>
> > * execute from read/write, so COPY FROM PROGRAM etc would be a separate
> > role?
>
> Suggestions on a name for this..? pg_server_copy_program?
>

Presumably it would also be used in postgres_fdw, so that seems like a bad
name. Maybe pg_exec_server_command?

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