Re: [EXAMPLE] Overly zealous security of schemas...

> > > Howdy. It looks as though the checks that allow for access to
> > > schemas doesn't check the correct permissions of the running user
> > > in that if a function is being run as the security definer, the
> > > schema checks still check the session_user. Am I missing the work
> > > around someplace or is this a bug?
> >
> > It looks to me like the bug is not related to the use of a SECURITY
> > DEFINER function at all, but just to the use of foreign keys. The
> > RI triggers know they should setuid to the table owner for execution
> > of their generated queries --- but they fail to do so for parsing
> > the queries. So parse-time security checks (such as USAGE on
> > schemas) will fail.
>
> Ah, I had this backwards: I thought SECURITY DEFINER wasn't setting
> something that'd allow the foreign keys to run as the owner of the
> function.
>
> > I think you can make the same problem happen without a SECURITY
> > DEFINER function --- what you need is user A inserting into table B,
> > which has an FK reference to table C, which is in a schema that B's
> > owner has USAGE rights on but A doesn't. Would you try it?
>
> Yep, you're right.

And actually, it looks like sequences have this same problem as well,
only things are slightly worse there: you have to grant SELECT,UPDATE
to a sequence to the public in order for those to work
automagically. :-/

-sc

--
Sean Chittenden