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 3:19 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > 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.
>
> Right. Another way to go at this would be to just ditch the names.

Alright.

> This exact syntax probably wouldn't work (or might not be a good idea)
> because GRANT is so badly overloaded already, but conceptually:
>
> GRANT READ ON DIRECTORY '/home/snowman' TO sfrost;

Yeah, GRANT is overloaded pretty badly and has the unfortunate quality
that it's spec-driven.

> Or maybe some variant of:
>
> ALTER USER sfrost GRANT READ ON DIRECTORY '/home/snowman';

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.

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.

Thanks for continueing to help walk this forward towards a hopefully
useful feature and apologies for the confusion.

Thanks again!

Stephen