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@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@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@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@ak.jp.nec.com>