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