4

I have copied function(from one of the webportal and modified accordingly) to copy data from csv file to table.

create or replace function public.load_csv_file
(
target_table text,
csv_path text,
col_count integer
)

returns void as $$

declare

iter integer; -- dummy integer to iterate columns with
col text; -- variable to keep the column name at each iteration
col_first text; -- first column name, e.g., top left corner on a csv file or     spreadsheet

begin
set schema 'public';

create table insert_from_csv ();

-- add just enough number of columns
for iter in 1..col_count
loop
    execute format('alter table insert_from_csv add column col_%s text;', iter);
end loop;

-- copy the data from csv file
execute format('copy insert_from_csv from %L with delimiter '','' quote ''"'' csv ', csv_path);

iter := 1;
col_first := (select col_1 from insert_from_csv limit 1);

-- update the column names based on the first row which has the column names
for col in execute format('select unnest(string_to_array(trim(temp_table::text, ''()''), '','')) from temp_table where col_1 = %L', col_first)
loop
    execute format('alter table insert_from_csv rename column col_%s to %s', iter, col);
    iter := iter + 1;
end loop;

-- delete the columns row
execute format('delete from insert_from_csv where %s = %L', col_first, col_first);

-- change the temp table name to the name given as parameter, if not blank
if length(target_table) > 0 then
    execute format('alter table insert_from_csv rename to %I', target_table);
end if;

end;

$$ language plpgsql;

And passing parameters as

select load_csv_file('Customer','C:\Insert_postgres.csv' ,4)

but getting error message

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

The idea is, i will create a automated test, and if i want to test on different instance then test should automatically create function and copy data from csv file. Is there any work around to copy data without superuser?

Geeme
  • 395
  • 2
  • 6
  • 18
  • 1
    The executing user will need permissions on the file system. I believe this defaults to the /home directory. Can you grant the executing user read/write on the directories the copy files exist ? – VynlJunkie Jun 23 '17 at 08:44
  • 1
    Yes, the user i am using has those rights. – Geeme Jun 23 '17 at 08:58
  • 1
    https://stackoverflow.com/questions/20268420/postgres-copy-to-from-a-file-as-non-superuser – Vao Tsun Jun 23 '17 at 09:17
  • 1
    Possible duplicate of [Postgres COPY TO / FROM A FILE as non superuser](https://stackoverflow.com/questions/20268420/postgres-copy-to-from-a-file-as-non-superuser) – Vao Tsun Jun 23 '17 at 09:18

2 Answers2

2

Looks Insert_postgres.csv is in C drive which usually does not have Read/Write permission. Move the file to your directory where Read/Write given atleast to some groups or everyone. Hope it will resolve the issue

priya raj
  • 362
  • 2
  • 8
  • 1
    I tried moving file to c:\Test\Insert_postgres.csv,but still getting same error. Note: The postgres user which i am using is not superuser. – Geeme Jun 23 '17 at 08:52
  • 1
    To make a user a SuperUser: ALTER USER username WITH SUPERUSER; To make a user no longer a SuperUser: ALTER USER username WITH NOSUPERUSER; To just allow the user to create a database: ALTER USER username CREATEDB; You can also use CREATEROLE and CREATEUSER to allow a user privileges without making them a superuser. – priya raj Jun 23 '17 at 09:00
  • 1
    https://stackoverflow.com/questions/10757431/postgres-upgrade-a-user-to-be-a-superuser (for more information) – priya raj Jun 23 '17 at 09:00
  • 1
    Hi Priya, thanks for the answer, but the user i have isn't a superuser so can't alter it's permissions. What i am trying to achive is, I am going to automate this process, so i want to repeat the activity again and again with just click, So i am looking for solution to copy without being superuser. – Geeme Jun 23 '17 at 09:29
  • 2
    After doing extensive research on this subject... you cannot use the COPY statement without being a SuperUser. End of story. :-) – blissweb Mar 10 '21 at 23:10
  • you can use \copy instead of COPY to get the job done. COPY wont work unless you are a superuser \copy will work as long as you have select and insert permissions. End of story :--) – idris Sep 14 '21 at 14:07
1

Try running the following command in terminal. This allows us to use COPY command, since you can't use COPY command without being a super user.

ALTER USER <username> WITH SUPERUSER;
Rohit Katariya
  • 119
  • 1
  • 9