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

On Tue, Aug 30, 2005 at 11:20:49PM -0400, Greg Stark wrote:
> Scott Marlowe <smarlowe(at)g2switchworks(dot)com> writes:
>
> > Plus, how is the server supposed to KNOW that you have access to the
> > file? psql may know who you are, but the server only knows who you are
> > in the "postgresql" sense, not the OS sense.
>
> My original suggestion was that clients connected via unix domain sockets
> should be allowed to read any file owned by the same uid as the connecting
> client. (Which can be verified using getpeereid/SO_PEERCRED/SCM_CREDS.)
>
> Alternatively and actually even better and more secure would be passing the fd
> directly from the client to the server over the socket. That avoids any
> question of the server bypassing any security restrictions. The client is
> responsible for opening the file under its privileges and handing the
> resulting fd to the server over the socket.
>
> None of this helps for remote clients of course but remote clients can just
> ftp the file to the server anyways and some manual intervention is necessarily
> needed by the DBA to create a security policy for them.

What do people think about the Oracle method where bulk data operations
can only occur in a specified directory? Making that restriction might
address some of the security concerns. I don't think we should change
COPY in such a way that you *have* to use a specified directory, but if
it was an option that helped with the security concerns...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com 512-569-9461