Thread: BUG #6076: Unexpected "Security Definer / invoker" interaction
The following bug has been logged online: Bug reference: 6076 Logged by: Dave Fennell Email address: dave@microtux.co.uk PostgreSQL version: 9.1 (beta2) Operating system: Linux Debian 64bit Description: Unexpected "Security Definer / invoker" interaction Details: Hi all! Not sure if this is a bug or possibly just undocumented (or unclearly documented) behaviour but the interaction of functions defined as "security definer" and functions defined as "security invoker" is not what I would expect. I would expect that if a function defined as "security definer" calls a function defined as "security invoker" the "invoker" role used would be the "definer" of the first function? However it appears that the *actual* invoker (current user) is used. My Setup is something like this: Schema public Schema global Schema sub1 Schema sub2 global schema + all contents are owned by role "global" sub1 schema + all contents are owned by role "sub1" sub2 schema + all contents are owned by role "sub2" The "global" role is a member of "sub1" and "sub2" but not vice-versa. No other grants have been made, I'm assuming full permissions by ownership. The public schema is stored procedures (functions) which we want users to do their interaction with the database through so any user can use them but only developers can access anything other than public. I want to define functions like this: public.func1() security definer owned by global sub1.func2() security invoker owned by sub1 operates on sub1.table owned by sub1 sub2.func3() security invoker owned by sub2 operates on sub2.table owned by sub2 where public.func1 calls sub1.func2 and sub2.func3. Any user can call public.func1 which should then run as user "global" but because "global" is a member of "sub1" and "sub2" I would expect to have access to these schemas. This doesn't seem to be the case. Curiously the function seems to execute but it complains about permission to access the table in the same schema as the function. If I change the functions to this: public.func1() security definer owned by global sub1.func2() security definer owned by global sub2.func3() security definer owned by global Then it works as expected which I'm pretty sure means it is simply not executing as the "global" role rather as the logged in user - unfortunately I'm not sure how to debug this any further - please let me know if there is a way. Can someone clarify if this is the expected / intentional behaviour? If it is then can you please explain why as it seems in my circumstance to be forcing using "security definer" where I do not want it which I understand is something that should be used with caution so should definately not be forced where not needed. Please let me know if I'm simply using the security model / schemas in a way it's not supposed to be used but it seems sensible to me! Thanks, Dave Fennell.
"Dave Fennell" <dave@microtux.co.uk> writes:
> I would expect that if a function defined as "security definer" calls a
> function defined as "security invoker" the "invoker" role used would be the
> "definer" of the first function?
So would I.
> However it appears that the *actual*
> invoker (current user) is used.
You did not provide any evidence of that. Please show a *complete*
self-contained test case.
regards, tom lane
Excerpts from Dave Fennell's message of vie jun 24 10:48:40 -0400 2011: > Not sure if this is a bug or possibly just undocumented (or unclearly > documented) behaviour but the interaction of functions defined as "security > definer" and functions defined as "security invoker" is not what I would > expect. > > I would expect that if a function defined as "security definer" calls a > function defined as "security invoker" the "invoker" role used would be the > "definer" of the first function? However it appears that the *actual* > invoker (current user) is used. I think your problem is that you need an explicit SET ROLE to sub1 before calling sub1.func2(). Alternatively you could set up global so that it "inherits" (which would automatically give it the privileges that both sub1 and sub2 have). There doesn't seem to be a bug here. -- Ãlvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support