KaiGai Kohei wrote: > 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.
I will changes this design. It is useful to provide an interface to set up default row acl, like: ALTER TABLE t1 DEFAULT_ROW_ACL='{kaigai=rwd/kaigai,ymj=r/kaigai}'; Please any comments, Thanks > 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, -- KaiGai Kohei <[EMAIL PROTECTED]> -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers