\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?

Solution 1

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.

Solution 2

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

Comments

[Craig Ringer]

Look at the screenshot. The user is running the \copy as their own user. +1 anyway for namei command, I've been using Linux for 15 years and didn't know about it.

[user156213]

Thanks for the +1, but they are running it as postgres. See the sudo -i -u postgres

[Craig Ringer]

Whoops, you're right, and I'm blind. Sorry. I plead -ENOCOFFEE where it's morning here in Australia.

[Manish Giri]

@user156213: Thank You for the detailed answer. I just ran the namei command and got the file permissions output. It looks like the Dropbox folder doesn't end with x. Can I still run either of the two solutions you provided after this, or should i change something? Here's a screenshot of the output: i.imgur.com/kQw0fNB.png Also, I'm the only user on this computer (user1).

[Manish Giri]

But the cat command does not throw me an error. Here's a screenshot of the output: i.imgur.com/R6JsNVh.png.. Should I just use the cp command to copy the csv file to \tmp?

[Manish Giri]

@user156213: I manged to setup a new user through the add user command via terminal, with administrator access, such that I would not run into administrative issues that happens when the user is postgres. But I don't know how to connect to Postgres using this new user, instead of sudo -i -u postgres. How does that work?

[Craig Ringer]

@Manish That is because you sudo'd to user postgres to run psql but not cat. If you run as postgres it will.

[user156213]

Ah now I see what the problem is. Your Dropbox installation is cutting off access to postgres. In this case I wouldn't change permissions (since Dropbox might get mad/overwrite them). You don't have to set up a new user account to get this working, what's best is if you (as user1) copy the file outside of the Dropbox folder and then try the \copy command again with the new path. For example you run could cp /home/user1/Dropbox/Development/Databases/SQL/Codeschool/Try‌​SQL/temp_data.csv /home/user1/temp_data.csv, import it with the \copy command, and delete the file if you want.

[Vass]

Will the /tmp folder always be an accessible folder? I have tried it and it does not work.. maybe that folder is placed somewhere else or I need to register it in a config file?

[Craig Ringer]

@Vass /tmp is always accessible by all users, but files and directories within /tmp may have permissions that are stricter