[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?  Here's the example code to demonstrate this problem:

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

\c test pgsql
BEGIN;
-- In case pl/plpgsql isn't loaded
CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler   AS '$libdir/plpgsql', 'plpgsql_call_handler'
LANGUAGEc; 

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
COMMIT;


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

SET search_path TO public;
CREATE FUNCTION t_ins(INT)   RETURNS INT   EXTERNAL SECURITY DEFINER   AS '
DECLARE     v_usr TEXT;
BEGIN     SELECT current_user INTO v_usr;     RAISE NOTICE ''current_user: %'', v_usr;     SELECT session_user INTO
v_usr;    RAISE NOTICE ''session_user: %'', v_usr; 
     INSERT INTO s.t (i) VALUES ($1);     RETURN $1;
END;
' LANGUAGE 'plpgsql';

SET search_path TO public;

REVOKE ALL ON SCHEMA public,s FROM PUBLIC;
GRANT EXECUTE ON FUNCTION t_ins(INT) TO PUBLIC;

-- Unnecessary grant, but example is explicit at least
GRANT USAGE ON SCHEMA s TO dba;

-- Usage has to be granted to the PUBLIC in order for this to work,
-- even though the function is being run as the dba user: this
-- seems broken in that session_user isn't being set correctly when a
-- function is run as the security definer, or that schema checks
-- aren't consulting the current_user and are consulting the
-- session_user.  Anyway, for the sake of correctness, comment out the
-- GRANT command for now, but if you want to see this example work
-- from start to finish, uncomment the following line.
--
-- GRANT USAGE ON SCHEMA s TO PUBLIC;
INSERT INTO s.c VALUES (42);
COMMIT;

\c test dba
-- Works
SELECT t_ins(42);
\c test normal_user
-- Doesn't work unless you run: GRANT USAGE ON SCHEMA s TO PUBLIC;
SELECT t_ins(42);
/* END */


The place where this breaks down is in the foreign key constraints, it
can't select 1 from the s schema and returns a permission denied.

test=> SELECT t_ins(42);
NOTICE:  current_user: dba
NOTICE:  session_user: normal_user
ERROR:  s: permission denied


-sc

--
Sean Chittenden