Обсуждение: `must be superuser to COPY to or from a file' - using perl DBI - approaches to work around this

Hi,

I'm using the perl DBI module to interface with Pg, generating a number
of tables and then loading them into a postgres database (this is to
automate a previously psql-based setup).

One instance of loading the data looks like this, but I am only able to
do this as a superuser (this is possible for me, but I would like to
avoid it):

$dbh->do("COPY chromosome_data FROM '".chromosomes(\%options)."' CSV");

Now either I can call psql from perl (missing the point of using DBI), I
can make the user a superuser (which I would like to avoid for safety
reasons) or I can try to figure out some way of IPC to get STDOUT from
the perl to be read into postgres via STDIN (this seems unnecessarily
complicated).

Does anyone have any suggestions (the least bad of the options above
seems to be to use psql, but I think that is ugly)?

Also, can anyone suggest why it is possible to create a database but not
COPY to/from a file as a non-superuser?

thanks
Dan

* 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

Thanks for that.

On Mon, 2009-10-12 at 20:21 -0400, Stephen Frost wrote:
> * 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.
>

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

On Tue, Oct 13, 2009 at 11:10:12AM +1030, Dan Kortschak wrote:
> On Mon, 2009-10-12 at 20:21 -0400, Stephen Frost wrote:
> > 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).

Unless perl is doing some very funky stuff I'd expect you'll be waiting
for the disks most of the time, Perl will just be shoving blocks of data
around and this is fast.  If performance is really your thing then C may
help.

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

"stdin" effectively just means data from the client, the filesystem
would be from "inside" the server and hence in the presence of a
malicious client letting it do stuff with its own query seems OK whereas
the server's filesystem is an authority you probably don't want to go
spreading too widely and hence is limited to userusers.

--
  Sam  http://samason.me.uk/

* 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

Thanks again.

On Mon, 2009-10-12 at 21:14 -0400, Stephen Frost wrote:
> > 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?

Sorry, question mark left out - that is how I have gone.

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

Yeah, didn't think of that. Thanks.

On Tue, 2009-10-13 at 02:13 +0100, Sam Mason wrote:
> Unless perl is doing some very funky stuff I'd expect you'll be waiting
> for the disks most of the time, Perl will just be shoving blocks of data
> around and this is fast.  If performance is really your thing then C may
> help.

See comment above - more of a question than a statement.

> "stdin" effectively just means data from the client, the filesystem
> would be from "inside" the server and hence in the presence of a
> malicious client letting it do stuff with its own query seems OK whereas
> the server's filesystem is an authority you probably don't want to go
> spreading too widely and hence is limited to userusers.

That makes sense - thanks for the explanation.

cheers all