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

Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> If your ETL process can be restricted that much, can't it use file_fdw or
>> some such to access a fixed filename set by somebody with more privilege?

> We currently have the ETL figure out what the filename is on a daily
> basis and by contrasting where it "should" be against what has been
> loaded thus far (which is tracked in tables in the DB) we can figure out
> what need to be loaded.  To do what you're suggesting we'd have to write
> a pl/pgsql function to do the same which runs as a superuser- not ideal,
> but it would be possible.

Well, surely there's a finite set of possible filenames.  But if creating
a bunch of file_fdw servers doesn't float your boat, could we imagine a
variant of file_fdw that allows unprivileged specification of filename
within a directory set by a more-privileged user?  (Directory as a foreign
server property and filename as a table property, perhaps.)  Although the
superuser security definer function solution might work just as well.

>> As for the DBA-access set of use cases, ISTM that most real-world needs
>> for this sort of functionality are inherently a bit ad-hoc, and therefore
>> once you've locked it down tightly enough that it's credibly not
>> exploitable, it's not really going to be as useful as all that.  The
>> nature of an admin job is dealing with unforeseen cases.

> I agree that for the DBA-access set of use-cases (ad-hoc data loads,
> etc), having a role attribute would be sufficient.  Note that this
> doesn't cover the auditor role and log file access use-case that we've
> been discussing though as auditors shouldn't have write access to the
> system.

Log access seems like a sufficiently specialized, yet important, case that
maybe we should provide bespoke features for exactly that.  Aside from
having a clearer idea of the security implications of what we're doing,
specialized code could provide convenience features like automatically
reassembling a series of log files into a single stream.
        regards, tom lane