This article is half-done without your Comment! *** Please share your thoughts via Comment ***
You are getting above error message because an only superuser can execute the COPY command in PostgreSQL.
Currently, I am working with ETL team, and we are managing the massive size of Data warehouse.
In the ETL project, database developers may require using COPY command. But unfortunately, they are getting below error.
1 2 |
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 solution:
The superuser can create a wrapper function and can assign to the public.
Script to create a wrapper function:
1 2 3 4 5 6 7 8 9 10 |
create or replace function copy_for_all(tablename text, filepath text) returns void security definer as $$ declare begin execute 'copy ' || tablename || ' from ''' || filepath || ''''; end; $$ language plpgsql; |