CODEWITHSUNDEEP
permissionspgadminpostgresql-8.4
Kate T
Kate T

Reputation: 41

PostgreSQL - inconsistent COPY permissions errors

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?

Upvotes: 4

Views: 9539

Answers (3)

varun
varun

Reputation: 4650

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

Notice the backslash before copy, when you run it with back slash it runs with user permissions other wise it just runs as postmaster which in the documentation is deprecated for recent versions of pg :|, anyways this might probably do the trick for ya .

Upvotes: 0

Edgar
Edgar

Reputation: 61

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.

Upvotes: 6

DiAngelo
DiAngelo

Reputation: 220

I think your postgres user still don't have access to your file.

Did you tried the folowing commands ?

chown postgres /var/lib/pgsql/data/file.csv

chmod u+r /var/lib/pgsql/data/file.csv

Upvotes: 0

Related Questions