\copy permission denied while importing CSV to Postgres on Ubuntu

Question

I'm using Postgres on Ubuntu 14.04 and I've been trying to import a csv file to a table in Postgres called 'weather'. I've looked at the question Postgres ERROR: could not open file for reading: Permission denied and tried using the \copy command instead of the regular copy, but I still get the same Permission Denied error.

I'm somewhat hesitant to modify ownership permissions for all files for that directory for all the users (as suggested in the first answer). Here's the copy statement:

\copy weather from '/home/user1/Dropbox/Development/Databases/SQL/Codeschool/TrySQL/temp_data.csv' HEADER DELIMITER ',' CSV

And here's a screenshot of the terminal:

Why is the \copy not working here?

Answer 1

Alternative by terminal with no permission

The pg documentation at NOTES say

The path will be interpreted relative to the working directory of the server process (normally the cluster's data directory), not the client's working directory.

So, gerally, using psql or any client, even in a local server, you have problems ... And, if you're expressing COPY command for other users, eg. at a Github README, the reader will have problems ...

The only way to express relative path with client permissions is using STDIN,

When STDIN or STDOUT is specified, data is transmitted via the connection between the client and the server.

as remembered here:

psql -h remotehost -d remote_mydb -U myuser -c \
   "copy mytable from STDIN with delimiter as ','" \
   < ./relative_path/file.csv

Answer 2

You definitely have to change some permissions because Postgres can't read your file. Postgres is a different user from you, so it can't read your files if you don't give it the right to. The \copy solution would work only if you have a setup where you, not postgres, are the user who runs the psql command.

You could always make a copy of the file, assign permissions for the file to user Postgres in a directory Postgres can execute, and delete the file afterwards, or you could do this:

What you have to change depends on the output of this command (run as user1):

namei -l /home/user1/Dropbox/Development/Databases/SQL/Codeschool/TrySQL/temp_data.csv

(you may need to sudo apt-get install util-linux before running this command, if it isn't already installed)

This command will list the current permissions of the file and all its parent directories so we can find solutions.

Assuming all of the directories have entries ending with "x", like this:

drwxr-xr-x user group filename
drwxr-xr-x
drwxr-xr-x
-rw-------

then either of the two solutions below will work.

1. If you don't want to change permissions for all users and you have sudoer permissions, you can do

   sudo chown /home/user1/Dropbox/Development/Databases/SQL/Codeschool/TrySQL/temp_data.csv postgresql
   

   However, this approach will revoke your access to the file, something you probably don't want. But you can always chown the file back to you after you're done importing it with

   sudo chown /home/user1/Dropbox/Development/Databases/SQL/Codeschool/TrySQL/temp_data.csv user1
   

2. If you don't mind if all users read your file, then you can execute (as user1 and without root permissions)

   chmod a+r /home/user1/Dropbox/Development/Databases/SQL/Codeschool/TrySQL/temp_data.csv
   

   I recommend that you do this solution. It will only change the permissions of that one file so that all the users on your computer can read it. However, although by default in linux most directories can be opened by anyone, there's a chance that this won't work if not all users have the execute permission on your directories.

   Of course, once you've read the file, you can always restrict the permissions again with

   chmod a-r /home/user1/Dropbox/Development/Databases/SQL/Codeschool/TrySQL/temp_data.csv
   

   If neither of these solutions are good for you, please comment with the output of the first command above.

Answer 3

It appears that you are trying to read a file from somebody else's home directory.

Usually this is not possible or permitted by the permissions on their home dir or the directories within it. It's nothing to do with PostgreSQL; you'll find that:

cat /home/user1/Dropbox/Development/Databases/SQL/Codeschool/TrySQL/temp_data.csv

produces the same error.

If your security requirements permit, you can change the permissions on that path. Otherwise, just copy it to /tmp and import it from there.