3

I have a PL/PgSQL stored procedure that performs various operations on tables (SELECT, UPDATE, INSERT, TRUNCATE).

I want users to be able to run the stored procedure, but not themselves perform those same operations on the tables.

Is that possible?

3

Yes that's possible. Create the function with the user owning all those tables and use the SECURITY DEFINER modifier.

SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that created it.

create or replace function foo()
  returns void
as
$body$
...
$body$
language plpgsql
security definer;

Also see the example in the manual:
http://www.postgresql.org/docs/current/static/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.