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 
 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

2 comments:

iDeals data room service said...

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

Richard C. Lambert said...

This is an awesome motivating article.I am practically satisfied with your great work.You put truly extremely supportive data. Keep it up. Continue blogging. Hoping to perusing your next post
James