Postgresql – psql denies access to file when using COPY FROM

csvpostgresqlpsql

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!

Best Answer

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).

Related Question