'How to allow permission to access CSV file using postgres in Ubuntu

I am using the following command :

copy (select so.name,
      so.date_order,
      sol.name,
      sol.product_Id,
      sol.product_uom_qty , 
      ai.number, 
      ai.date_invoice , 
      so.amount_total , 
      so.amount_tax 
      from sale_order so , 
      sale_order_line sol , 
      account_invoice ai 
      where so.id = sol.order_id 
      and so.name = ai.origin 
      and ai.state='open') 

to '/home/ekodev/Documents/test1.csv' delimiter ',' csv header;

However, it gives the following error :

********** Error **********

ERROR: must be superuser to COPY to or from a file
SQL state: 42501
Hint: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

Even when I changed the permission ekodev@partner:~/Documents$ sudo chmod a+rwX /home/ekodev/ /home/ekodev/Documents/ /home/ekodev/Documents/test1.csv

It still does not work for me.

Does anybody know what the problem is?

postgresqlubuntu


Solution 1:[1]

This solution worked for me using \copy. ALTER did not as that also required admin privileges.

psql -h <host> -U <user> -d <dbname> -c "\copy <table_name> FROM '<path to csvfile/file.csv>' with (format csv,header true, delimiter ',');"

Solution 2:[2]

Out of documentation:

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.

That means, your database user needs to have the superuser flag. you can set the flag with 

ALTER ROLE <rolename> WITH SUPERUSER

As this can be quiet dangerous did you consider using \copy from psql instead to copy data from client side.

Solution 3:[3]

Alternatively you could use pgAdmin to import csv data. Works when the SuperUser role is not available like in for example AWS.

Solution 4:[4]

It's worked for my case

sudo psql -h localhost -U root -d my_db -p 5432  -c "\COPY source_table TO  '/home/user/source_table.csv' DELIMITER ',' CSV HEADER;"

Solution 5:[5]

The role that is running the query needs to be SUPERUSER to COPY FROM file. Otherwise you can copy only from STDIN.

Solution 6:[6]

From inside database using postgres user:

sudo -u postgres psql

change file's permissions (all users can read and write):

chmod o+rw /tmp/amit.csv

get data from specific table:

\COPY table TO '/tmp/amit.csv' DELIMITER ',' CSV HEADER;

Solution 7:[7]

GRANT pg_read_server_files TO <your user>;

It is not necessary to grant superuser privileges as suggested in other answers.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Atihska
Solution 2 frlan
Solution 3 David Smits
Solution 4 Beyhan Gul
Solution 5 DrCabry
Solution 6 Danrley Pereira
Solution 7 noname12

Related Questions