Re: Updates of SE-PostgreSQL 8.4devel patches

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(at)kaigai(dot)gr(dot)jp>