PostgreSQL: hide table INSERT UPDATE DELETE behind a function

Question

I understand how to use GRANT to control who can do what to a DB object.

I have a case where it would be useful to have specific functions that modify a table's contents while preventing a user from directly accessing the table.

Let's say I have a stock control table and I want a function increase_stock(int) that modifies only one column. There would be other functions that modify other columns, too.

My actual table has arrays of ltrees and arrays of ints, making a trigger an undesirable option as that does not present a clean way of directly manipulating only one field without testing for changes on each. More importantly, it doesn't create the APIs I desire.

Regardless of the approach, can I create a function that is visible to a user while that function accesses a table that is NOT visible to that user?

I'm using PG 10.

Answer 1

here is an example with select. more or less same would be for insert.update and delete:

t=# create table sd(i int);
CREATE TABLE
t=# insert into sd select 1;
INSERT 0 1
t=# create user nr;
CREATE ROLE
t=# create function s() returns table (i int) as $$ 
begin
return query select * from sd;
end;
$$ language plpgsql security definer;
CREATE FUNCTION

now login as nr:

-bash-4.2$ psql -U nr t
psql (9.3.14)
Type "help" for help.

t=> select * from sd;
ERROR:  permission denied for relation sd
t=> select * from s();
 i
---
 1
(1 row)

https://www.postgresql.org/docs/current/static/sql-createfunction.html

SECURITY INVOKER indicates that the function is to be executed with the privileges of the user that calls it. That is the default. SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that owns it.

Of course it means that function owner needs permissions on relation