Updates of SE-PostgreSQL 8.4devel patches (r1120)

KaiGai Kohei wrote:
> OK, I decided to pay my efforts to implement row-level permission
> as soon as possible. Its fundamental idea is same as I posted before.
> It enables to assign database ACL per tuples, and enables to filter
> violated tuples from the result set of query.
>
> I guess we can see the initial working revision within the next week,
> if my design was not wrong.

The following patches are updates of SE-PostgreSQL.
The sixth patch provides row-level database ACL, as I promised before.
Please update the wiki entry to refer the latest patch set.

[1/6] http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r1120.patch
[2/6] http://sepgsql.googlecode.com/files/sepostgresql-pg_dump-8.4devel-3-r1120.patch
[3/6] http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r1120.patch
[4/6] http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r1120.patch
[5/6] http://sepgsql.googlecode.com/files/sepostgresql-tests-8.4devel-3-r1120.patch
[6/6] http://sepgsql.googlecode.com/files/sepostgresql-row_acl-8.4devel-3-r1120.patch

List of updates:
- The patches are rebased for the HEAD of CVS
- Code cleanups in src/backend/security/sepgsql/proxy.c
- Revising permission checks in FK constraint trigger invocation
- Row-level database ACL feature is added as an alternative option for
row-level access controls.

Some more detailed description will be necessary for the last feature newly added.

------------------------------------------------------------
Row-level database ACL feature
------------------------------------------------------------

* Overview

This feature enables to apply row-level access controls based on database ACL.
It works independently from external security facilities. Any tuples can have
its access control list as table having, and it is checked when the executor
fetches the tuple. The violated tuples are filtered from the result set.

Example:
| tuple_acl | id | name | price |
+-----------------------------------+----+-------+-------+
| {kaigai=rwdx/kaigai,ymj=r/kaigai} | 1 | coke | 120 |
| {kaigai=rwdx/kaigai} | 2 | juice | 150 |
| {kaigai=rwdx/kaigai} | 3 | tea | 103 |
| {kaigai=rwdx/kaigai,ymj=r/kaigai} | 4 | beer | 240 |

In this example, unconditional SELECT returns four tuples for kaigai, but same
query returns two tuples for ymj.

We can activate this feature on the build time with "--enable-row-acl" option
for the configure script. If we omit the option, the built binary works same
as the vanilla PostgreSQL.

Example:
$ ./configure --enable-row-acl --enable-cassert --enable-debug

* Security Design

Its access control policy is based on database ACL which is a sort of
discretional access control (DAC), not a mandatory one. So, it allows resource
owner or privileged users to change its access rights.
As the existing database ACL doing, privileged database roles can also ignore
row-level access controls provided by this feature.

The resource owner of tuple is always same as its relation's owner, because
we have to massive number of pg_depend entries if individual tuple has its
owner.

** The kinds of permissions

This feature enables to assign four kind of permissions to tuples.
These are SELECT, UPDATE, DELETE and REFERENCES. The INSERT permission is
not provided, because an object does not exist when the permission should
be evaluated. All insertion of tuples are controled by database ACL of tables.
It also enables to avoid administrative matter, since table owner is always
same as tuple's one.

** Default ACL

We can setup a default ACL to regular tables. This ACL is assigned to newly
inserted tuples implicitly, if no ACLs are given explicitly.
Enhanced SQL statement can be used to set up default ACL, like:

CREATE TABLE t1 (
a int primary key,
b text
) DEFAULT_ACL = '{kaigai=rwdx/kaigai,=r/kaigai}';

When we insert a tuple into a table without default ACL, the newly inserted
tuple has an empty ACL. This feature handles a tuple with empty ACL as if it
has same ACL of its stored table.
Thus, users can access tuples with empty ACL, because they already have
required privileges for tables.

I think the behavior of empty ACL is an arguable item.
One considerable option is to allow anything for public, to follow the current
bahavior in the vanilla PostgreSQL.

** Special cases

In normal cases, this feature simply filters violated tuples, then it shows
users a result set as if violated tuples are not here.
However, there is an exception in foreign key constraint checks.
When a user tries to delete or update a tuple with primary key which is refered
by invisible foreign key, an error is raised to abort the current query and to
keep referential integrity. It enables users to infer the existance of invisible
fereign keys. It is called as a covert channel in security engeering region.
The row-level access control does not care about the covert channel, so you
should not apply this feature on the region which requires covert channel
elimination.
We recommend not to apply natural keys to avoid actual matter.

* 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 should be handled as an optional security feature, not a core one.
The other is it provides several useful foundation to implement enhanced
security feature, like security hooks in strategic points, security system
column support and so on. It strongly helps to implement to store row-level
ACL within individual tuples by the minimum impact towards the core PostgreSQL.

** Security system column

We can set up row-level ACL using security system column named as "tuple_acl".
It enables us to confirm what ACL is assigned, as follows:

kaigai=# SELECT tuple_acl, * FROM t1;
tuple_acl | a | b
------------------------------------+---+-----
{kaigai=rwdx/kaigai} | 1 | aaa
{kaigai=rwdx/kaigai,ymj=rd/kaigai} | 2 | bbb
{kaigai=rwdx/kaigai,ymj=r/kaigai} | 3 | ccc
{kaigai=rwdx/kaigai,ymj=rd/kaigai} | 4 | ddd
{kaigai=rwdx/kaigai} | 5 | eee
(5 rows)

The "tuple_acl" is a system column, but writable.
We can modify the "tuple_acl" with UPDATE statement and a function to make ACL
in text representation, as follows:

kaigai=# UPDATE t1 SET tuple_acl = rowacl_revoke(tableoid, tuple_acl,
'kaigai,ymj',
'update,delete');
UPDATE 5
kaigai=# SELECT tuple_acl, * FROM t1 ORDER BY a;
tuple_acl | a | b
---------------------------------+---+-----
{kaigai=rx/kaigai} | 1 | aaa
{kaigai=rx/kaigai,ymj=r/kaigai} | 2 | bbb
{kaigai=rx/kaigai,ymj=r/kaigai} | 3 | ccc
{kaigai=rx/kaigai,ymj=r/kaigai} | 4 | ddd
{kaigai=rx/kaigai} | 5 | eee
(5 rows)

It shows ACL as a test representation, but it does not store the text for
each tuples. It put an additional Oid value on the padding area of
HeapTupleHeader ad "oid" system column doing. It indicates a tuple within
pg_security system catalog, and it holds internal representation.

** Newly added functions

This feature adds three new functions to make ACL in text.
- TEXT rowacl_grant(Oid tableoid, TEXT old_acl,
TEXT user_ids, TEXT permissions)
- TEXT rowacl_revoke(Oid tableoid, TEXT old_acl,
TEXT user_ids, TEXT permissions)
- TEXT rowacl_revoke_cascade(Oid tableoid, TEXT old_acl,
TEXT user_ids, TEXT permissions)

* Backup/Restore

I added "--row-level-acl" option to pg_dump and pg_dumpall.
It enables to dump tables with default ACL and its contents with ACL.

* Example

[kaigai(at)saba ~]$ psql -Ukaigai postgres
psql (8.4devel)
Type "help" for help.

postgres=# CREATE TABLE tbl (
x int primary key,
y text
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tbl_pkey" for table "tbl"
CREATE TABLE
postgres=# INSERT INTO tbl VALUES (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd');
INSERT 0 4
postgres=# GRANT select ON tbl TO ymj;
GRANT
postgres=# UPDATE tbl SET tuple_acl = rowacl_revoke(tableoid, tuple_acl,
'ymj', 'select') WHERE x in (1,4);
UPDATE 2

postgres=# SELECT tuple_acl, * FROM tbl;
tuple_acl | x | y
----------------------+---+-----
| 2 | bbb
| 3 | ccc
{kaigai=rwdx/kaigai} | 1 | aaa
{kaigai=rwdx/kaigai} | 4 | ddd
(4 rows)

postgres=# \q

[kaigai(at)saba ~]$ psql -Uymj postgres
psql (8.4devel)
Type "help" for help.

postgres=> SELECT tuple_acl, * FROM tbl;
tuple_acl | x | y
-----------+---+-----
| 2 | bbb
| 3 | ccc
(2 rows)

postgres=>

Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>