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

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 <kaigai@kaigai.gr.jp>