Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
1  
are you running SELinux or something like it? –  Jack Douglas Jul 21 '11 at 20:30
    
wiki.ubuntu.com/SELinux –  Jack Douglas Jul 21 '11 at 20:32
    
In other words, SELinux is preventing access? –  Pete Jul 21 '11 at 20:36
4  
"Files named in a COPY command are read or written directly by the server, not by the client application. Therefore, they must reside on or be accessible to the database server machine, not the client. They must be accessible to and readable or writable by the PostgreSQL user (the user ID the server runs as), not the client. COPY naming a file is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access." (postgresql.org/docs/current/static/sql-copy.html) –  Milen A. Radev Jul 21 '11 at 23:09
2  
If the file isn't on the database server, shouldn't that give you a "file not found" error rather than a "permission denied" error? I'm thinking that the PostgreSQL user (the service account?) might not have permission to read that file. –  Mike Sherrill 'Cat Recall' Jul 22 '11 at 11:05

1 Answer 1

up vote 3 down vote accepted
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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.