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

* Dan Kortschak (dan.kortschak@adelaide.edu.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.

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

    Thanks,

        Stephen