Re: Add default role 'pg_access_server_files' - Mailing list pgsql-hackers

From Stephen Frost
Subject Re: Add default role 'pg_access_server_files'
Date
Msg-id 20180102120846.GV2416@tamriel.snowman.net
Whole thread Raw
In response to Re: Add default role 'pg_access_server_files'  (Magnus Hagander <magnus@hagander.net>)
Responses Re: Add default role 'pg_access_server_files'
List pgsql-hackers
Magnus,

* Magnus Hagander (magnus@hagander.net) wrote:
> On Sun, Dec 31, 2017 at 8:19 PM, Stephen Frost <sfrost@snowman.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?

> I realize we don't want to go overboard with the number of roles here, but
> at least separating read from write seems useful.

Yeah, these are certainly good suggestions for the COPY case.  I had set
out thihking about pg_read/write_file and we have the read/write
seperation there through the GRANT rights on the functions themselves,
but we don't have that for COPY without different roles.

I'll add those in and publish a new patch soon.

Thanks!

Stephen

Attachment

pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: [HACKERS] why not parallel seq scan for slow functions
Next
From: Alvaro Herrera
Date:
Subject: Re: Better testing coverage and unified coding for plpgsql loops