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

Robert,

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Tue, Oct 28, 2014 at 11:16 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > There are more capabilities that I've been considering longer-term but
> > wasn't sure if they should be independent or just lumped into the
> > simpler read/write category:
> >
> > read (eg: importing log files, or importing from an NFS mount)
> > write (eg: exporting to NFS mount)
> > tablespace (eg: create a tablespace in a subdir of a directory)
> > create directory (eg: create subdirs)
> > modify permissions (eg: allow users other than pg to read/write/etc)
> > directory listing
> > large-object import/export (might be same as read/write)
> > COPY PIPE
>
> I think it would be a good idea to figure out how this fits together
> and propose a design that covers all the cases you think are
> important, and then see how many of them the community agrees are
> important. I have no problem with incremental commits moving toward
> an agreed-upon design, but it's important that we don't go off in one
> directly and then have to reverse course, because it creates upgrade
> problems for our users.

Certainly.

> To articular my own concerns perhaps a bit better, there are two major
> things I don't like about the whole DIRALIAS proposal. Number one,
> you're creating this SQL object whose name is not actually used for
> anything other than manipulating the alias you created.

I agree that this makes it feel awkward. Peter had an interesting
suggestion to make the dir aliases available as actual aliases for the
commands which they would be relevant to. I hadn't considered that- I
proposed 'diralias' because I didn't like 'directory' since we weren't
actually creating *directories* but rather defining aliases to existing
OS directories in PG. Perhaps it wasn't clear at the outset, but this
is all work-in-progress and not intended to be the one-true-solution
from on-high. Apologies if it came across that way.

> The users are
> still operating on pathnames. That's awfully strange. Number two,
> every other SQL object we have has a name that is one or several
> English words. DIRALIAS does not appear in any dictionary. The
> second objection can be answered by renaming the facility, but the
> first one is not so straightforward.

I do think it's important to support subdirectories (the Amazon use-case
is one where this would be required) and allowing users to specify the
specific file names, so we'd have to come up with a way to combine the
alias and the rest of the fully-qualified path. That might not be too
bad but, to me at least, it seemed more natural to just use the full
path. That was from a sysadmin perspective though, from a DBA
perspective, knowing the rest of the path is probably not all that
interesting and using the alias would be simpler for them.

> > I'll discuss with Adam putting a wiki together which outlines the use
> > cases and rationale for them and hopefully that'll lead into a better
> > discussion about the possible permissions which would make sense to
> > exist for these and that may inform us as to if a GUC-based approach
> > would work. I'm still unsure about using GUCs to define permissions in
> > this way. That feels novel to me for PG to do, but I'll admit that I
> > may just be ignorant or forgetting existing cases where we do that.
>
> Well, there's temp_file_limit, for example. That's not exactly the
> same, but it bears a passing resemblance.

Hrm, yes, that's PG_SUSET and could be set per-user.

> I'm definitely not saying that the GUC-based proposal is perfect. It
> isn't, and if we're going to need a whole bunch of different
> permissions that are all per-directory, that could get ugly in a
> hurry. My points are (1) the community does not have to accept this
> feature just because you propose it, and in fact there's a good
> argument for rejecting it outright, which is that very few users are
> going to get any benefit out of this, and it might end up being a
> whole lot of code; and (2) the pros and cons of accepting this at all,
> and of different designs, need to be debated here, on this list, in an
> open way.

I'd like to think that we're doing (2) now. As for (1), I certainly
feel it's a useful capability and will argue for it, but the community
certainly has the 'final say' on it, of course. I'm optomistic that the
amount of code will be reasonable and that users will benefit from it or
I wouldn't be advocating it, but that's obviously a judgement call and
others will and are certainly entitled to have different opinions.

> I think it would help, on all accounts, to explain why in the world
> we're spending time on this in the first place.

Because I feel it's a valuable feature...? So does Oracle, MySQL, and
the other databases which support it. This isn't the first time it's
come up either, as I pointed out up-thread.

> I have a sneaking
> suspicion this is 1 of N things we need to do to meet some US
> government security standard, and if something like that is the case,
> that could tip the balance toward doing it, or toward a particular
> implementation of the concept.

No, it hasn't got anything to do with NIST or other government
standards. Those standards are much more interested in the general
"reduce the need to be a superuser" concept but there's certainly
nothing in there about directory-level access, nor was it even part of
the original discussion that this idea came out of. If there were
specific standards about this, I'd have pointed them out (as I've done
previously...), because solving those cases are valuable to our
community, in my view.

> From my point of view, if you made a
> list of all of the annoyances of using PostgreSQL and listed them in
> order of importance, you'd burn through a fair amount of paper before
> reaching this one.

I'm not quite sure what to do with this comment. Perhaps it isn't at
the top of anyone's list (not even mine), but I didn't think we rejected
features because the community feels that some other feature is more
important. If we're going to start doing that then we should probably
come up with a list of what features the community wants, prioritize
them, and require that all committers work towards those features to the
exclusion of their own interests, or those of their employers or the
companies they own/run. I hope I've simply misunderstood the
implication here instead.

Thanks,

Stephen