Only allow access to a table via a function, not directly via queries

Question

I have a table t and some functions which access t. For example:

create function list_t() returns setof t as $$
    select * from t;
$$ language sql stable;

Is it possible to define role permissions to only allow the execution of list_t(), while not allowing plain SELECT, UPDATE, DELETE and INSERT queries on t?

I tried the following:

grant execute on all functions in schema public to my_user;
revoke all on t from my_user;

While this indeed disallows SELECT * FROM t, it also disallows SELECT list_t().

Answer 1

You need to create the function as a user that has (select) access to the underlying table. Then define the function with the option security defined which means when the function is called, it operates with the privileges of the function owner, not the caller:

create function list_t() returns setof t 
  security definer
as $$
    select * from t;
$$ language sql stable;

---

Another way of doing this is to create a view, then grant SELECT privileges to the view (not the table):

create view v_t
as
select *
from t;

grant select on v_t to my_user;

---

Note that a set returning function should be used in the FROM clause like a table:

select *
from list_t();

Answer 2

The issue brought up here is easily resolved by using SECURITY DEFINER. However, it's worth noting that code that runs in procedures and functions marked as SECURITY DEFINER cannot utilize transaction support (such as COMMIT or ROLLBACK).