Re: SE-PostgreSQL and row level security - Mailing list pgsql-hackers

BogDan Vatra wrote:
> 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).

Indeed, I could not find "strtok_r" in any other implementation.
PostgreSQL adopts multi-processes model, so it might not be necessary
to use thread safe interface.

> 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.

I can understand your pains and you want the row-level security stuffs
to be merged within the vanilla v8.4. However, I would like you to
understand we don't have infinite time to review proposed features
for the upcoming v8.4.
Thus, I separated a few features (including row-level facility) to
reduce the scale of patches, and the dieted patches are now under
reviewing.
If we change our strategy *from now*, it will break anything. :(

At least, I'll provide row-level facilities (both DAC and MAC) for the
first CommitFest of v8.5 development cycle. It might not be the best
for you, but it is better than nothing in v8.4.

Thanks,

> 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
>>
>>
>> ------------------------------------------------------------------------
>>
>>


-- 
KaiGai Kohei <kaigai@kaigai.gr.jp>