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
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)
security definer -- << this is important
insert into public.foo (some_info) values (p_info);
grant execute on function public.insert_foo(text) to restricted_user;
Now you log in as the
insert into foo (some_info) values ('foo');
==> ERROR: permission denied for relation foo
==> one row inserted