psql denies access to file when using COPY FROM

Question

When I do this:

COPY "mytable" FROM '/my/file.csv' WITH DELIMITER AS ',' CSV;

On this:

-rw-r--r-- 1 peter peter 54819176 2011-07-21 13:17 file.csv

psql tells me this:

ERROR:  could not open file "/my/file.csv" for reading: Permission denied

How can I read my file?? Thanks!

--- Update

It looks like something called apparmor is installed by default in Ubuntu.
Seems to have the same functionality as SELinux, mentioned in the comments.

--- Update

After removing apparmor, I still have the same problem. selinux is not installed.

Regarding the comment below about access, the COPY FROM is being run from a superuser account (it gives a different error message if not) and the file permissions, copied above, I understand as "readable by everyone".

--- Update

I tried to get to the file under the postgres user. It gets stuck at a particular place in the tree

drwxr--r-- 6 peter peter 4096 2011-04-14 14:03 phm

postgres@dexter:/home/peter/PyPacks$ cd phm

bash: cd: phm: Permission denied

I suppose I will just put the file somewhere else, but this is strange!

Answer 1

drwxr--r-- 6 peter peter 4096 2011-04-14 14:03 phm

postgres@dexter:/home/peter/PyPacks$ cd phm

bash: cd: phm: Permission denied

Directories need to be executable to be able to cd into them (or use files within them). All the sub-directory from / to the /home/peter/PyPacks/phm need to be executable by the user you want to use for this to work.

Try at least chmod 711 phm (which should give you rwx--x--x), or perhaps chmod 755 phm (rwxr-xr-x). r for directories is the ability to list their content, not to go into them (or use files/directories within them).

Answer 2

Assuming the psql command-line tool, you may use \copy instead of copy.

\copy opens the file and feeds the contents to the server, whereas copy tells the server the open the file itself and read it, which may be problematic permission-wise, or even impossible if client and server run on different machines with no file sharing in-between.

Under the hood, \copy is implemented as COPY FROM stdin and accepts the same options than the server-side COPY.

from https://stackoverflow.com/questions/19463074/postgres-error-could-not-open-file-for-reading-permission-denied

Answer 3

On Linux, the user postgres must have the right to read the files and execute the directories. It also needs a restart of the PSQL server.

Answer 4

If you’re on a Mac place the csv file on ‘/tmp’ directory. In this case, the file would be accessible by all users such as pgadmin.