snippetsqlMinor
How to allow users to run a function, but not access objects the function is accessing
Viewed 0 times
theobjectsbutfunctionallowaccessinghowusersnotaccess
Problem
I have a PL/PgSQL stored procedure that performs various operations on tables (
I want users to be able to run the stored procedure, but not themselves perform those same operations on the tables.
Is that possible?
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?
Solution
Yes that's possible. Create the function with the user owning all those tables and use the
SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that created it.
Also see the example in the manual:
http://www.postgresql.org/docs/current/static/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY
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
Code Snippets
create or replace function foo()
returns void
as
$body$
...
$body$
language plpgsql
security definer;Context
StackExchange Database Administrators Q#106919, answer score: 3
Revisions (0)
No revisions yet.