Hi
I am trying to understand how permissions work with plpgsql functions.
I have created a role (lets call it role1) and assigned EXECUTE to a function (lets call it func_1). In func_1, I select data from tableA.
I have then created another role (role2) that inherits from role1.
When I login as role2 and issue "select * from func_1(...);" it comes back with the following error:
"ERROR: permission denied for relation tableA".
I am trying to prevent anyone that inhertis from role1 to not be able to select from any database table, unless they execute a function that I have provided. How do I setup the security for this?
I come from an MS SQL background and in that RDBMS you can grant execute to a stored procedure and any objects that are accessed in the proc work, even if the user has no direct permissions to those objects.
Any help will be greatly appreciated
Craig