Postgres Pro
Downloads
Home   >   mailing lists

Re: About "ERROR: must be *superuser* to COPY to or from a file" - Mailing list pgsql-general

From Doug Bloebaum
Subject Re: About "ERROR: must be *superuser* to COPY to or from a file"
Date
Msg-id caa2de8a05083105584f975849@mail.gmail.com
Whole thread Raw
In response to Re: About "ERROR: must be *superuser* to COPY to or from a file"  ("Jim C. Nasby" <jnasby@pervasive.com>)
List pgsql-general
Tree view
Jim Nasby asked:

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

Oracle's new (well, since 9i) DIRECTORY object (see http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_5007.htm#sthref4678) has proven itself to be a flexible approach for me.

A privileged account creates the DIRECTORY object like so:

CREATE OR REPLACE DIRECTORY my_dir AS '/var/tmp/my_dir';

and then grants access to it:

GRANT READ ON my_dir to db_username;

I'd envision the COPY command using the DIRECTORY object something like:

COPY my_table FROM my_dir:'file.txt';

pgsql-general by date:

Previous
From: Tino Wildenhain
Date:
Subject: Re: How do I copy part of table from db1 to db2 (and
Next
From: "Bjoern A. Zeeb"
Date:
Subject: user defined type, plpgsql function and NULL