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

Thanks for that.

On Mon, 2009-10-12 at 20:21 -0400, Stephen Frost wrote:
> * Dan Kortschak (dan(dot)kortschak(at)adelaide(dot)edu(dot)au) wrote:
> > $dbh->do("COPY chromosome_data FROM '".chromosomes(\%options)."' CSV");
>
> > 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).

> > 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.

thanks again

Dan