Sample Header Ad - 728x90

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

4 votes
2 answers
1322 views
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().
Asked by Katrin (369 rep)
Sep 21, 2018, 08:30 AM
Last activity: Jan 30, 2020, 02:42 AM