Re: SE-PostgreSQL and row level security

BogDan Vatra wrote:
> Hi,
> [...]
>> In my understanding, the row-level ACLs feature (plus a bit enhancement)
> can
>> help your requirements. I developed it with SE-PostgreSQL in parallel,
> but also postponed to v8.5 series.
>> It enables to assign database ACLs on individual tuples, and filter out
> violated tupled from the result set of SELECT, UPDATE and DELETE.
>> So, it is not very hard. At least, we already have an implementation. :)
>
> Where is it ? I like to try it?

The latest full-functional revision (r1467) is here:
http://code.google.com/p/sepgsql/downloads/list

However, it has a few confliction to the latest CVS HEAD, so I modified
the patch a bit and added a feature to support the replacement rule in
default acl configuration. It is the attached one (r1537).

> If is working why is not included in 8.4?
> IMHO this is a killer feature. I like to try this, and if you want I like
> to give you more feedbacks.

We are standing on open source project, so it is impossible to do anything
in my own way.

However, I guess it will match with what you want to do.

---- Example: drink table is shared by several normal users

postgres=# CREATE TABLE drink (
postgres(# id serial primary key,
postgres(# name text,
postgres(# price int
postgres(# ) with (row_level_acl=on, default_row_acl='{%u=rwx/kaigai}');
NOTICE: CREATE TABLE will create implicit sequence "drink_id_seq" for serial column "drink.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "drink_pkey" for table "drink"
CREATE TABLE
postgres=# GRANT SELECT, UPDATE, INSERT, DELETE ON drink TO public;
GRANT
postgres=# GRANT USAGE ON drink_id_seq TO public;
GRANT
postgres=# INSERT INTO drink (name, price) VALUES ('coke', 120);
INSERT 0 1
postgres=# SELECT security_acl, * FROM drink;
security_acl | id | name | price
---------------------+----+------+-------
{kaigai=rwx/kaigai} | 1 | coke | 120
(1 row)

-- NOTE: "%u" in the default_row_acl is replaced by 'kaigai'

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

postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
--------------+----+------+-------
(0 rows)

-- NOTE: violated tuples are filtered out from the viewpoint of 'ymj'.

postgres=> INSERT INTO drink (name, price) VALUES ('juice', 140), ('beer', 240);
INSERT 0 2
postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
------------------+----+-------+-------
{ymj=rwx/kaigai} | 2 | juice | 140
{ymj=rwx/kaigai} | 3 | beer | 240
(2 rows)

postgres=> \q
[kaigai(at)saba ~]$ psql postgres -Utak
psql (8.4devel)
Type "help" for help.

postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
--------------+----+------+-------
(0 rows)

postgres=> INSERT INTO drink (name, price) VALUES ('tea', 120), ('water', 100);
INSERT 0 2
postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
------------------+----+-------+-------
{tak=rwx/kaigai} | 4 | tea | 120
{tak=rwx/kaigai} | 5 | water | 100
(2 rows)

-- NOTE: A normal user 'tak' cannot see tuples by others.

postgres=> UPDATE drink SET price = price * 1.2;
UPDATE 2
postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
------------------+----+-------+-------
{tak=rwx/kaigai} | 4 | tea | 144
{tak=rwx/kaigai} | 5 | water | 120
(2 rows)

-- NOTE: Only his tuples are affected.

postgres=> UPDATE drink SET security_acl = '{=rwx/tak}';
ERROR: Only owner or superuser can set ACL

-- NOTE: He is not allowed to update ACL

postgres=> \q
[kaigai(at)saba ~]$ psql postgres
psql (8.4devel)
Type "help" for help.

postgres=# SELECT security_acl, * FROM drink;
security_acl | id | name | price
---------------------+----+-------+-------
{kaigai=rwx/kaigai} | 1 | coke | 120
{ymj=rwx/kaigai} | 2 | juice | 140
{ymj=rwx/kaigai} | 3 | beer | 240
{tak=rwx/kaigai} | 4 | tea | 144
{tak=rwx/kaigai} | 5 | water | 120
(5 rows)

-- NOTE: From the viewpoint of superuser again.

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