Row-based security (virtual private database)

I have found a way to implement the sort of row-based security that a
few people have recently been looking for. I would like to develop this
sufficiently to add this solution to the PostgreSQL CookBook Pages and
would appreciate feedback on both the general approach and the actual
implementation. I apologise for the length of this posting (if you
reply to this please cite only the parts you need).

To restate the problem, we are looking for a means to restrict the
access of individual users to specific subsets of the available data,
though they will all share the same (full) data model. Often this will
be used to partition the data in a database so that only the owner of
that data will be able to see it, hence the term Virtual Private
Database.

Consider the following logical data model:

person
/ |
/|\ |
merchant |
| \ |
/|\ /|\ /|\
item customer
\ /
/|\ /|\
purchase

A customer should see:
- only their own person details
- only their own customer details
- only their own purchases
- public details of items for sale by their merchant(s)
- public details of their merchant(s) information

A merchant should see:
- their own full person details, and the public details for persons
who are their customers
- only their own merchant details
- only their own customers details
- only their customers purchases
- all items for sale by themselves

[A merchant is a person. A customer is a person. A person may be a
customer of many merchants.]

We can fairly easily use rewrite rules to restrict access but the
difficulty lies in providing those rules with a secure means of
identifying the user.

We do this by implementing an application level authentication protocol
within the database. At the start of your application session you must
logon to authenticate yourself and establish your session rights.

We maintain a "sessions" table to record this login information, with a
"logon" view that provides access only to the session record for your
own session.

Logon is by insertion of your user details into the logon view. If your
user details are valid (currently we use password authentication), your
insert will succeed and you will gain the appropriate access. If not,
you will have no logon record and the rewrite rules will give you only
the most rudimentary level of access. At the end of your session you
should delete your logon record so that no future application user can
inherit it.

Well, that's the theory. The implementation is a bit clunky but it
works. It assumes two database user accounts. One to own everything,
and one, "dbuser", which will access it.

-- BASE TABLES
-- t_persons is the primary authentication table as it stores passwords.
-- It also stores personal details such as address information.
--
create table t_persons (
name varchar(10) primary key,
password varchar(10) not null,
address varchar(100) not null
);

create table t_merchants (
name varchar(10) primary key,
address varchar(100) not null,
tax_code varchar(20),
bank_account varchar(40),
constraint fk_owner foreign key(name)
references t_persons(name)
);

create table t_customers (
merchant varchar(10) not null,
name varchar(10) not null,
notes varchar(100),
constraint pk_customers
primary key(merchant, name),
foreign key (name)
references t_persons(name),
foreign key (merchant)
references t_merchants(name)
);

create table t_items (
merchant varchar(10) not null,
item_id integer not null,
cost money not null,
price money not null,
constraint pk_items
primary key(merchant, item_id),
foreign key(merchant)
references t_merchants(name)
);

create table t_purchases (
merchant varchar(10) not null,
item_id integer not null,
name varchar(10) not null,
qty integer not null,
price money not null,
purchase_date date not null,
filled_date date,
constraint pk_purchases
primary key(merchant, name),
foreign key(merchant, item_id)
references t_items(merchant, item_id),
foreign key(merchant, name)
references t_customers(merchant, name)
);

-- SESSION AND LOGON MANAGEMENT
-- The pid function returns the pid of the connected backed process.
-- This is sufficient to uniquely identify a session.
--
create function pid() returns int
as '/home/marc/vpd/pid.so' language 'c';

-- Commented-out function is explained below.
--
--create function user_has_role(varchar, varchar) returns boolean as '
--declare
-- p_name alias for $1;
-- p_role alias for $2;
-- v_dummy boolean;
--begin
-- if p_role = ''CUSTOMER'' then
-- select true
-- into v_dummy
-- from t_customers
-- where name = p_name
-- limit 1;
-- return found;
-- else
-- if p_role = ''MERCHANT'' then
-- select true
-- into v_dummy
-- from t_merchants
-- where name = p_name;
-- return found;
-- else
-- return FALSE;
-- end if;
-- end if;
--end;
--' language 'plpgsql';

-- The sessions table shows the currently connected sessions. You have
-- to have a session recorded in this table in order to be able to see
-- most data. To record your session into this table you insert into
-- the logon view. You must end your current session with a new logon,
-- or by deleting your entry from the logon view.
--
create table sessions (
pid int primary key,
name varchar(10) not null,
role varchar(10) not null
);

-- The logon view is used to record and view your session's access
-- rights. These are based on your name and role.
--
create view logon as
select name, '--------------------'::varchar(10) as password, role
from sessions
where pid = pid();

-- Only allow logon inserts if a valid username and password, and
-- appropriate role is provided. The ORed sub-queries below implement
-- the user_has_role functionality that is commented out above.
-- Definining this as a function does not work because functions do not
-- inherit the permissions of the rule owner which are needed in order
-- to see the password field of t_persons.
--
create rule logon_insert as
on insert to logon do instead
( delete from sessions where pid = pid();
insert into sessions
(pid, name, role)
select pid(), new.name, upper(new.role)
from t_persons p
where p.name = new.name
and p.password = new.password
and ( (upper(new.role) = 'CUSTOMER'
and exists (
select 1
from t_customers
where name = new.name)
)
or (upper(new.role) = 'MERCHANT'
and exists (
select 1
from t_merchants
where name = new.name)
)
);
);

create rule logon_delete as
on delete to logon do instead
delete from sessions where pid = pid();

grant select, insert, delete on logon to dbuser;

-- RESTRICTED ACCESS VIEWS
-- Person records can be seen by the person themselves or by merchants.
-- This is not very secure. Merchants should only be able to see person
-- details for their own customers. This is left as an exercise for the
-- reader. Persons cannot be deleted by dbuser.
--
create view persons as
select p.name, '--------------------'::varchar(10) as password,
p.address
from t_persons p, logon l
where p.name = l.name
or l.role = 'MERCHANT';

-- Anyone can insert into the persons table.
--
create rule persons_insert as
on insert to persons do instead
insert into t_persons
(name, password, address)
values (new.name, new.password, new.address);

-- Only the owner of the record may update it, and they may only update
-- the password and the address fields.
--
create rule persons_update as
on update to persons do instead
update t_persons set
name = new.name,
password = case when new.password = '--------------------'
then old.password
else new.password
end,
address = new.address
where name = new.name
and name = (select name from logon);

grant select, insert, update on persons to dbuser;

-- The merchants view gives us only the subset of the persons table that
-- we should see. Specifically, only the merchant may see their
-- bank account details, and only when they are logged in as a merchant.
-- Note that merchants cannot be created or deleted using the dbuser
-- account.
--
create view merchants as
select m.name, m.address, m.tax_code,
case when (m.name = l.name and l.role = 'MERCHANT')
then m.bank_account
else null
end as bank_account
from t_merchants m, logon l;

create rule merchants_update as
on update to merchants do instead
update t_merchants set
address = new.address,
bank_account = new.bank_account,
tax_code = new.tax_code
where name = new.name
and name = (select name from logon where role = 'MERCHANT');

grant select, update on merchants to dbuser;

-- Merchants can see all customer details for their own customers.
-- Customers can see their own customer info but not any notes.
--
create view customers as
select c.merchant, c.name,
case when l.role = 'MERCHANT'
then c.notes
else null
end as notes
from t_customers c, logon l
where (l.name = c.name and l.role = 'CUSTOMER')
or (l.name = c.merchant and l.role = 'MERCHANT');

-- The person or the merchant may create a customer.
--
create rule customers_insert as
on insert to customers do instead
insert into t_customers
(name, merchant, notes)
select new.name, new.merchant,
case when l.role = 'MERCHANT'
then new.notes
else null
end
from logon l
where (l.name = new.name and l.role = 'CUSTOMER')
or (l.name = new.merchant and l.role = 'MERCHANT');

-- The only updatable field in customers is notes, so only the merchant
-- may update it.
--
create rule customers_update as
on update to customers do instead
update t_customers set
notes = new.notes
where exists (
select 1
from logon
where name = new.merchant
and role = 'MERCHANT')
and name = new.name
and merchant = new.merchant;

grant select, insert, update on customers to dbuser;

-- Items can be seen by everyone. Price can only be seen by the
-- owning merchant. Better security would be to only allow customers of
-- the merchant in question to see the items.
--
create view items as
select i.merchant, i.item_id,
case when 'MERCHANT' = (
select role from logon where name = i.merchant)
then i.cost
else null
end as cost,
i.price
from t_items i;

-- Only the merchant may insert or update items. Deletions are not
-- allowed.
create rule items_insert as
on insert to items do instead
insert into t_items
(merchant, item_id, cost, price)
select new.merchant, new.item_id, new.cost, new.price
from logon
where role = 'MERCHANT'
and name = new.merchant;

create rule items_update as
on update to items do instead
update t_items set
cost = new.cost,
price = new.price
where merchant = new.merchant
and item_id = new.item_id
and 'MERCHANT' = (
select role from logon where name = new.merchant);

grant select, insert, update on items to dbuser;

-- Purchases are left as an exercise for the reader.

-- TEST DATA

-- People first:
insert into t_persons values ('marc', 'secret', 'Here');
insert into t_persons values ('fred', 'secret', 'There');
insert into t_persons values ('bob', 'hidden', 'somewhere');

-- Then merchants:
insert into t_merchants values ('marc', 'Shop', '99', 'mine');
insert into t_merchants values ('bob', 'shop 2', 'sjsdds', 'hidden');

-- Customers:
insert into t_customers values ('marc', 'fred', 'no credit');
insert into t_customers values ('marc', 'marc', 'big discount');

-- Items:
insert into t_items values ('marc', 99, 12.30::money, 29.99::money);
insert into t_items values ('marc', 98, 12.30::money, 49.99::money);

--
Marc marc(at)bloodnok(dot)com