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

Question

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 ',');"

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.

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

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;"

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

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;