Re: Updates of SE-PostgreSQL 8.4devel patches - Mailing list pgsql-hackers

The following proposal is idea which I have been considered for several days.

A design of PostgreSQL fine-grained security
--------------------------------------------

* Target

This feature provide a row-level access control feature based on
database acl. Any tuple can have its access control list as table
having, and it is checked when the executor scan the tuple.
The violated tuples are filtered from the result set.

This feature does not provide a column-level access control feature,
because its effort is already in development, and it has SQL standards
to be refered.
But there is no standard for row-level security as far as I know,
so these features should be provided in separated.


* Security architecture

Its access control policy is based on database acl which is a kind of
discretional access control (DAC). It implicitly allows resource owner
or privileged users to change its access rights.
As an existing mechanism doing, privileged database roles can ignore
row-level access controls provided by this feature.

The resource owner of tuple should be a same as table owner, because
we have to massive number of pg_depend entries if individual tuple has
its owner. In addition, here is one more reason related to kinds of
permissions.

Three kind of permissions are provided for tuples. These are SELECT,
UPDATE and DELETE. The violated tuples are filtered out from the result
set of DML statement.
The "INSERT" permission is not provided, because an object does not exist
when the permission should be checked. All insertion of tuples are controled
by database acl of table. Since table owner is always same as tuple's one,
there is no administrative matter.

When we insert a tuple without any explicit acl, an empty acl is assigned.
It allows any kinds of accesses. Only owner can insert a tuple with explicit
acl.


* Implementation

This feature is implemented as a guest of PGACE security framework due to
the following two reasons.
The one is we don't have a standard for row-level security to be refered,
so it is more appropriate to be implemented as an "enhanced" security
mechanism.
The other is it provides several useful foundation to implement enhanced
security feature, like security system column support. We have to store
a database acl for each tuples which have characteristics massive objects
tend to share a small number of acls. The PGACE enables to represent it
with minimum cost.

The following image shows a concept of security system column.
 kaigai=# SELECT pg_tuple_acl, * FROM drink; +--------------------------------+----+--------+-------+ |
pg_tuple_acl        | id |  name  | price | +--------------------------------+----+--------+-------+ |
{kaigai=rwd/kaigai,=ar/kaigai}|  1 | coke   |  130  | | {}                             |  2 | juice  |  150  | |
{kaigai=rwd/kaigai,=rw/kaigai}|  3 | coffee |  200  | |                :               |  : |    :   |   :   |
 

The security system column is writable. The owner can set per-tuple acl
with UPDATE or INSERT statement. The acl statement is a bit complicated,
so the following two functions helps to modify acl.
 pg_tuple_acl_grant(text original, text role, text permissions) pg_tuple_acl_revoke(text original, text role, text
permissions)
 For example:   UPDATE drink SET pg_tuple_acl = pg_tuple_acl_grant(pg_tuple_acl, 'bob', 'select,update');
WHERE id in (5,6,7);
 

One limitation is we can use this feature exclusively with SE-PostgreSQL.
But, I think user's requirements are different.


* Special cases

This feature does not allow to assign ACLs to tuples within system catalogs
to prevent inconsistency with existing access control mechanism.

When a user tries to insert a tuple with duplicate PK, it is failed
independent from its visibility.

When a user tries to insert a new tuple with FK, the refered PK have to
be visible for the owner of refered table, because FK triggers are invoked
with owner's identifier. In similar case, when a user tries to update or
delete a tuple with PK, the owner of refering table have to be able to
perform pre-defined action (like SET NULL).

* Backup/Restore

I'll add '--enable-tuple-acl' option to pg_dump/pg_dumpall.
It enables to dump tables with defined acls, and they can be restored via
writable security system column.

Thanks,
-- 
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>