Row-based security (virtual private database) - Mailing list pgsql-general

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@bloodnok.com