permission error when populating a table in PostGr

问题:

I know this issue has already been raised by others, but even trying previous suggestions I still get this error...

When I try to populate a table copying from a csv file, I get a permission error.

COPY Eurasia FROM '/Users/Oritteropus/Desktop/eurasia1.csv' CSV HEADER;

ERROR: could not open file "/Users/Oritteropus/Desktop/eurasia1.csv" for reading: Permission denied
SQL state: 42501

As previously suggested in these cases, I changed the permission of the file (chmod 711 eurasia1.csv or chmod a+r eurasia1.csv) and I also changed the user rights with:

ALTER USER postgres WITH SUPERUSER; #where postgres is my user

However, I still get the same error. I also tried to manually change the privileges from pgAdmin but seems avery privilege is already given. I'm working on a Mac Os and I'm using PostGreSQL 9.2.4.

Any suggestion? Thanks

回答1:

The best option is to change and use COPY FROM STDIN as that avoids quite a number of permissions issues.

Alternatively you can make sure that the postgres user can access the file. This rarely better than COPY FROM STDIN however for a couple reasons.

1. COPY TO STDOUT can conceivably corrupt your data. Because this involves file I/O by PostgreSQL if bugs exist in COPY FROM STDIN that could be a problem too.

2. If you are doing it on the server side because of automation/stored proc concerns, this is rarely a win, as you are combining transactional and non-transactional effects. COPY TO STDOUT and COPY FROM STDIN do not have these issues. (For example, you don't have to wonder whether the atime of the inode actually means the file was properly processed).