Automatically updatable security barrier views

From PostgreSQL wiki

Revision as of 08:26, 11 November 2013 by Ringerc (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Contents

Automatically updatable security barrier views

As part of the Row-security work, and as a useful feature on its own, it is desirable to support automatically updatable security_barrier views. Ordinary views are updatable in 9.3, but security_barrier views aren't considered "simple" views and are not updatable.

Status in 9.3

"simple" views are made updatable in 9.3 by flattening the view quals into the outer query. So given:

 CREATE TABLE t AS 
 SELECT n AS id, 'secret'||n AS secret FROM generate_series(1,10) n;
 CREATE VIEW t_even AS SELECT * FROM t WHERE id % 2 = 0;
 CREATE VIEW t_even_sb WITH (security_barrier) AS SELECT * FROM t WHERE id % 2 = 0;

you can update the simple non-security-barrier view, but can also steal values:

 CREATE OR REPLACE FUNCTION f_leak(text) RETURNS boolean AS $$
 BEGIN
   RAISE NOTICE 'Saw secret=%',$1;
   RETURN true;
 END;
 $$ LANGUAGE plpgsql COST 1;
 test=>   UPDATE t_even SET id = id WHERE f_leak(secret);
 NOTICE:  Saw secret=secret1
 NOTICE:  Saw secret=secret3
 NOTICE:  Saw secret=secret5
 NOTICE:  Saw secret=secret7
 NOTICE:  Saw secret=secret9
 NOTICE:  Saw secret=secret2
 NOTICE:  Saw secret=secret4
 NOTICE:  Saw secret=secret6
 NOTICE:  Saw secret=secret8
 NOTICE:  Saw secret=secret10
 UPDATE 5

You can't update the security barrier view at all:

 test=> UPDATE t_even_sb SET id = id WHERE f_leak(secret);
 ERROR:  cannot update view "t_even_sb"
 DETAIL:  Security-barrier views are not automatically updatable.
 HINT:  To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.

so there's no secure way to give a user update on only some rows of table. That's something we need for row-security and it's a feature that would be useful for users in general.

Issue with security barrier support

The existing updatable view code relies on flattening a view's quals into the outer query. So with the above example:

 UPDATE t_even SET id = id WHERE f_leak(secret);

gets view-expanded into something logically like:

 UPDATE (SELECT * FROM t WHERE id % 2 = 0) SET id = id WHERE f_leak(secret)

but the executor doesn't know how to deal with that so it can't be passed through as-is. Instead the subquery gets flattened out, producing:

 UPDATE t SET id = id WHERE id % 2 = 0 AND f_leak(secret)

which the executor can deal with, since it's a simple update of a relation. You can see this in the plan:

 test=# explain  UPDATE t_even SET id = id WHERE f_leak(secret);
                        QUERY PLAN                        
 ---------------------------------------------------------
  Update on t  (cost=0.00..31.53 rows=2 width=42)
    ->  Seq Scan on t  (cost=0.00..31.53 rows=2 width=42)
          Filter: (f_leak(secret) AND ((id % 2) = 0))
 (3 rows)

See how the f_leak(secret) qual and id % 2 = 0 are at the same level? The executor will tend to pick f_leak(secret) to run first because its COST is artificially low.

This won't work with security-barrier views, since we have to make sure that the predicate id % 2 = 0 filters rows before any are passed to the user-supplied predicate, in this case f_leak(secret). So the current code refuses to operate on security_barrier views.

Adding security_barrier support

To add security_barrier support for updatable views we need to teach UPDATE and DELETE to operate on a subquery.

This isn't unprecedented.

Personal tools