In PostgreSQL it’s quite easy to restrict access for user to some tables:

1. create restrictive view
2. grant usage on view schema
3. grant select on view to restricted user
4. done

Really easy. And it’s not working when the restrictive view, is selecting from another view that is using function(s)!

In that case, you might get very informative error:

ERROR:  permission denied for relation <TABLE>

or

ERROR:  permission denied for schema <SCHEMA>

Why ? Well everything works as expected, you have permission to SELECT from that restrictive VIEW and thus you really have some access to the underlying view/table, but function used in that view is still executed with permission of restricted user and therefore you obviously end up with ‘permission denied‘.

Solution for this is simple, force affected function(s) to execute with privileges of user that created it:

ALTER FUNCTION <<NAME>> SECURITY DEFINER;

See CREATE FUNCTION manual for more info.