Hi all I'm going to be contributing a fair bit of time to RLS for 2ndQuadrant, courtesy of the EU AXLE project (http://axleproject.eu/).
I've been catching up on Kohei KaiGai's work and I've been really impressed by what's already done and working. I'm currently reading the patches, mailing list discussion, and the tests. Prompted by mailing list discussion on the topic, I added some tests to the 9.4 v4 RLS patch to check behaviour around SECURITY DEFINER functions and found a bit of an issue. If one non-superuser user2 with limited row access creates a SECURITY DEFINER function that returns a refcursor, and the other user user1 fetches from the cursor, the returned result set is what user1 sees when selecting the table directly, not what user2 sees when it selects the table. That is inconsistent with how SECURITY DEFINER behaves for other rights. It's also inconsistent with a superuser-owned SECURITY DEFINER function, where RLS doesn't add the predicate at all so all rows are returned. Another issue occurs when the superuser invokes the SECURITY DEFINER function created by user2. There are no rows returned from a fetch of the refcursor. This makes sense given that in the test the RLS condition is "(dauthor = "current_user"())" and there are no rows with dauthor set to the superuser's username. This asymmetry is a bug. Either RLS should be applied consistently for the definer, or consistently as the caller. Currently it's the caller unless the definer is superuser, in which case no checks are applied because the RLS predicate never gets applied. I'm doing these tests on top of the tables defined by the rowsecurity test suite in the patch. On a side note, I also noticed that while psql's \dt+ supports RLS, \d or \d+ doesn't provide any indication that there's an RLS policy or what the conditions are. Anyway - the additional tests are attached, and can also be found in https://github.com/ringerc/postgres/tree/rls-9.4 along with a patched expected file showing what I think _should_ be happening. Comments would be appreciated. I'm also interested in more details on the mention of "functions that change the current user ID during a query" that came up in prior RLS discussion. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql new file mode 100644 index 55d4aad..d9a827f *** a/src/test/regress/sql/rowsecurity.sql --- b/src/test/regress/sql/rowsecurity.sql *************** SET SESSION AUTHORIZATION rls_regress_us *** 277,282 **** --- 277,352 ---- EXPLAIN (costs off) DELETE FROM only t1 WHERE f_leak(b); EXPLAIN (costs off) DELETE FROM t1 WHERE f_leak(b); + + + -- Check refcursors returned from PL/PgSQL SECURITY DEFINER functions + + RESET SESSION AUTHORIZATION; + + CREATE OR REPLACE FUNCTION return_refcursor_assuper() RETURNS refcursor AS $$ + DECLARE + curs1 refcursor; + BEGIN + curs1 = 'super_cursor'; + OPEN curs1 FOR SELECT * FROM document; + RETURN curs1; + END; + $$ + LANGUAGE plpgsql + SECURITY DEFINER; + + -- Run the function entirely as rls_regress_user1 + SET SESSION AUTHORIZATION rls_regress_user1; + BEGIN; + SELECT return_refcursor_assuper(); + -- This fetch should return the full results, even though we are now + -- running as a user with much lower access according to the current + -- RLS policy. + FETCH ALL FROM "super_cursor"; + -- But this should still return the usual result set + SELECT * FROM document; + ROLLBACK; + + -- Do the same check where we return a refcursor from one RLS-affected + -- user to another RLS-affected user. + + SET SESSION AUTHORIZATION rls_regress_user2; + + CREATE OR REPLACE FUNCTION return_refcursor_asuser2() RETURNS refcursor AS $$ + DECLARE + curs1 refcursor; + BEGIN + curs1 = 'user2_cursor'; + OPEN curs1 FOR SELECT * FROM document; + RETURN curs1; + END; + $$ + LANGUAGE plpgsql + SECURITY DEFINER; + + BEGIN; + SET SESSION AUTHORIZATION rls_regress_user1; + SELECT return_refcursor_asuser2(); + -- Even though we're user1, we should see only user2's results from this. + -- This FAILS, returning user1's results. + FETCH ALL FROM "user2_cursor"; + -- but user1's results for this + SELECT * FROM document; + ROLLBACK; + + -- Now as the superuser, see if the SECURITY DEFINER on an RLS-affected + -- user filters the rows the superuser sees. It should, for consistency. + + BEGIN; + RESET SESSION AUTHORIZATION; + SELECT return_refcursor_asuser2(); + -- Should see user2's results, but FAILS, instead returning an empty result set (!) + FETCH ALL FROM "user2_cursor"; + -- Should see superuser's results + SELECT * FROM document; + ROLLBACK; + + DELETE FROM only t1 WHERE f_leak(b) RETURNING oid, *, t1; DELETE FROM t1 WHERE f_leak(b) RETURNING oid, *, t1;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
