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.  Here's the test script + logput:

/* Begin */
\c template1 pgsql
DROP DATABASE test;
CREATE DATABASE test WITH OWNER dba;

\c test dba
BEGIN;
CREATE SCHEMA s AUTHORIZATION dba;
CREATE TABLE s.c (i INT, PRIMARY KEY(i));
CREATE TABLE public.t (i INT);
ALTER TABLE public.t ADD FOREIGN KEY(i) REFERENCES s.c(i);

REVOKE ALL ON SCHEMA s FROM PUBLIC;
GRANT INSERT,SELECT ON TABLE t TO PUBLIC;
INSERT INTO s.c VALUES (42);
COMMIT;

\c test normal_user
INSERT INTO t VALUES (42);
/* End */

And the bits from the log file:
2003-04-26 14:29:39 [1044]   LOG:  query: INSERT INTO t VALUES (42);
2003-04-26 14:29:39 [1044]   LOG:  query: SELECT 1 FROM ONLY "s"."c" x WHERE "i" = $1 FOR UPDATE OF x
2003-04-26 14:29:39 [1044]   ERROR:  s: permission denied

-sc

-- 
Sean Chittenden