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

Sean Chittenden <sean(at)chittenden(dot)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