Re: SE-PostgreSQL and row level security

I've tested you patch in windows and in linux and it just work, it's a
killer feature. I have to tank you and all who worked on this.
On windows I have one little problem, mingw does not have "strtok_r"
function and I have to add it myself (see attached file).


A message for postgresql decision board:

   Dear postgresql hackers, if I can do something to push row level acl
for 8.4 please tell me, I do anything to have this feature, it will
help me, and I hope many others, this feature will help to develop
client to postgres applications without a server application or tones
of triggers and viewers.

BogDan,

> 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>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>