Postgres ERROR: could not open file for reading: Permission denied

Question

Computer: Mac OS X, version 10.8 Database: Postgres

Trying to import csv file into postgres.

pg> copy items_ordered from '/users/darchcruise/desktop/items_ordered.csv' with CSV;
ERROR:  could not open file "/users/darchcruise/desktop/items_ordered.csv" for reading: Permission denied

Then I tried

$> chown postgres /users/darchcruise/desktop/items_ordered.csv
chown: /users/darchcruise/desktop/items_ordered.csv: Operation not permitted

Lastly, I tried

$> ls -l
-rw-r--r--  1 darchcruise  staff      1016 Oct 18 21:04 items_ordered.csv

Any help is much appreciated!

The `postges` server does not have read access in the directory where the file is stored. You must be root to change the user:group to `postgres`. (the `postgres` users must have read access on all directories above where the file is located [or all directories must be world-readable with the `x` execute bit set -- which governs "descend into" for directories]) If your `/tmp` is on `tmpfs`, `/tmp` won't work. Alternative, create a dedicated directory for `postgres` to read from, e.g. as root `mkdir -p /usr/local/postgres` and `chown postgres:postgres /usr/local/postgres` and put the file there — David C. Rankin, May 08 '23 at 19:12

score 151 · Answer 1 · answered Oct 19 '13 at 13:31

151

Assuming the psql command-line tool, you may use \copy instead of copy.

\copy opens the file and feeds the contents to the server, whereas copy tells the server the open the file itself and read it, which may be problematic permission-wise, or even impossible if client and server run on different machines with no file sharing in-between.

Under the hood, \copy is implemented as COPY FROM stdin and accepts the same options than the server-side COPY.

answered Oct 19 '13 at 13:31

Daniel Vérité

58,074
15
129
156

4

I wish I could mark this answer as useful every time I inevitably forget the `\\` and end up back here. Please accept my sincere thanks in-stead. – J.M. Janzen May 25 '18 at 14:11
3

Yeah, this should be the accepted answer. @J.M.Janzen, you can favorite the thread by clicking the star next to the question. – Raydot Jun 18 '18 at 20:19

user637338 · Answer 2 · 2016-05-02T17:06:16.743

91

Copy the CSV file to /tmp

For me this solved the issue.

edited May 02 '16 at 17:06

answered Apr 27 '15 at 15:31

user637338

2,565
1
25
26

score 23 · Accepted Answer · answered Oct 19 '13 at 06:43

23

chmod a+rX /users/darchcruise/ /users/darchcruise/desktop /users/darchcruise/desktop/items_ordered.csv

This will change access rights for your folder. Note that everyone will be able to read your file. You can't use chown being a user without administrative rights. Also consider learning umask to ease creation of shared files.

answered Oct 19 '13 at 06:43

Basilevs

22,440
15
57
102

Hi, a little one liner that would do this task for you: a=`pwd`;NumOfSlashes=$((`echo $a | sed 's/[^/]//g' | wc -c` - 1 )); for ((i=1;i<=$NumOfSlashes;i++)); do chmod a+rX $a; a=`dirname $a`;done; – Roy Jun 17 '15 at 01:11

score 22 · Answer 4 · answered Sep 28 '17 at 20:18

22

Copy your CSV file into the /tmp folder

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.

answered Sep 28 '17 at 20:18

Antonio

689
6
4

1

Elegant. No permission change. No fear. No hassle. – madhairsilence Feb 13 '19 at 09:59
What do you mean by "COPY naming a file"? – SametSahin Oct 04 '22 at 12:00

score 5 · Answer 5 · answered Dec 14 '15 at 11:50

I had the issue when I was trying to export data from a remote server into the local disk. I hadn't realised that SQL copy actually is executed on the server and that it tries to write to a server folder. Instead the correct thing to do was to use \copy which is the psql command and it writes to the local file system as I expected. http://www.postgresql.org/message-id/CAFjNrYsE4Za_KWzmfgN1_-MG7GTw_vpMRxPk=OEjAiLqLskxdA@mail.gmail.com

Perhaps that might be useful to someone else too.

score 3 · Answer 6 · answered Jul 22 '15 at 16:42

3

Another way to do this, if you have pgAdmin and are comfortable using the GUI is to go the table in the schema and right click on the table you wish to import the file to and select "Import" browse your computer for the file, select the type your file is, the columns you want the data to be imputed into, and then select import.

That was done using pgAdmin III and the 9.4 version of PostgreSQL

answered Jul 22 '15 at 16:42

Jeff Spicoli

280
5
13

This option no longer exists on pgAdmin III – Ojonugwa Jude Ochalifu May 23 '18 at 10:34
1

This worked like a charm. By far the easiest way to accomplish the objective. – Jul 20 '21 at 11:50

score 3 · Answer 7 · answered Feb 07 '19 at 15:56

3

I resolved the same issue with a recursive chown on the parent folder:

sudo chown -R postgres:postgres /home/my_user/export_folder

(my export being in /home/my_user/export_folder/export_1.csv)

answered Feb 07 '19 at 15:56

Benjamin Crouzier

40,265
44
171
236

Dinesh Gurjar · Answer 8 · 2019-05-07T06:10:42.767

3

for macbook first i opened terminal then type

open /tmp

or in finder directory you directly enter command+shift+g then type /tmp in go to the folder.

it opens temp folder in finder. then i paste copied csv file into this folder.then again i go to postgres terminal and typed below command and then it is copied my csv data into db table

\copy recharge_operator FROM '/private/tmp/operator.csv' DELIMITER ',' CSV;

edited May 07 '19 at 06:10

answered May 01 '19 at 05:50

Dinesh Gurjar

498
5
17

Why does this work? and does it mean all the CSV files we want to import has to be copied into this folder from now on? – Frankdroid7 Aug 19 '23 at 23:19

score 2 · Answer 9 · edited Feb 09 '20 at 15:47

2

COPY your table (Name, Latitude, Longitude) FROM 'C:\Temp\your file.csv' DELIMITERS ',' CSV HEADER;

Use c:\Temp\"Your File"\.

edited Feb 09 '20 at 15:47

double-beep

5,031
17
33
41

answered Feb 09 '20 at 15:46

Kalpana

103
5

This solution throws the same error for me. ``` enceladus=# COPY master_plan enceladus-# FROM 'C:\Windows\Temp\master_plan.csv' enceladus-# WITH DELIMITER ',' HEADER CSV; ERROR: could not open file "C:\Windows\Temp\master_plan.csv" for reading: Pe rmission denied – Andrew McCartney Mar 14 '22 at 18:35

score 1 · Answer 10 · answered Mar 03 '17 at 11:36

1

For me it worked to simply to add sudo (or run as root) for the chown command:

sudo chown postgres /users/darchcruise/desktop/items_ordered.csv

answered Mar 03 '17 at 11:36

annakeuchenius

195
2
8

score 1 · Answer 11 · edited Aug 08 '19 at 11:56

1

You must grant the pg_read_server_files permission to the user if you are not using postgres superuser.

Example:

GRANT pg_read_server_files TO my_user WITH ADMIN OPTION;

edited Aug 08 '19 at 11:56

gehbiszumeis

3,525
4
24
41

answered Aug 08 '19 at 10:54

ZUIBAFI

11
2

Executing this gave me `ERROR: role "pg_read_server_files" does not exist` – SametSahin Jul 26 '20 at 16:33

score 1 · Answer 12 · edited Nov 04 '22 at 14:23

1

I just copied the source csv file to another folder in which you have more permissions (C:/temp), and it worked fine.

edited Nov 04 '22 at 14:23

Ognyan Dimitrov

6,026
1
48
70

answered Oct 25 '21 at 14:43

Zatman

144
2

score 0 · Answer 13 · answered Apr 28 '19 at 21:07

0

just in case you're facing this problem under windows 10 , add the group of users "youcomputer\Users" on the security Tab and grant it full control , that solved my issue

answered Apr 28 '19 at 21:07

Benderradji Khireddine

69
1
6

Downvote, because it gives me: `"MYCOMPUTERNAME\Users" could not be found`. In the end, this is not the right advice (anymore?). You need to use the pgAdmin import menu instead. For "how", see [Data Will not import into PG Admin](https://stackoverflow.com/questions/67153045/data-will-not-import-into-pg-admin/67164155#67164155). For "why", see [Providing postgres windows system permission for copy (windows 8)](https://stackoverflow.com/a/22598261/11154841). – questionto42 May 31 '21 at 17:22

score 0 · Answer 14 · answered Jul 03 '19 at 12:54

I had the same error message but was using psycopg2 to communicate with PostgreSQL. I fixed the permission issues by using the functions copy_from and copy_expert that will open the file on the client side as the user running the python script and feed the data to the database over STDIN.

Refer to this link for further information.

score 0 · Answer 15 · answered May 02 '20 at 07:45

This answer is only for Linux Beginners.

Assuming initially the DB user didn't have file/folder(directory) permission on the client side.

Let's constrain ourselves to the following:

User: postgres

Purpose: You wanted to (write to / read from) a specific folder

Tool: psql

Connected to a specific database: YES

FILE_PATH: /home/user/training/sql/csv_example.csv

Query: \copy (SELECT * FROM table_name TO FILE_PATH, DELIMITER ',' CSV HEADER;

Actual Results: After running the query you got an error : Permission Denied

Expected Results: COPY COUNT_OF_ROWS_COPIED

Here are the steps I'd follow to try and resolve it.

Confirm the FILE_PATH permissions on your File system.

Inside a terminal to view the permissions for a file/folder you need to long list them by entering the command ls -l.

The output has a section that shows sth like this -> drwxrwxr-x Which is interpreted in the following way:

TYPE | OWNER RIGHTS | GROUP RIGHTS | USER RIGHTS

rwx (r: Read, W: Write, X: Execute)

TYPE (1 Char) = d: directory, -: file

OWNER RIGHTS (3 Chars after TYPE)

GROUP RIGHTS (3 Chars after OWNER)

USER RIGHTS (3 Chars after GROUP)

If permissions are not enough (Ensure that a user can at least enter all folders in the path you wanted path) - x.

This means for FILE_PATH, All the directories (home , user, training, sql) should have at least an x in the USER RIGHTS.

Change permissions for all parent folders that you need to enter to have a x. You can use chmod rights_you_want parent_folder

Assuming /training/ didn't have an execute permission.

I'd go the user folder and enter chmod a+x training

Change the destination folder/directory to have a w if you want to write to it. or at least a r if you want to read from it

Assuming /sql didn't have a write permission.

I would now chmod a+w sql

Restart the postgresql server sudo systemctl restart postgresql
Try again.

This would most probably help you now get a successful expected result.

score 0 · Answer 16 · answered Jul 13 '22 at 04:48

0

On Linux you can fix this by giving the postgres user read/write/execute permissions on the target directory. Eg:

setfacl -m u:postgres:rwx /home/hi

answered Jul 13 '22 at 04:48

étale-cohomology

2,098
2
28
33

Make sure the directory you choose is not on `tmpfs`. (many distros now put `/tmp` on `tmpfs`). Reason, you cannot set acl's on `tmpfs`. – David C. Rankin May 08 '23 at 19:07

score 0 · Answer 17 · answered Jun 10 '23 at 09:33

0

In Mac, did the following

open /tmp from terminal

/tmp folder opens in finder

Manually copied .csv data file to /tmp

Run command from query tool. COPY tableName FROM '/private/tmp/employee.csv' DELIMITER ',' CSV HEADER;

answered Jun 10 '23 at 09:33

ARS

1

score 0 · Answer 18 · answered Jul 25 '23 at 19:18

0

Been struggling with this for a couple of hours. If you're a Windows user, try moving your file to 'C:\Users\Public'.

answered Jul 25 '23 at 19:18

user17825011

63
1
9

score -3 · Answer 19 · answered Mar 22 '14 at 09:48

-3

May be You are using pgadmin by connecting remote host then U are trying to update there from your system but it searches for that file in remote system's file system... its the error wat I faced May be its also for u check it

answered Mar 22 '14 at 09:48

raj

7

This one is actually true, but could be more explainable. I had to copy file to the server to be able to load it in pgAdmin's console. – adamczi Mar 16 '18 at 13:49

Postgres ERROR: could not open file for reading: Permission denied

19 Answers19

Here are the steps I'd follow to try and resolve it.

Linked

Related