May 2018
Intermediate to advanced
576 pages
30h 25m
English
One thing that a superuser can do that ordinary user cannot tell PostgreSQL to copy table data from a file:
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) ...