Database Reference
In-Depth Information
COPY 37
test2=# SET SESSION AUTHORIZATION bob;
SET
test2=> copy lines from '/home/bob/names.txt';
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.
To let user Bob copy directly from 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 ' || tablename || ' from ''' || filepath || '''';
end;
$$ language plpgsql;
It is usually a good idea to restrict usage of such a function to the intended user only:
revoke all on function copy_from( text, text) from public;
grant execute on function copy_from( text, text) to bob;
You may also want to check that Bob imports files only from his home directory.
How it works...
When a function defined with
security definer
is called, then postgres changes the
sessions rights to those of the user who defined the function while that function is executed.
So when Bob executes function copy_from(tablename, filepath), he is effectively promoted
to superuser for the time the function is running.
This behavior is similar to
setuid
in unix systems, where you can have a program to be run
by anybody (with execute access) as the owner of that program. It also carries similar risks.
There's more...
There are other operations that are reserved for PostgreSQL superusers, such as setting
some parameters.
