4

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?

casperOne
  • 73,706
  • 19
  • 184
  • 253
Kate T
  • 41
  • 1
  • 3
  • getting same problem , changed permission to 777 , user to postgres, tried with \ option too, but not able to write anything on file – Satish Sharma Apr 24 '13 at 06:38

3 Answers3

6

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.

Edgar
  • 61
  • 1
  • 2
  • this is the best answer regarding the subject. Solved my problem. – Digao Mar 17 '17 at 11:52
  • Yup, copied the files to `/tmp` and adjusted my `COPY FROM` statements appropriately. Not sure if this is a production ready strategy but worked awesome for the one off thing I had to do. Thank you! – Pat Jones May 01 '21 at 19:30
0
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 .

varun
  • 4,522
  • 33
  • 28
  • I probably need to know more from you to answer anything useful, if you just need to get things moving try copying the source file at C:\Users\Public\Documents – varun May 08 '13 at 09:37
0

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

DiAngelo
  • 220
  • 1
  • 5