Thread: BUG #6076: Unexpected "Security Definer / invoker" interaction

BUG #6076: Unexpected "Security Definer / invoker" interaction

From
"Dave Fennell"
Date:
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.

Re: BUG #6076: Unexpected "Security Definer / invoker" interaction

From
Tom Lane
Date:
"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

Re: BUG #6076: Unexpected "Security Definer / invoker" interaction

From
Alvaro Herrera
Date:
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