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

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Wed, Oct 29, 2014 at 6:50 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > This could work though. We could add an array to pg_authid which is a
> > complex type that combines the permission allowed with the directory
> > somehow. Feels like it might get a bit clumsy though.
>
> Sure, I'm just throwing things out to see what sticks. It would be
> helpful to have more input from others on what they like and dislike,
> too; I'm not pretending my input is Gospel.

Agreed- additional input from others would be great. Adam's started the
wiki to hopefully capture these thoughts and be a way to solicit input
from others also. I wonder if we could try something different to get
input from users who don't typically follow -hackers, like maybe get
someone to blog about the concept, point to the wiki, and ask for
feedback? Just a random thought.

I've only glanced at it so far myself and plan a deeper review to add my
own thoughts, but if folks want to look at what Adam's put together
so far, it's here:

https://wiki.postgresql.org/wiki/Directory_Permissions

> > One other thing occured to me while I was considering Peter's idea about
> > using the 'DIRALIAS' name- replicas and/or database migrations.
> > pg_basebackup always really annoyed me that you had to have your
> > tablespace directories set up *exactly* the same way when doing the
> > restore. That stinks. If we actually used the DIRALIAS name then
> > sysadmins could abstract out the location and could handle migrations
> > and/or changes to the filesystem structure without having to bother the
> > DBAs to update their code to the new location. That's not something the
> > other RDBMS's have that I could see, but it strikes me as a nice
> > capability anyway and, well, we're certainly not limited to just
> > implementing what others have.
>
> Of course, any design that stores paths in the system catalogs is
> going to have the problem that the standby will perforce have the same
> configuration as the master.

Yeah, that's a good point, this wouldn't address replicas (until/unless
we allow catalogs to be different some day.. though I guess you'd do
that with logical replication instead) but rather the pg_basebackup /
migration-to-new-system case.

> I'm fuzzy on how you see DIRALIAS helping with tablespace migrations,
> etc. There's no obvious way to make a tablespace definition reference
> an alias rather than a pathname; it's just a filesystem-level symlink.

Sorry, to clarify, I wasn't thinking of tablespaces (which pg_basebackup
now deals with better by allowing you to provide a mapping from the old
to the new) but rather files referenced by a file_fdw table, though it
could be used with COPY also (possibly inside of pl/pgsql, or in client
apps).

Thanks!

Stephen