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

Kevin,

Thanks.

* Kevin Grittner (kgrittn@ymail.com) wrote:
> Stephen my correct me on this, but I seem to remember him saying
> that this was part of a general effort to avoid needing to use a
> superuser login for routine tasks that don't fit into the area of
> what a sysadmin would do.  That seems like a laudable goal to me.

Right, and this is one of those things that only a superuser can do now.
I had expected to find other more complicated cases which would require
a generalized "pg_permissions" type of approach but having gone through
the superuser() checks, this is the one case where we really needed a
complex ACL scheme that, in my view at least, warranted a new catalog
table.  Rather than come up with "pg_permissions" and some ugly hacks
to make that work for a variety of object types, I looked to address the
specific case of server-side directory access in a way similar to what
other databases already provide.

> Of course, most or all of what this particular feature would allow
> can be done using superuser-owned SECURITY DEFINER functions, but
> that is sure a lot clumsier and error-prone than being able to say
> that role x can read from directory data/input and role y can write
> to directory data/output.

Exactly.

> That said, Stephen does seem to have some additional specific use
> cases in mind which he hasn't shared with the list; knowing what
> problems we're talking about solving would sure help make
> discussions about the possible solutions more productive.  :-)

It's actually more-or-less the opposite..  As I think I mentioned at
some point earlier, the original ask was to be able to view logs as a
DBA who isn't a superuser, and without having to have those views
delayed or complex cron jobs running to set up access to them.  That's a
*frequently* asked for capability and I don't think this directory type
approach will be the final solution to that specific problem, but it'll
at least get us a lot closer while also providing capabilities that
other databases have and that I've personally wanted for a long time.

In other words, I took the ask and attempted to generalize it out to
cover more use-cases that I've run into which are similar.  While I have
ideas and memories about times when I've wanted this capability for
various use-cases, there's not some pre-defined list that I'm hiding
offline in hopes that no one asks for it, nor is it for some government
check-list.

Since there is evidently interest in this, I'll try to provide some
insight into the times I've run into this previously:

The first time I came across COPY and was frustrated that I had to be a
superuser to use it, period.  Initially, I didn't realize it could do
STDIN/STDOUT, but even once I discovered that, I felt it was unfortunate
that only a superuser could do it server-side, unlike other databases.
This, in my view, is probably the experience of nearly every new user to
PG and COPY and, basically, it sucks.

Later on, I started writing scripts to do server-side copy to avoid
having to marshall data through whatever-client-API-I'm-using (perl,
php, python, etc) and where I couldn't do that due to not being able to
run as a superuser, I ended up doing ugly things in some cases (like
exec'ing out to psql..) because I couldn't just tell the server "pull
this file in".

In some cases, COPY wasn't even supported by the client library, as I
recall.  That's better now, but new languages continue to come out and
often initially support the bare minimum (wasn't ruby initially in this
boat of lacking COPY protocol support initially..?).

Then, when working with Pentaho I came across it again- having to
marshall data through Java and over into PG, and it had to go over a
local TCP connection instead of a unix socket (still the case with our
JDBC driver, no?), primairly to get data into the DB which was out on an
NFS mount in a format that PG could have digested just fine directly or
could have made available via the file_fdw.

Next was the Amazon use-case, which wasn't obvious to me initially but
makes perfect sense now.  They want to allow users to add new i/o
channels and use them but can't let users run as the normal PG
superuser, hence the idea about supporting CREATE TABLESPACE with this
same 'diralias' approach.

The thoughts around permissions related to 'diralias' (chmod, mkdir, ls,
etc) are all just based on what unix provides already.  Similairly,
extending to support large-object import/export along with COPY just
makes sense, as does supporting the file_fdw with this approach, imv.
The file_fdw case is interesting as it's an extension and we'll need to
be able to provide a clear and simple interface to check if the access
is allowed or not which the file_fdw would then leverage.
Thanks,
    Stephen