Simple Row Security with PostgreSQL 9.4

Published on 12/18/2014

---

PostgreSQL 9.4 makes row security a whole lot easier:

• security_barrier views are update-able
• WITH CHECK OPTION prevents users from inserting, updating, or deleting rows that they can’t / won’t be able to see

create role alice;

create role bob;

create schema protected;

create table protected.bank_accounts (
 id int primary key,
 name text not null,
 owner name not null default current_user,
 balance decimal(19,2) not null
);

create view bank_accounts with ( security_barrier ) as 
 select
  *
 from protected.bank_accounts
 where
  owner = current_user
with check option;

grant delete, insert, select, update on bank_accounts to alice, bob;

Users can’t set owner to a role in which they don’t have membership:

set role alice;

insert into bank_accounts values (1, 'chequeing', 'bob', 500);

ERROR:  new row violates WITH CHECK OPTION for view "bank_accounts"

Users can only see their own stuff:

set role alice;

insert into bank_accounts values (1, 'chequeing', 'alice', 500);

select count(*) from bank_accounts; --result = 1

set role bob;

select count(*) from bank_accounts; --result = 0

delete from bank_accounts; --0 rows affected

... views