Thursday, December 18, 2014

Simple Row Security with PostgreSQL 9.4

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 
 from protected.bank_accounts
  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

1 comment:

iDeals data room service said...

A lot of interesting info. Your tips are amazing. I routinely read your web journal and its exceptionally useful.