Re: using the "copy from" command to load tables from a

Knepper, Michelle wrote:
> Hi out there,
>
> I'm a first-time user of the "Copy ... From..." command, and I'm trying
> to load a table from a text flat file.
>
> http://www.postgresql.org/docs/7.4/static/sql-copy.html
>
> I don't know if I'm using the command correctly.
> Question:  I run this command as a superuser, and the "copy from"
> command is run like a SQL command on the command line, correct?
> I get the following error.  Any suggestions would be greatly
> appreciated.

The user you're logged in as doesn't matter to "copy from".  It's
the user that the Postgres server is running as that is important.

> [mknepper@barney datafiles]$ psql -U postgres medispan
> Welcome to psql 7.3.4, the PostgreSQL interactive terminal.
>
> Type:  \copyright for distribution terms
>        \h for help with SQL commands
>        \? for help on internal slash commands
>        \g or terminate with semicolon to execute query
>        \q to quit
>
> medispan=# COPY mmw_ade_com FROM
> '/home/mknepper/medispan/datafiles/mmwadecom.txt' WITH DELIMITER '|';
> ERROR:  COPY command, running in backend with effective uid 500, could
> not open file '/home/mknepper/medispan/datafiles/mmwadecom.txt' for
> reading.  Errno = Permission denied (13).

This error is pretty explicit.  The server is running as UID 500, and
UID 500 doesn't have read access to the file in question.  An easy
solution could be to get everyone read access to the file.  If your
security concerns are high, you'll have to work out something better,
but your definately getting hung up on file permissions.

For future reference:
copy runs with the permissions of the server process.
\copy runs with the permissions of the client process.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com