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

* Peter Eisentraut (peter_e(at)gmx(dot)net) wrote:
> On 10/27/14 7:27 AM, Stephen Frost wrote:
> > * Peter Eisentraut (peter_e(at)gmx(dot)net) wrote:
> >> On 10/16/14 12:01 PM, Stephen Frost wrote:
> >>> This started out as a request for a non-superuser to be able to review
> >>> the log files without needing access to the server.
> >>
> >> I think that can be done with a security-definer function.
> >
> > Of course it can be. We could replace the entire authorization system
> > with security definer functions too.
>
> I don't think that is correct.

Of course it is- you simply have to move all the logic into the
function.

> It's easy to do something with security definer functions if it's single
> purpose, with a single argument, like load this file into this table,
> let these users do it.

The files won't be consistently named and there may be cases to make
ad-hoc runs or test runs. No, it isn't as simple as always being a
single, specific filename and when consider that there needs to be
intelligence about the actual path being specified and making sure that
there can't be '..' and similar, it gets to be a pretty ugly situation
to make our users have to deal with.

> It's not easy to do it with functions if you have many parameters, like
> in a general SELECT statement.

You could define SRFs for every table.

> So I would like to see at least three wildly different use cases for
> this before believing that a security definer function isn't appropriate.

I'm not following this- there's probably 100s of use-cases for this, but
they're all variations n 'read and/or write data server-side instead of
through a front-end connection', which is what the purpose of the
feature is.. I do see this as being useful for COPY, Large Object, and
the file_fdw...

> > I don't view this as an argument
> > against this feature, particularly as we know other systems have it,
> > users have asked for multiple times, and large PG deployments have had
> > to hack around our lack of it.
>
> What other systems have it? Do you have links to their documentation?

MySQL:
http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_file

(note they provide a way to limit access also, via secure_file_priv)

Oracle:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5007.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9013.htm#i2125999

SQL Server:
http://msdn.microsoft.com/en-us/library/ms175915.aspx
(Note: they can actually run as the user connected instead of the SQL DB
server, if Windows authentication is used, which is basically doing
Kerberos proxying unless I'm mistaken; it's unclear how the security is
maintained if it's a SQL server logon user..).

DB2:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dm.doc/doc/c0004589.html?cp=SSEPGG_9.7.0

etc...

Thanks,

Stephen