Row-based security (virtual private database)
От | Marc Munro |
---|---|
Тема | Row-based security (virtual private database) |
Дата | |
Msg-id | 1012438507.29133.4.camel@bloodnok.com обсуждение исходный текст |
Список | 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
В списке pgsql-general по дате отправления: