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

Sean Chittenden <sean@chittenden.org> writes:
> 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.

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?
        regards, tom lane