Re: `must be superuser to COPY to or from a file' - using perl DBI - approaches to work around this

* Dan Kortschak (dan.kortschak@adelaide.edu.au) wrote:
> On Mon, 2009-10-12 at 20:21 -0400, Stephen Frost wrote:
> > > Does anyone have any suggestions (the least bad of the options above
> > > seems to be to use psql, but I think that is ugly)?
> >
> > perldoc DBD::Pg
> >
> > Read the 'COPY support' section.
> >
>
> Seems like the way to go, though it will be significantly slower than
> psql or superuser reads (a couple of tables have ~10s-100sM rows).

Erm, really?  You've tested that and found it to be that much slower?

> > > Also, can anyone suggest why it is possible to create a database but not
> > > COPY to/from a file as a non-superuser?
> >
> > When a COPY statement which references a file is sent to the backend,
> > the *backend* PG process will try to open the file and read from it-
> > hence you have to be a PG superuser.  The '\copy' that psql provides
> > actually sends a 'COPY .. FROM STDIN' to the server, just like the
> > DBD::Pg COPY support.
>
> Yeah sure, I understand that, I was just wondering about the reasons for
> making that decision - the relative danger of creation and read from
> stdin vs read from a file.

Being able to read from any file the *unix* PG user can read from means
you can access any file in the database..  Pretty serious from a
security standpoint.  Not sure what you're expecting here.

    Stephen