PostgreSQL - inconsistent COPY permissions errors

Question

I am using the EnterpriseDB pgAdmin III (v. 1.12.1) on a Windows 7, 32-bit machine to work with PostgreSQL databases on a remote Linux server. I am logged in as the user postgres, which allows me to access the $PGDATA directory (in this instance, it is found here: /var/lib/pgsql/data/)

If I log into the server via a terminal, run psql, and use the \copy command to import data from csv files into newly created tables, I have no problems.

If I'm in pgAdmin, however, I use the COPY command to import data from csv files into newly created tables.

COPY table_name FROM '/var/lib/pgsql/data/file.csv' 
WITH DELIMITER AS ',' csv header

Sometimes this works fine, other times I get a permissions error:

ERROR: could not open file '/var/lib/pgsql/data/file.csv" for reading: Permission denied SQL state: 42501

It is the inconsistency of the error that is confusing to me. When the error arises, I change the file permission to anywhere from 644 - 777, with no effect. I also try moving the file to other folders, e.g., var/tmp/, also with no effect.

Any ideas?

Answer 1

The problem is the access permissions trough the directories to the file. Postgres user does not have access to your home folder, for example. The answer is to use a folder all users have access like /tmp, or create one with the correct permissions so any user can access/read/write there, a sort of users shared folder.