Re: Directory/File Access Permissions for COPY and Generic File Access Functions

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Wed, Oct 15, 2014 at 11:34 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > "Brightwell, Adam" <adam(dot)brightwell(at)crunchydatasolutions(dot)com> writes:
> >> The attached patch for review implements a directory permission system that
> >> allows for providing a directory read/write capability to directories for
> >> COPY TO/FROM and Generic File Access Functions to non-superusers.
> >
> > TBH, this sounds like it's adding a lot of mechanism and *significant*
> > risk of unforeseen security issues in order to solve a problem that we
> > do not need to solve. The field demand for such a feature is just about
> > indistinguishable from zero.
>
> I am also not convinced that we need this. If we need to allow
> non-superusers COPY permission at all, can we just exclude certain
> "unsafe" directories (like the data directory, and tablespaces) and
> let them access anything else?

Wow.. I'd say 'no' to this, certainly. Granularity is required here.
I want to give a non-superuser the ability to slurp data off a specific
NFS mount, not read /etc/passwd..

> Or can we have a whitelist of
> directories stored as a PGC_SUSER GUC? This seems awfully heavyweight
> for what it is.

Hrm, perhaps this would work though..

Allow me to outline a few use-cases which I see for this though and
perhaps that'll help us make progress.

This started out as a request for a non-superuser to be able to review
the log files without needing access to the server. Now, things can
certainly be set up on the server to import *all* logs and then grant
access to a non-superuser, but generally it's "I need to review the log
from X to Y" and not *all* logs need to be stored or kept in PG.

In years past, I've wanted to be able to grant this ability out for
users to do loads without having to transfer the data through the user's
laptop or get them to log onto the Linux box from their Windows desktop
and pull the data in via psql (it's a bigger deal than some might
think..), and then there's the general ETL case where, without this, you
end up running something like Pentaho and having to pass all the data
through Java to get it into the database.

Building on that is the concept of *background* loads, with
pg_background. That's a killer capability, in my view. "Hey, PG, go
load all the files in this directory into this table, but don't make me
have to stick around and make sure my laptop is still connected for the
next 3 hours."

Next, the file_fdw could leverage this catalog to do its own checks and
allow non-superusers to use it, which would be fantastic and gets back
to the 'log file' use-case above.

And then there is the next-level item: CREATE TABLESPACE, which we
already see folks like RDS and others having to hack the codebase to
add as a non-superuser capability. It'd need to be an independently
grantable capability, of course.

Thanks!

Stephen