Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a function in postgresql that inserts some rows in a table and updates some other rows based on an input provided by the user. Is it possible to somehow execute the function without giving write access to the user for those tables which the functions inserts and updates? So i basically want the user to be able to execute the function and for the changes to happen through the function, but the user should not be able to insert or update the table on his own outside of the function.

TIA.

share|improve this question

1 Answer 1

Yes this is possible.

You (obviously) need two database users for this:

  • one who is allowed to call the function
  • one who ones the table and is allowed to directly modify it.

Then revoke the update, insert and delete privilege from the restricted user.

Then create the function with the privileged user as the owner and use the attribute SECURITY DEFINER when creating it.

Thus the restricted user cannot change the table, but when he calls the function, the function runs with the privileges of the other user and thus can modify the table.

Something like this:

Logged in as the privileged user:

create user restricted_user password 'hidden';
create table foo (id serial not null primary key, some_info text);
grant select on foo to restricted_user;
create or replace function public.insert_foo(p_info text)
  RETURNS void
  security definer  -- << this is important
  LANGUAGE sql
AS
$body$
   insert into public.foo (some_info) values (p_info);
$body$;

grant execute on function public.insert_foo(text) to restricted_user;

Now you log in as the restricted_user:

insert into foo (some_info) values ('foo');
==> ERROR: permission denied for relation foo

select insert_foo('foo');
==> one row inserted
share|improve this answer
    
Thanks a lot. That worked. –  user38729 May 21 at 12:06
    
@user38729 then please mark the answer as accepted. Thanks! –  dezso May 21 at 12:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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