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 gt  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    gt  chown postgres  users darchcruise desktop items ordered csv chown   users darchcruise desktop items ordered csv  Operation not permitted   Lastly  I tried    gt  ls -l -rw-r--r--  1 darchcruise  staff      1016 Oct 18 21 04 items ordered csv   Any help is much appreciated

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Copy the CSV file to  tmp  For me this solved the issue

User · Answer

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

User · Answer

COPY your table  Name  Latitude  Longitude  FROM  C  Temp your file csv  DELIMITERS     CSV HEADER    Use c  Temp  Your File

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[postgresql] Postgres ERROR: could not open file for reading: Permission denied

Examples related to postgresql