Emulating row security in PostgreSQL 9.4

PostgreSQL 9.5 adds declarative row security. You can declare policies on tables and have them enforced automatically – for example, allowing user joe to only see rows with the owner column equal to joe.

This is a great feature, and it’s been a long time coming. It didn’t make it into PostgreSQL 9.4, but automatically updatable security_barrier views did. They and LEAKPROOF functions form part of the foundation on which row security is built. You can use these pieces without the declarative policy support to achieve row-security-like effects in 9.4.

I discussed security_barrier views earlier. That post contains examples of how information can be leaked from a view and how security_barrier views prevent such leaks. I’ll assume you’re familiar with the principles in the rest of this post, and won’t repeat the demonstration of view information leaks etc.

To achieve a similar effect to a row security policy on a table you must revoke all access to the table except by the privileged (but non-superuser) role you wish to have own the view(s). Then create a security_barrier view owned by that privileged role, with a WHERE clause that limits other users’ ability to see rows based on your chosen predicate – you can use current_user, a current_setting call, etc.

For example:

CREATE ROLE secret_manager;
CREATE ROLE bob;
CREATE ROLE sid;

CREATE TABLE user_secrets(
    secret_id integer primary key,
    owner text not null,
    secret text not null
);

ALTER TABLE user_secrets OWNER TO secret_manager;

INSERT INTO user_secrets (secret_id, owner, secret) VALUES
(1, 'bob', 'pancakes'),
(2, 'fred', 'waffles'),
(3, 'anne', 'cake'),
(4, 'sid', 'fraud');

REVOKE ALL ON user_secrets FROM public;

CREATE VIEW filtered_user_secrets
WITH (security_barrier)
AS
SELECT *
FROM user_secrets
WHERE owner = current_user
WITH CHECK OPTION;

ALTER VIEW filtered_user_secrets OWNER TO secret_manager;

GRANT ALL ON filtered_user_secrets TO public;

RESET ROLE;

Now lets see how it works:

test=# SET ROLE bob;
SET
test=> select * from filtered_user_secrets ;
 secret_id | owner |  secret  
-----------+-------+----------
         1 | bob   | pancakes
(1 row)

test=> SET ROLE sid;
SET
test=> select * from filtered_user_secrets ;
 secret_id | owner | secret 
-----------+-------+--------
         4 | sid   | fraud
(1 row)

test=> SELECT * FROM filtered_user_secrets WHERE owner = 'bob';
 secret_id | owner | secret 
-----------+-------+--------
(0 rows)

test=> INSERT INTO filtered_user_secrets (secret_id, owner, secret) VALUES (5, 'sid', 'larceny');
INSERT 0 1
test=> select * from filtered_user_secrets ;
 secret_id | owner | secret  
-----------+-------+---------
         4 | sid   | fraud
         5 | sid   | larceny
(2 rows)

test=> INSERT INTO filtered_user_secrets (secret_id, owner, secret) VALUES (6, 'joe', 'impersonation');
ERROR:  new row violates WITH CHECK OPTION for view "filtered_user_secrets"
DETAIL:  Failing row contains (secret_id, owner, secret) = (6, joe, larceny).

Behaviour is very like a row security policy, but with a few limitations:

• ALTERing the underlying table won’t make the changes visible in the view. You must drop and recreate the view.
• It isn’t transparent to applications. Applications need to use the view instead of the underlying table.

The latter point can be addressed to an extent by using schemas and search_path, e.g.

CREATE SCHEMA filtered_tables;

ALTER TABLE user_secrets SET SCHEMA filtered_tables;

-- Leave the view in the public schema and just rename it
ALTER TABLE filtered_user_secrets RENAME TO user_secrets;

Now clients can query the view without caring that it’s a wrapper for the original table.

Instead of using current_user you could instead use current_setting('myapp.active_user'). If you do this, you should set an empty default at the database level so that current_setting doesn’t ERROR if the setting isn’t defined (unless you’re on 9.5 and can use current_setting('myapp.active_user', 't') to ignore missing entries). E.g.

ALTER DATABASE mydatabase SET myapp_active_user = '';

Important: keep in mind that if you use current_setting in a view predicate there are no security policies on user-defined configuration options, so any user who is able to execute arbitrary SQL can change the setting. It remains a useful tool when all queries go through an application with total control over the queries run, but it’s not suitable for restricting the actions of users with direct database connections. The same applies to using SET ROLE to switch the active user when using pooled connections in an application, since any user can just RESET ROLE.

It’s much easier to use 9.5’s row security features, but if you need similar functionality you can already achieve it now.

For even older versions of PostgreSQL you can use SECURITY DEFINER plpgsql functions that RETURN QUERY a filtered view of the underlying table. Performance will tend to be terrible, though, because all rows the user can see must be fetched and then filtered, so most indexes cannot be used. Alternately you can use a plain view, if you don’t allow users to define custom functions so leaks are less of a concern.

Row security, and view based approaches like this, would greatly benefit from the ability to define secure variables that can be set once and cannot be reset, or that can be set only by a particular role (possibly via a SECURITY DEFINER function that does sanity checking). No such functionality will be built in to 9.5, but it might be possible with an extension, a possibility I hope to explore later.