John McKown <john.archie.mckown@gmail.com> writes:
>Perhaps what is necessary is something akin to the UNIX "sudo" facility.
> That is, an SQL statement prefix which, if used, runs the given SQL
> statement as a PG superuser. You then GRANT(?) authority to that facility
> like you would to a table or database or ... . E.g. GRANT SUDO TO SOMEBODY;
> who could then do SUDO some other SQL statement; and that SQL statement
> would be done as if the PG user was a superuser.
You can already achieve effects of that sort with a security definer
function, eg
begin;
create function sudo(text) returns void as
$$begin execute $1; end$$ language plpgsql security definer;
revoke all on function sudo(text) from public;
grant execute on function sudo(text) to trusted_users;
commit;
(You have to work a bit harder if you want to be able to return query
results, but it's doable.)
The main benefit of approaching things this way is it doesn't have to
be all-or-nothing: the gating function can apply checks on what it
will allow.
regards, tom lane