From: | Sean Chittenden <sean(at)chittenden(dot)org> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | [EXAMPLE] Overly zealous security of schemas... |
Date: | 2003-04-26 20:48:23 |
Message-ID: | 20030426204823.GN79923@perrin.int.nxad.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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'
LANGUAGE c;
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2003-04-26 21:07:17 | Array access to type "name" |
Previous Message | Tom Lane | 2003-04-26 16:45:31 | Re: conflicting libraries at runtime |