2

My question is similar to this one but in Linux Mint 15 (Ubuntu). I've tried the standard COPY (which I used on Windows all the time):

COPY public.bio FROM 'tmp/sisinst_bio.csv' DELIMITERS '|' CSV;

I receive this error:

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

The owner and user of the database is postgres.

Tries

(1) Creating a user that is the same as my user account for Ubuntu (zach) and changing the owner of the database.

(2) Moving the csv to various parts of Ubuntu

sudo cp -r /home/zach/Documents/Postgres91/sisinst_postgresql_bio_test.csv /usr/share/postgresql/9.1

sudo cp -r /home/zach/Documents/Postgres91/sisinst_postgresql_bio_test.csv /tmp

This is really easy but I'm stuck. I'm not sure if this is Postgresql or Ubuntu problem.

Update

Does this have anything to do with this?

As always, thanks all

Community
  • 1
  • 1
Zach
  • 185
  • 12

2 Answers2

2

For the Permissions in the CSV (Right Click -> Properties -> Permissions), the Others was set to None.

To fix this so that Postgres users can use it, I changed Others to Read-only which worked.

Ultimately, it might be better have Postgres users in a Group but that's above me at this point.

I'm open to a better answer.

Zach
  • 185
  • 12
0

Use ls -l /tmp/sisinst_bio.csv to see what the permissions on the file are. Chances are you'll see something like -rw-------, which means it's readable and writeable by the owner, but not readable by anyone else.

If that's the case, try making the CSV file group- and world-readable, using:

chmod g+r,o+r /tmp/sisinst_bio.csv
simonp
  • 2,827
  • 1
  • 14
  • 19
  • you're right on the `-rw-------` but `chmod g+r,o+r /tmp/sisinst_bio.csv` gave me this error:
    `chmod: changing permissions of ‘/tmp/sisinst_bio.csv’: Operation not permitted`.     – Zach May 30 '13 at 23:19
  • First, don't connect to Postgres as postgres unless performing initial setup admin. Second, chown the file to whatever user you are connecting as. If you can't chown or chmod the file, you may have to sudo chown, or copy it to a new location that you can control (if Mint 15 has a special trapping mechanism for /tmp -- which may be the case). – zxq9 May 31 '13 at 01:45
  • Ok, I've changed the ownership of the file using [chown](http://www.cyberciti.biz/faq/how-to-use-chmod-and-chown-command/). I'm also using a new user in Postgres (`zach`). I run `ls -l /home/zach/Documents/Postgres91/sisinst_bio.csv`. The file is owned in this way: `-rw-rw---- 1 zach zach 90722 may 30 12:39` and I'm still receiving the same error in `Postgres`. Does this mean that it is owned by the user `zach` and group `zach`? – Zach May 31 '13 at 15:03
  • Yes - this means it's owned by user `zach` and group `zach`. `-rw-rw----` means it's readable and writable by the owning user, and by any user in the owning group. `chmod` should now work. `chmod o+r /home/zach/Documents/Postgres91/sisinst_bio.csv` will make it readable by any user. – simonp May 31 '13 at 18:16
  • I should probably clarify: Postgres users and UNIX users are not the same thing. When you do a `COPY`, the copy runs on the server as whatever Unix user the server is running as. That's why even though you have a Unix user named "zach" and a Postgres user named "zach", you still can't access the file. Making the file readable by any user will ensure the Pg server can read it. The alternative is to make the file owned by the Pg server user. – simonp May 31 '13 at 18:21