PostgreSQL 11 Administration Cookbook

How to do it…

An ordinary user cannot tell PostgreSQL to copy table data from a file. Only a superuser can do that, as follows:

pguser@hvost:~$ psql -U postgrestest2...test2=# create table lines(line text);CREATE TABLEtest2=# copy lines from ‘/home/bob/names.txt’;COPY 37test2=# SET ROLE to bob;SETtest2=> copy lines from ‘/home/bob/names.txt’;ERROR:  must be superuser to COPY to or from a fileHINT:  Anyone can COPY to stdout or from stdin. psql’s \copy command also works for anyone.

To let bob copy directly from the file, the superuser can write a special wrapper function for bob, as follows:

create or replace function copy_from(tablename text, filepath text) returns void security definer as $$  declare  begin  execute 'copy ' || quote_ident(tablename) ...