Security Barrier Views

People sometimes want to use PostgreSQL to implement row-level security, and historically it has not been very easy to do that securely.  You can try to do it by creating a view which exposes just some of the rows  in the underlying table, and grant access to the view but not the underlying table to the user to whom you wish to expose the data; but this turns out not to be secure.  Consider the following example.

rhaas=# create table emp (name text, jobtitle text, department text);
CREATE TABLE
rhaas=# drop table emp;
DROP TABLE
rhaas=# create table emp (name text, jobtitle text, organization text);
CREATE TABLE
rhaas=# insert into emp values ('Robert Haas', 'Senior Database Architect', 'EnterpriseDB'), ('KaiGai Kohei', 'SELinux Guru', 'NEC'), ('Tom Lane', 'Ninja', 'CIA');
INSERT 0 3
rhaas=# create view unclassified_emp as select * from emp where organization <> 'CIA';
CREATE VIEW
rhaas=# create user bob;
CREATE ROLE
rhaas=# grant select on unclassified_emp to bob;
GRANT
rhaas=# \c - bob
You are now connected to database "rhaas" as user "bob".
rhaas=> select * from emp;
ERROR:  permission denied for relation emp
rhaas=> select * from unclassified_emp;
     name     |         jobtitle          | organization
--------------+---------------------------+--------------
 Robert Haas  | Senior Database Architect | EnterpriseDB
 KaiGai Kohei | SELinux Guru              | NEC
(2 rows)

Here's the problem: if bob has permission to create functions (even temporary functions), the jig is up, and he will easily be able to extract the hidden rows:

rhaas=> create or replace function pg_temp.leak(text) returns bool as $$begin raise notice '%', $1; return true; end$$ language plpgsql cost 0.0000000000000001;
CREATE FUNCTION
rhaas=> select * from unclassified_emp e where pg_temp.leak(name);

NOTICE:  Robert Haas

NOTICE:  KaiGai Kohei
NOTICE:  Tom Lane
     name     |         jobtitle          | organization
--------------+---------------------------+--------------
 Robert Haas  | Senior Database Architect | EnterpriseDB
 KaiGai Kohei | SELinux Guru              | NEC
(2 rows)

Curses!  The hidden Tom Lane has been revealed!  By creating a function with a very small cost, bob has tricked the optimizer into evaluating leak(text) before checking whether organization = 'CIA', and thus Tom's name gets spit out as a side effect while executing the query, even though it still doesn't show up in the query results.  With a little more work, the rest of the hidden information can be extracted as well, or the entire hidden table copied into an identical table owned by bob.

This is not exactly a security hole, because we've documented that this is how it works, and that you shouldn't rely on any other behavior.  And you can use it provide row-level security if you're infrastructure enables you to place stringent restrictions on what queries people can execute, but this is harder to do than it first appears.  Even built-in functions can have side effects (e.g. division by zero, casting failures) that can leak information about rows that were intended to stay hidden.  So it would be nice to have a better way.

Courtesy of a great deal of hard work by KaiGai Kohei, we do now have a better way.  In PostgreSQL 9.2devel, you can do this:

rhaas=# create or replace view unclassified_emp with (security_barrier) as select * from emp where organization <> 'CIA';

CREATE VIEW

This protects against the attacks mentioned above, and some more subtle ones as well; and it's about as simple to use as you could hope for.  It does not protect against every possible way you could gather information about the unseen rows (such as timing attacks, or beating up the DBA) but it at least prevents overt data leakage, which for many use cases is good enough.