Role Permissions

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

"Craig" <postgresql(at)bryden(dot)co(dot)za> writes:
> 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?=20

You need to mark the function as SECURITY DEFINER, which means that it
runs with its creator's permissions. By default a function runs with
the caller's permissions.

(Yeah, SECURITY DEFINER is a pretty obscure name for this. It's what
the SQL spec requires though :-()

regards, tom lane