Re: About "ERROR: must be *superuser* to COPY to or from a file"

"John D. Burger" <john(at)mitre(dot)org> writes:

> >> Well, they would have access to every world readable file on the
> >> system, ie /etc, /usr, /lib, ... most files are world readable. There's
> >> a lot of discussion about this, yet no-one has demonstrated that COPY
> >> FROM STDIN isn't just as good and avoids all the issues entirely.
> >
> > Well they're world-readable. So, uh, huh?
>
> I haven't completely followed the details of this, but I took the point to be
> that the files might be readable for anyone with a real account on the server
> machine, but that doesn't mean they should be accessible to every remote DB
> user.

I was only suggesting using this from a local unix user where you can actually
authoritatively say something about the uid of the connecting user. I
suggested that if the owner of the file matches the uid of the connecting user
(which you can get on a unix domain socket) then there's no reason not to
grant access to the file.

There isn't really any gain to be had from remote users since they have to get
the data to the server one way or another anyways. There's no good reason for
piping it over a libpq connection to be any less efficient than an ftp
connection (though it might be in practice, that's just an engineering problem
to solve).

If you already have files sitting on the server and want to grant access to
them to remote users then I would say using a SECURITY DEFINER function is
exactly what you want. The server doesn't know anything about the remote user
so it definitely needs the dba to tell it to allow access to the files.

--
greg