From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Stephen Frost <sfrost(at)snowman(dot)net> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, Andres Freund <andres(at)2ndquadrant(dot)com>, Adam Brightwell <adam(dot)brightwell(at)crunchydatasolutions(dot)com>, Peter Eisentraut <peter_e(at)gmx(dot)net>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Directory/File Access Permissions for COPY and Generic File Access Functions |
Date: | 2014-10-29 20:38:58 |
Message-ID: | 32323.1414615138@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2014-10-29 20:38:59 | Re: pg_background (and more parallelism infrastructure patches) |
Previous Message | Alvaro Herrera | 2014-10-29 20:11:53 | Re: BRIN indexes - TRAP: BadArgument |