* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> 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.
Ugh, no, I wouldn't want hundreds of file_fdw tables created (and when
would you stop..?). I'm trying to figure out how what you're
suggesting with file_fdw is different from what I was trying to propose
with directory aliases? Wouldn't that have the same issues of hard
links, etc, if the user also has access to the filesystem and that
directory? And if we trust the admin to use protected directories when
setting up file_fdw, why couldn't they do the same with directory
aliases? Perhaps I've misunderstood this suggestion?
> 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.
I agree with this, absolutely. This as a use-case for the directory
aliases concept was more as a "it happens to support this nicely too"
than a final solution to this use-case, which I agree we definitely
could and should do better with, though I don't have any specific
solutions for it.
Clearly, I'd like to provide a solution to this use-case also though, so
if the whole 'directory alias' idea is defunct then I'd love to hear
suggestions on how to provide ad-hoc log file access for auditors via
file_fdws and/or COPY, if anyone has any ideas..
Thanks,
Stephen