Re: Proposing COPY .. WITH PERMISSIVE

On Wed, Sep 2, 2015 at 1:19 PM, Stefan Kaltenbrunner <
stefan(at)kaltenbrunner(dot)cc> wrote:

> On 09/02/2015 10:10 PM, dinesh kumar wrote:
> > On Tue, Sep 1, 2015 at 10:58 PM, Stefan Kaltenbrunner
> > <stefan(at)kaltenbrunner(dot)cc <mailto:stefan(at)kaltenbrunner(dot)cc>> wrote:
> >
> > On 07/25/2015 03:38 AM, dinesh kumar wrote:
> > >
> > >
> > > On Fri, Jul 24, 2015 at 10:22 AM, Robert Haas <
> robertmhaas(at)gmail(dot)com <mailto:robertmhaas(at)gmail(dot)com>
> > > <mailto:robertmhaas(at)gmail(dot)com <mailto:robertmhaas(at)gmail(dot)com>>>
> wrote:
> > >
> > > On Thu, Jul 23, 2015 at 8:15 PM, dinesh kumar
> > > <dineshkumar02(at)gmail(dot)com <mailto:dineshkumar02(at)gmail(dot)com>
> > <mailto:dineshkumar02(at)gmail(dot)com <mailto:dineshkumar02(at)gmail(dot)com>>>
> > wrote:
> > > > On Thu, Jul 23, 2015 at 9:21 AM, Robert Haas
> > > <robertmhaas(at)gmail(dot)com <mailto:robertmhaas(at)gmail(dot)com>
> > <mailto:robertmhaas(at)gmail(dot)com <mailto:robertmhaas(at)gmail(dot)com>>>
> wrote:
> > > >>
> > > >> On Thu, Jul 23, 2015 at 12:19 PM, dinesh kumar
> > > <dineshkumar02(at)gmail(dot)com <mailto:dineshkumar02(at)gmail(dot)com>
> > <mailto:dineshkumar02(at)gmail(dot)com <mailto:dineshkumar02(at)gmail(dot)com>>>
> > > >> wrote:
> > > >> > Sorry for my unclear description about the proposal.
> > > >> >
> > > >> > "WITH PERMISSIVE" is equal to our existing behavior. That
> > is, chmod=644
> > > >> > on
> > > >> > the created files.
> > > >> >
> > > >> > If User don't specify "PERMISSIVE" as an option, then the
> > chmod=600 on
> > > >> > created files. In this way, we can restrict the other
> > users from reading
> > > >> > these files.
> > > >>
> > > >> There might be some benefit in allowing the user to choose
> the
> > > >> permissions, but (1) I doubt we want to change the default
> > behavior
> > > >> and (2) providing only two options doesn't seem flexible
> > enough.
> > > >>
> > > >
> > > > Thanks for your inputs Robert.
> > > >
> > > > 1) IMO, we will keep the exiting behavior as it is.
> > > >
> > > > 2) As the actual proposal talks about the permissions of
> > group/others. So,
> > > > we can add few options as below to the WITH clause
> > > >
> > > > COPY
> > > > ..
> > > > ..
> > > > WITH
> > > > [
> > > > NO
> > > > (READ,WRITE)
> > > > PERMISSION TO
> > > > (GROUP,OTHERS)
> > > > ]
> > >
> > > If we're going to do anything here, it should use COPY's
> > > extensible-options syntax, I think.
> > >
> > >
> > > Thanks Robert. Let me send a patch for this.
> >
> >
> > how are you going to handle windows or unix ACLs here?
> > Its permission model is quite different and more powerful than
> (non-acl
> > based) unix in general, handling this in a flexible way might soon
> get
> > very complicated and complex for limited gain...
> >
> >
> > Hi Stefan,
> >
> > I had the same questions too. But, I believe, our initdb works in these
> > cases, after creating the data cluster. Isn't ?
>
> maybe - but having a fixed "default" is very different from baking a
> classic unix permission concept of user/group/world^others into actual
> DDL or into a COPY option. The proposed syntax might make some sense to
> a admin used to a unix style system but it is likely utterly
> incomprehensible to somebody who is used to (windows style) ACLs.
>
> I dont have a good answer on what to do else atm but I dont think we
> should embedded traditional/historical unix permission models in our
> grammer unless really really needed..
>

Yes, I agree with you. We shouldn't embed the unix style access model into
COPY.

COPY's default behaviour umask 644 on output files, which is giving read
access to other users.
I see, there is a good reason behind it. Also, it would be good to have a
control on the READ ACCESS of a file, where we can secure our dump files,
from the non instance ownership users.

Adding a trivial patch to control this read ACL.

--Sample Test Case

--Default behaviour
postgres=# COPY test_table TO '/tmp/readacs.txt';
COPY 1000

$ ls -l /tmp/readacs.txt
-rw-r--r-- /tmp/test1.txt

--With applied patch
postgres=# COPY test_table TO '/tmp/noreadacs.txt' NO READ ACCESS;
COPY 1000

$ ls -l /tmp/noreadacs.txt
-rw------- /tmp/noreadacs.txt

We can also use "PROGRAM 'cat > Output.csv' " to achieve this "NO READ
ACCESS", since the program is always running as a instance owner.

Let me know your inputs and thoughts.

Stefan
>

--

Regards,
Dinesh
manojadinesh.blogspot.com