Re: SE-PostgreSQL and row level security/Alternatives

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

[..]
>
> I guess you concerned about:
> - It is necessary to set up many trigger functions for each tables, which
> provide similar functionality.
> - Users have to specify different names between reference and
> modification.
>
> And, you want to make clear how the row-level access control resolves
it. Is it OK?

Yes.

>
> Your requirement is a simple separation between different users. Thus,
what we have to do is:
> - When a tuple is inserted, the backend automatically assigns an ACL
> which
> allows anything for the current user, but nothing for others.
> - So, when user tries to select, update and delete this table, tuples
> which
> inserted by others to be filtered out from the result set or affected
> rows.
> - Normal users are disallowed to change automatically assigned ACLs.
> (I don't think you want to restrict superuser's operations.)
>
> The row-level ACLs have a functionality named as "default acl".
> It enables table's owner to specify ACLs to be assigned to newly
inserted tuple, like:
>
> CREATE TABLE customer_products (
> id serial,
> :
> ) WITH (default_row_acl='{rwd=kaigai}');
>
> Currently, it does not allow replacement rules like
"{rwd=%current_user}", but it is not a hard enhancement. If such an ACL
is assigned, the tuple is not visible from other users without any
triggers.
>
> For example, please consider when a user "kaigai" insert a tuple into
"customer_products", the "{rwd=kaigai}" is assigned to the tuple, but
the "{rwd=bogdan}" is assigned when a user "bogdan" do same thing.
>
> In this case, any users must not be an owner of the table, because owner
of the table is allowed to change the ACLs.
>
> This is an aside. If you want different access controls, like read-only
for other's tuples but read-writable for own tuples, it will be possible
with different default acl configuration.
>
> Does it help you to understand about the row-level security currently we
are in development?
>

Yes and I like to try it (with more complex situations).
I have C/C++ knowledge maybe I can help you with this.

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

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
>

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

--
KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>

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

I don't want to be to selfish , but AFAIK postgresql is already delayed
(according to PostgreSQL_8.4_Development_Plan page
"http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Development_Plan" beta1
should be out on 1st January 2009), so, what's matter another 2-3 weeks of
delay?
Or, maybe, I'm the only one who consider this a *must to have* feature.

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

Don't understand me wrong, the last thing I want is an unstable postgresql
server.

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

I hope you will provide patches against 8.4.x, I don't want to wait 1-2
years until 8.5 will be out to use this feature. This is why I want to
help you (with more complex testings or *anything* you or others ask me)
to push row level acl to vanilla 8.4, that why I don't think 2-3 more
weeks matter.
PLEASE try to push this patches to 8.4 (I don't see row level acl here:
http://wiki.postgresql.org/wiki/CommitFestInProgress).

Thanks,
BogDan,

[..]

On Fri, Feb 13, 2009 at 02:29:39PM +0200, BogDan Vatra wrote:
> [..]
> >> 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.
>
> I don't want to be to selfish , but AFAIK postgresql is already
> delayed (according to PostgreSQL_8.4_Development_Plan page
> "http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Development_Plan"
> beta1 should be out on 1st January 2009), so, what's matter another
> 2-3 weeks of delay? Or, maybe, I'm the only one who consider this a
> *must to have* feature.

You probably aren't the *only* one, but row-level ACL has unsolved
math problems in it, which means it's a no go for 8.4.

Any resources you could commit to getting those problems solved for
8.5 would be awesome, but no amount of whining for a back-port will
help you or the project, and frankly, resources committed to a
back-port will pretty much stall any attempt to get it into 8.5.

What kinds of resources can you dedicate to the solvable problems, and
when can you start mobilizing them?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

> On Fri, Feb 13, 2009 at 02:29:39PM +0200, BogDan Vatra wrote:
>> [..]
>> >> 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.
>>
>> I don't want to be to selfish , but AFAIK postgresql is already
>> delayed (according to PostgreSQL_8.4_Development_Plan page
>> "http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Development_Plan"
>> beta1 should be out on 1st January 2009), so, what's matter another
>> 2-3 weeks of delay? Or, maybe, I'm the only one who consider this a
>> *must to have* feature.
>
> You probably aren't the *only* one, but row-level ACL has unsolved
> math problems in it, which means it's a no go for 8.4.
>

I'm glad to see I'm not the only one who need row-level ACL, I was think
I'm only a dreamer. I didn't know row-level ACL has math problems in it,
please, can you give me more details ?

>
> Any resources you could commit to getting those problems solved for
> 8.5 would be awesome, but no amount of whining for a back-port will
> help you or the project, and frankly, resources committed to a
> back-port will pretty much stall any attempt to get it into 8.5.
>
> What kinds of resources can you dedicate to the solvable problems, and
> when can you start mobilizing them?
>

What I can do ?
-I can test it in real applications.
-I'll try to fix math problems.
-I can even try to code. For this I need more help with postgresql internals.
-I can donate or sponsor someone else to do things I can't do or things
what are beyond me.
When can start this? If I find what are the math problems and KaiGai Kohei
can give me *only* the row level ACL patch I'll start to work on it even
tomorrow.

I see this feature in December or January on announce mail list (it was a
call for hackers to test SE-Postgres), back there my English suck much
more the it sucks today, you don't even understand what I wanted to say, I
just hoped someone else will try to push this in 8.4, it seems that was a
bad decision.

Thanks for your reply,
BogDan,

[..]

BogDan Vatra wrote:
>> On Fri, Feb 13, 2009 at 02:29:39PM +0200, BogDan Vatra wrote:
>>> [..]
>>>>> 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.
>>> I don't want to be to selfish , but AFAIK postgresql is already
>>> delayed (according to PostgreSQL_8.4_Development_Plan page
>>> "http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Development_Plan"
>>> beta1 should be out on 1st January 2009), so, what's matter another
>>> 2-3 weeks of delay? Or, maybe, I'm the only one who consider this a
>>> *must to have* feature.
>> You probably aren't the *only* one, but row-level ACL has unsolved
>> math problems in it, which means it's a no go for 8.4.
>>
>
> I'm glad to see I'm not the only one who need row-level ACL, I was think
> I'm only a dreamer. I didn't know row-level ACL has math problems in it,
> please, can you give me more details ?

At the previous discussion, two items were pointed out.

The one is called as covert channel. When a tuple with PK is refered by
one or more tuples with FK, row-level control prevents to update or delete
the PK, even if the FK is invisible from users. It allows users to infer
existence of invisible FK. However, our evaluation criteria (ISO15408, CC)
does not consider it as a problem. The requirements for security feature
depends on its purpose and environments in use. The purpose of SE-PostgreSQL
is to improve security in general enterprise class systems, such as other
commercial databases (Oracle Label Security. etc...). In this class, we
don't need to eliminate the covert channel. What we should do here is to
provide an explicit documentation about this behavior to help user's decision.

The other is an interaction with optimization. The row-level security
works as if an additional condition is automatically appended to the target
relations. The current optimizer works without consideration for row-level
security, so it can make undesirable optimization. For example, upcoming
join-removal stuff assumes here is a FK tuple at least, when we do outer
join between two relations with FK constraints. In this case, we can replace
the outer join by inner one with performance gain. However, row-level security
can filter out invisible FK tuples from users. So, it is necessary to provide
a hint to restain such kind of optimization on the relations with row-level
security.

However, these two items are not essentials here.
In the previous discussion, I was pointed out that a single large patch
is hard to review for commiters, so we should separate a fullset feature
into several parts and to be reviewed step-by-step.
One other fact is we don't have infinite time to the upcoming v8.4.
Thus, I accepted to separate a few features for getting it merged at v8.4
even if it does not have fullset facilities.

>> Any resources you could commit to getting those problems solved for
>> 8.5 would be awesome, but no amount of whining for a back-port will
>> help you or the project, and frankly, resources committed to a
>> back-port will pretty much stall any attempt to get it into 8.5.
>>
>> What kinds of resources can you dedicate to the solvable problems, and
>> when can you start mobilizing them?
>>
>
> What I can do ?
> -I can test it in real applications.
> -I'll try to fix math problems.
> -I can even try to code. For this I need more help with postgresql internals.
> -I can donate or sponsor someone else to do things I can't do or things
> what are beyond me.
> When can start this? If I find what are the math problems and KaiGai Kohei
> can give me *only* the row level ACL patch I'll start to work on it even
> tomorrow.

Currently we are waiting for comitter's review.

On the other hand, I also begain to prepare to submit patches for rest of
features at the next development phase, after the first features get merged.

* CVS HEAD + security label/acl management stuff
http://code.google.com/p/sepgsql/source/browse/trunk/sysatt
* trunk/sysatt + row-level security stuff
http://code.google.com/p/sepgsql/source/browse/trunk/rowlv

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

On Mon, Feb 16, 2009 at 11:10:19AM +0900, KaiGai Kohei wrote:
> At the previous discussion, two items were pointed out.
>
> The one is called as covert channel. When a tuple with PK is refered by
> one or more tuples with FK, row-level control prevents to update or delete
> the PK, even if the FK is invisible from users. It allows users to infer
> existence of invisible FK.

One thing I keep missing in this discussion: the term "row-level
security" in the above senstence in not the important part. Right now
you can revoke SELECT permission on a table with a foreign key and it
will still prevent UPDATEs and DELETEs of the primary key, allowing
users to infer the existance of an invisible FK.

This is the same "covert channel", so why is it a problem for
SE-Postgres and not for normal Postgres?

Is it because revoking permissions is not considered a security
mechanism or something? I'm sure it's obvious, I'm just not seeing it.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Martijn van Oosterhout wrote:
> On Mon, Feb 16, 2009 at 11:10:19AM +0900, KaiGai Kohei wrote:
>> At the previous discussion, two items were pointed out.
>>
>> The one is called as covert channel. When a tuple with PK is refered by
>> one or more tuples with FK, row-level control prevents to update or delete
>> the PK, even if the FK is invisible from users. It allows users to infer
>> existence of invisible FK.
>
> One thing I keep missing in this discussion: the term "row-level
> security" in the above senstence in not the important part. Right now
> you can revoke SELECT permission on a table with a foreign key and it
> will still prevent UPDATEs and DELETEs of the primary key, allowing
> users to infer the existance of an invisible FK.
>
> This is the same "covert channel", so why is it a problem for
> SE-Postgres and not for normal Postgres?

Please note that I don't consider it is a problem, even if SE-PostgreSQL.

Both of SE-PostgreSQL and vanilla PostgreSQL don't give an assurance to
eliminate information leaks via such kind of covert channels, so they
don't violate any specifications of them. Thus, it is not a problem.

When we revoke SELECT permission on FK table, user cannot select on
the table directly. It surely follows the specification as documented.

In generally, security requirements depend on its environment in use,
value of information asset to be managed and so on.
If a user really needs to eliminate covert channels, he can choose
a product which gives an assurance for them.
However, it has a tradeoff in closed source, expensive price, not
widely used and so on. :)

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

KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com> writes:

> Martijn van Oosterhout wrote:
>> On Mon, Feb 16, 2009 at 11:10:19AM +0900, KaiGai Kohei wrote:
>>> At the previous discussion, two items were pointed out.
>>>
>>> The one is called as covert channel. When a tuple with PK is refered by
>>> one or more tuples with FK, row-level control prevents to update or delete
>>> the PK, even if the FK is invisible from users. It allows users to infer
>>> existence of invisible FK.
>>
>> One thing I keep missing in this discussion: the term "row-level
>> security" in the above senstence in not the important part. Right now
>> you can revoke SELECT permission on a table with a foreign key and it
>> will still prevent UPDATEs and DELETEs of the primary key, allowing
>> users to infer the existance of an invisible FK.
>>
>> This is the same "covert channel", so why is it a problem for
>> SE-Postgres and not for normal Postgres?
>
> Please note that I don't consider it is a problem, even if SE-PostgreSQL.
>
> Both of SE-PostgreSQL and vanilla PostgreSQL don't give an assurance to
> eliminate information leaks via such kind of covert channels, so they
> don't violate any specifications of them. Thus, it is not a problem.

If that's true then I don't see why we would try to automatically hide records
you don't have access to. The only reason to do so is to try to close these
covert channels and if we can't do that then I don't see any benefit to doing
so.

If users want to select "all matching records the user has access to" they
should just put that in the WHERE clause (and we should provide a convenient
function to do so). If we implicitly put it in the WHERE clause then
effectively we're providing incorrect answers to the SQL query they did
submit.

This is a big part of the "breaking SQL semantics" argument. Since the
automatic row hiding provides different answers than the SQL query is really
requesting it means we can't trust the results to follow the usual rules.

I think there's more to it though. Tom pointed out some respects in which the
hooks are too late and too low level to really know what privilege set is in
effect. The existing security checks are all performed earlier in plan
execution, not at low level row access routines. This is a more fundamental
change which you'll have to address before for *any* row level security scheme
even without the automatic data hiding.

So, assuming the SELinux integration for existing security checks is committed
for 8.4 I think the things you need to address for 8.5 will be:

1) Row level security checks in general (whether SELinux or native Postgres
security model) and showing that the hooks are in the right places for
Tom's concerns.

2) Dealing with the scaling to security labels for billions of objects and
dealing with garbage collecting unused labels. I think it might be simpler
to have security labels be explicitly allocated and dropped instead of
creating them on demand.

3) The data hiding scheme -- which frankly I think is dead in the water. It
amounts to a major change to the SQL semantics where every query
effectively has a volatile function in it which produces different answers
for different users. And it doesn't accomplish anything since the covert
channels it attempts to address are still open.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!

>>> This is the same "covert channel", so why is it a problem for
>>> SE-Postgres and not for normal Postgres?
>> Please note that I don't consider it is a problem, even if SE-PostgreSQL.
>>
>> Both of SE-PostgreSQL and vanilla PostgreSQL don't give an assurance to
>> eliminate information leaks via such kind of covert channels, so they
>> don't violate any specifications of them. Thus, it is not a problem.
>
> If that's true then I don't see why we would try to automatically hide records
> you don't have access to. The only reason to do so is to try to close these
> covert channels and if we can't do that then I don't see any benefit to doing
> so.

It is incorrect.
It seems to me you confound a covert channel and granularity in access
controls. The purpose of row-level security is to provide users more
flexibility in access controls, not related to covert channels.

> If users want to select "all matching records the user has access to" they
> should just put that in the WHERE clause (and we should provide a convenient
> function to do so). If we implicitly put it in the WHERE clause then
> effectively we're providing incorrect answers to the SQL query they did
> submit.
>
> This is a big part of the "breaking SQL semantics" argument. Since the
> automatic row hiding provides different answers than the SQL query is really
> requesting it means we can't trust the results to follow the usual rules.

When he want to see all the tuple within a relation, it is necessary to
invoke SQL query with highest privileges.

Since we don't have row-level granularity now, same query always returns
same result (if client has enough privileges on tables/columns) due to
the uniform access controls on tuples.
But, it is not correct when once we have the row-level granulariry.

Could you imagine a case when we see a table via a view with a condition
depending on user's identifier. I don't think it breaks something.
In fact, prior commercial database with row-level security (Oracle, DB2)
adopts similar way which implicitly modify WHERE clause.

> I think there's more to it though. Tom pointed out some respects in which the
> hooks are too late and too low level to really know what privilege set is in
> effect. The existing security checks are all performed earlier in plan
> execution, not at low level row access routines. This is a more fundamental
> change which you'll have to address before for *any* row level security scheme
> even without the automatic data hiding.

SE-PostgreSQL also checks table/column level privilges on the head of plan
execution, as if the existing security checks doing. Please check my patches.
(It is also same in r1530.)

At first, please note that the existing security checks don't have row-level
granularity now. So, it is necessary to add a condition on WHERE clause, or
add a trigger on target relations, as BogDan did at first.
(At least, it is a simple way to achive row-level controls now.)
SE-PostgreSQL checks reader permission on tuples just after evaluation of
conditional clause on ExecScan(), and also checks writter permission just
after before-row triggers invoked.
I don't think it is too late. Please note that we cannot know what tuples
are accessed until we actually execute the given query.

> So, assuming the SELinux integration for existing security checks is committed
> for 8.4 I think the things you need to address for 8.5 will be:

Anyway, the reason why we postponed row-level features was lack of time
to review, to discuss, to conclude and others.
I don't forget to open discussion these issue on the v8.5 development cycle.
However, I think it is unproductive to heat up row-level facilities before
the first step.

Thanks,
--
KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>

>>> Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
> And it doesn't accomplish anything since the covert
> channels it attempts to address are still open.

Hyperbole. We're not very likely to go the SE-* route, but I can say
that we've got some of the issues it addresses, and it is a very
different thing for someone to know, for example, that there is a
paternity case 2009PA000023 in a county, and for them to know what the
case caption is (which includes the names).

-Kevin

On Mon, Feb 16, 2009 at 10:54:32AM +0000, Gregory Stark wrote:
> > Both of SE-PostgreSQL and vanilla PostgreSQL don't give an assurance to
> > eliminate information leaks via such kind of covert channels, so they
> > don't violate any specifications of them. Thus, it is not a problem.
>
> If that's true then I don't see why we would try to automatically hide records
> you don't have access to. The only reason to do so is to try to close these
> covert channels and if we can't do that then I don't see any benefit to doing
> so.

To me the reason you automatically hide records is because otherwise
everyone has to rewrite all of their queries since accessing anything
you don't have rights to would generate an error (and abort the
transaction and be a covert channel itself).

When you search something in your webmail, you don't have to tick the
box saying "please only show me emails I have permission to access".
That's implied, just like all MACs in the real world.

> If users want to select "all matching records the user has access to" they
> should just put that in the WHERE clause (and we should provide a convenient
> function to do so). If we implicitly put it in the WHERE clause then
> effectively we're providing incorrect answers to the SQL query they did
> submit.

Eh, the result of the query is the correct answer with respect to the
model its applied to. Users of a MAC system are aware that the model
they're working may not be totally logical but setting up an illogical
system is the admin's fault, not SE-Postgres itself.

BTW, it sounds to me like you're suggesting the entire rule system
breaks SQL semantics because it's not executing the query the user
gave. Indeed, if we go down the road of requiring users to apply their
own permission checks, what SE-Postgres users could do is use the rule
system to apply the checks automatically.

Perhaps that's the way to go. For 8.4 provide something that people can
stuff in the where clause and users can use the rule system to apply it
globally.

> 3) The data hiding scheme -- which frankly I think is dead in the water. It
> amounts to a major change to the SQL semantics where every query
> effectively has a volatile function in it which produces different answers
> for different users. And it doesn't accomplish anything since the covert
> channels it attempts to address are still open.

This doesn't make sense to me. The covert channel you're referring to
leaks the primary key, whereas what it's hiding is an entire row. These
are in no way the same thing, and the hiding is more of a convenience
than anything else (aborted transactions are more annoying). As for
giving different answers to different users, this is precisely what
Veil does (and information_schema for that matter).

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

On Mon, Feb 16, 2009 at 12:08 PM, KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp> wrote:
>>>> This is the same "covert channel", so why is it a problem for
>>>> SE-Postgres and not for normal Postgres?
>>>
>>> Please note that I don't consider it is a problem, even if SE-PostgreSQL.
>>>
>>> Both of SE-PostgreSQL and vanilla PostgreSQL don't give an assurance to
>>> eliminate information leaks via such kind of covert channels, so they
>>> don't violate any specifications of them. Thus, it is not a problem.
>>
>> If that's true then I don't see why we would try to automatically hide
>> records
>> you don't have access to. The only reason to do so is to try to close
>> these
>> covert channels and if we can't do that then I don't see any benefit to
>> doing
>> so.
>
> It is incorrect.
> It seems to me you confound a covert channel and granularity in access
> controls. The purpose of row-level security is to provide users more
> flexibility in access controls, not related to covert channels.

No, I claim it's you that's confounding the data hiding scheme with
row-level granular access controls.

If a user types "SELECT count(*) from table" they should either get
the correct count of records from that table or get a permission
failure.

If they want to get the count of records for which they have read
access they should have to write "SELECT count(*) from table where
access_check(security_label, current_user())" or something like that.

The only reason to make the above automatic is to hide from the user
the fact that there are records they're not seeing. Since we can't do
that effectively there's no point in having it at all, especially
since it breaks the interpretation of the query in ways that interfere
with other functionality.

--
greg

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
>> And it doesn't accomplish anything since the covert
>> channels it attempts to address are still open.

> Hyperbole. We're not very likely to go the SE-* route, but I can say
> that we've got some of the issues it addresses, and it is a very
> different thing for someone to know, for example, that there is a
> paternity case 2009PA000023 in a county, and for them to know what the
> case caption is (which includes the names).

Which is something you could implement with standard SQL column
permissions; and could *not* implement with row-level access
permissions. Row-level is all or nothing for each row.

regards, tom lane

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> One thing I keep missing in this discussion: the term "row-level
> security" in the above senstence in not the important part. Right now
> you can revoke SELECT permission on a table with a foreign key and it
> will still prevent UPDATEs and DELETEs of the primary key, allowing
> users to infer the existance of an invisible FK.

> This is the same "covert channel", so why is it a problem for
> SE-Postgres and not for normal Postgres?

The reason it's a problem for SE-Postgres is that the entire row-level
security feature is advertised on the premise that it allows you to
hide the existence of data; a claim not made by regular SQL. If the
feature doesn't do what it's claimed to do then it's fair to ask why
have it.

regards, tom lane

>> Both of SE-PostgreSQL and vanilla PostgreSQL don't give an assurance to
>> eliminate information leaks via such kind of covert channels, so they
>> don't violate any specifications of them. Thus, it is not a problem.
>
> If that's true then I don't see why we would try to automatically hide records
> you don't have access to. The only reason to do so is to try to close these
> covert channels and if we can't do that then I don't see any benefit to doing
> so.

So, this email really got me thinking, and after thinking about it for
a while I think you're wrong about this part. :-)

If we had no security in the database at all (no table or column
privileges, no login roles or privileges - everyone connects as
superuser!) then we wouldn't have any covert channels either. Covert
channels, by definition, are methods by which access controls can be
partially or completely subverted, so if there are no access controls,
there are no covert channels, either. In some sense, covert channels
are the degree to which its possible to work around the overt security
controls.

It's worth noting that this is almost never zero. There are papers
out there about subverting SSH by measuring the length of time that
the remote machine takes to reject your request for access and
inferring from that at what stage of the process authentication
failed, and from that eventually being able to crack the system. Of
course they only got it working on a local LAN with a fast switch and
probably not a lot of other traffic on the network, but so what? The
point is that there is information there, as it is in every system,
and so the question is not "Are there covert channels?" but "Are the
covert channels sufficiently large so as to render the system not
useful in the real world?".

I haven't seen anyone present a shred of evidence that this would be
the case in SE-PostgreSQL. Even if you can infer the existence of a
referring key, as Kevin Grittner just pointed out in another email on
this thread, that may not be that helpful. The information is likely
to be some sort of unexciting key, like an integer or a UUID or (as in
Kevin's example) a sequentially assigned case number. Maybe if you're
really lucky and have just the right set of permissions you'll be able
to infer the size of the referring table, and there could be
situations where that is sensitive information, but Kevin's example is
a good example of a case where it's not: the case load of the family
court (or whatever) is not that much of a secret. The names of the
people involved in the cases is.

> If users want to select "all matching records the user has access to" they
> should just put that in the WHERE clause (and we should provide a convenient
> function to do so). If we implicitly put it in the WHERE clause then
> effectively we're providing incorrect answers to the SQL query they did
> submit.
>
> This is a big part of the "breaking SQL semantics" argument. Since the
> automatic row hiding provides different answers than the SQL query is really
> requesting it means we can't trust the results to follow the usual rules.

The requested functionality is no different in its effect than writing
a custom view for each user that enforces the desired permissions
checks, but it is a lot more convenient.

> I think there's more to it though. Tom pointed out some respects in which the
> hooks are too late and too low level to really know what privilege set is in
> effect. The existing security checks are all performed earlier in plan
> execution, not at low level row access routines. This is a more fundamental
> change which you'll have to address before for *any* row level security scheme
> even without the automatic data hiding.
>
> So, assuming the SELinux integration for existing security checks is committed
> for 8.4 I think the things you need to address for 8.5 will be:
>
> 1) Row level security checks in general (whether SELinux or native Postgres
> security model) and showing that the hooks are in the right places for
> Tom's concerns.
>
> 2) Dealing with the scaling to security labels for billions of objects and
> dealing with garbage collecting unused labels. I think it might be simpler
> to have security labels be explicitly allocated and dropped instead of
> creating them on demand.
>
> 3) The data hiding scheme -- which frankly I think is dead in the water. It
> amounts to a major change to the SQL semantics where every query
> effectively has a volatile function in it which produces different answers
> for different users. And it doesn't accomplish anything since the covert
> channels it attempts to address are still open.

One thing that I do think is a legitimate concern is performance,
which I think is some of what you're getting at here. An iterative
lookup of the security ID for each row visited basically amounts to
forcing row-level security to be checked using a nested loop plan, but
it's probably not hard to construct scenarios where that isn't a very
good plan. Surely we want to be able to index the relation on the
security ID and do bitmap index scans, etc.

...Robert

Hi,

On 02/16/2009 03:53 PM, Tom Lane wrote:
>> Hyperbole. We're not very likely to go the SE-* route, but I can say
>> that we've got some of the issues it addresses, and it is a very
>> different thing for someone to know, for example, that there is a
>> paternity case 2009PA000023 in a county, and for them to know what the
>> case caption is (which includes the names).
> Which is something you could implement with standard SQL column
> permissions; and could *not* implement with row-level access
> permissions. Row-level is all or nothing for each row.
I guess he is talking about 2009PA000023 being a foreign key - about
which you could get information via the aforementioned covert channels,
even if you cannot read that row.

That

Andres

>>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
>>> And it doesn't accomplish anything since the covert
>>> channels it attempts to address are still open.
>
>> Hyperbole. We're not very likely to go the SE-* route, but I can
say
>> that we've got some of the issues it addresses, and it is a very
>> different thing for someone to know, for example, that there is a
>> paternity case 2009PA000023 in a county, and for them to know what
the
>> case caption is (which includes the names).
>
> Which is something you could implement with standard SQL column
> permissions; and could *not* implement with row-level access
> permissions. Row-level is all or nothing for each row.

Well, 99% of the cases are a matter of public record and we *do* have
to show case caption. It's not the caption column in general which
must be hidden, nor the fact that a row with that primary key exists,
but the *contents* of certain rows. We can identify those based on
case type, and a class code which is essentially a finer-grained
categorization of cases, and limit who can see what based on
permissions tables we maintain. We do that in the application, but I
can certainly understand and sympathize with those who want to control
that at the level allowed by SE-* technology.

-Kevin

On Mon, Feb 16, 2009 at 9:53 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
>>> And it doesn't accomplish anything since the covert
>>> channels it attempts to address are still open.
>
>> Hyperbole. We're not very likely to go the SE-* route, but I can say
>> that we've got some of the issues it addresses, and it is a very
>> different thing for someone to know, for example, that there is a
>> paternity case 2009PA000023 in a county, and for them to know what the
>> case caption is (which includes the names).
>
> Which is something you could implement with standard SQL column
> permissions; and could *not* implement with row-level access
> permissions. Row-level is all or nothing for each row.

Huh?

The scenario I think we're talking about here is a user who has
permissions to SELECT and INSERT and UPDATE a subset of the records in
a table. Because there is a unique index on the case name, if he
happens to try to insert a new case called 2009PA000023, it will fail,
and he will gain some information about a row he can't see. But since
that information is a sequentially assigned key, it's not very useful
- it doesn't reveal any of the other attributes of the unseen row,
which is where the really sensitive data is.

Column-level privileges would not let you implement this at all.

...Robert

>>> Andres Freund <andres(at)anarazel(dot)de> wrote:
> I guess he is talking about 2009PA000023 being a foreign key - about

> which you could get information via the aforementioned covert
channels,
> even if you cannot read that row.

Exactly. Sorry I didn't make that more clear.

-Kevin

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> ... so the question is not "Are there covert channels?" but "Are the
> covert channels sufficiently large so as to render the system not
> useful in the real world?".

Fair enough.

> I haven't seen anyone present a shred of evidence that this would be
> the case in SE-PostgreSQL.

Sorry, but the burden of proof is in the other direction.

In any case, this was already discussed in detail in previous threads.
It's possible that you could make the database adequately secure given
appropriate design rules, such as "only use synthetic keys as foreign
keys". (For instance consider Kevin's example of needing to hide the
case caption. If the caption had been used directly as PK then he'd
have a problem.) We have seen no evidence that anyone has a worked-out
set of design rules that make a SE-Postgres database secure against
these issues, so the whole thing is pie in the sky.

regards, tom lane

>>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> We have seen no evidence that anyone has a worked-out
> set of design rules that make a SE-Postgres database secure against
> these issues, so the whole thing is pie in the sky.

I've seen several mentions of the rule "Don't use a column containing
data you want to secure as part of the primary key." mentioned several
times in these threads. I think that just might be the complete set.
Can anyone show that it's not?

-Kevin

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> We have seen no evidence that anyone has a worked-out
>> set of design rules that make a SE-Postgres database secure against
>> these issues, so the whole thing is pie in the sky.

> I've seen several mentions of the rule "Don't use a column containing
> data you want to secure as part of the primary key." mentioned several
> times in these threads. I think that just might be the complete set.
> Can anyone show that it's not?

You've still got the burden of proof backwards... but just as a
counterexample to that phrasing, I'll note that FKs can be set up
against columns other than a primary key. If the attacker has
insert/update privilege then *any* unique constraint represents
a possible covert channel.

regards, tom lane

On Mon, Feb 16, 2009 at 10:11 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I haven't seen anyone present a shred of evidence that this would be
>> the case in SE-PostgreSQL.
>
> Sorry, but the burden of proof is in the other direction.
>
> In any case, this was already discussed in detail in previous threads.
> It's possible that you could make the database adequately secure given
> appropriate design rules, such as "only use synthetic keys as foreign
> keys". (For instance consider Kevin's example of needing to hide the
> case caption. If the caption had been used directly as PK then he'd
> have a problem.) We have seen no evidence that anyone has a worked-out
> set of design rules that make a SE-Postgres database secure against
> these issues, so the whole thing is pie in the sky.

I agree that it would be useful to have some documentation that
outlines the new covert channels that this creates (by virtue of
blocking the overt channels), approaches to addressing those that can
be addressed, and warnings about those that can't. I think the only
ones we've been able to come up with so far are:

1. Unique constraints. If you have insert or update privileges on a
table with unique indices, you might be able to infer the existence of
a hidden row because your insert or update fails despite the fact that
it doesn't conflict with any row that is visible to you.

2. Foreign-key constraints.
(A) If you have update or delete privileges on a table that is
referenced by foreign keys, you might be able to infer the existence
of a hidden, referring row because your update or delete fails.
(B) If you have select privileges on a table which references foreign
keys, you might be able to infer the existence of hidden, referred-to
row on the basis of the fact that there is no visible row to which
your row refers.

Is there anything else?

Problem 1 can be addressed by avoiding creating unique indices on
columns that contain potentially sensitive data. Probably the most
common examples of unique indices are primary keys, so use non-natural
primary keys. If you want to avoid inferences about the cardinality
of your data set, don't use serial: instead use UUIDs or similar.

Problems 2(A) and 2(B) can be addressed by setting up the security
labels and policy so that any particular role can see both the
referring row and the referred row, or neither. Alternatively, the
damage from either can be minimized by using randomized, non-natural
foreign keys as above, so that you learn only that there is a row out
there, but nothing interesting about its contents.

Also, don't problems 2(A) and 2(B) already exist with just table-level
DAC? What happens today if you give permission on the referring table
but not the referred-to table, or the other way around?

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> 2. Foreign-key constraints.
> (A) If you have update or delete privileges on a table that is
> referenced by foreign keys, you might be able to infer the existence
> of a hidden, referring row because your update or delete fails.

Also the other direction (insert or update on the referencing table
lets you infer contents of the referenced table).

> Is there anything else?

If we ever had SQL-spec ASSERTIONS, they'd create hard-to-analyze
issues of this sort. I've also seen people abuse CHECK constraints
in ways that expose data cross-row (they shouldn't do so, but...)

> Also, don't problems 2(A) and 2(B) already exist with just table-level
> DAC? What happens today if you give permission on the referring table
> but not the referred-to table, or the other way around?

I'm repeating myself, but: the reason it isn't a problem now is that
plain SQL doesn't claim to be able to hide the existence of data.

regards, tom lane

On Mon, Feb 16, 2009 at 10:59 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> 2. Foreign-key constraints.
>> (A) If you have update or delete privileges on a table that is
>> referenced by foreign keys, you might be able to infer the existence
>> of a hidden, referring row because your update or delete fails.
>
> Also the other direction (insert or update on the referencing table
> lets you infer contents of the referenced table).

Ah, right.

>> Is there anything else?
>
> If we ever had SQL-spec ASSERTIONS, they'd create hard-to-analyze
> issues of this sort. I've also seen people abuse CHECK constraints
> in ways that expose data cross-row (they shouldn't do so, but...)

I can imagine someone doing that. :-(

>> Also, don't problems 2(A) and 2(B) already exist with just table-level
>> DAC? What happens today if you give permission on the referring table
>> but not the referred-to table, or the other way around?
>
> I'm repeating myself, but: the reason it isn't a problem now is that
> plain SQL doesn't claim to be able to hide the existence of data.

I'm not sure I understand what you mean by that. I expect that if I
deny a particular user access to SELECT from a particular table the
system will throw a permissions error if that user later enters
"SELECT * FROM <table-name>". I don't expect that the system will
foresee every possible alternative way that a user might able to infer
something about the contents of that table and block it. I similarly
expect that if I install SE-PostgreSQL and configure it to filter out
certain rows from accesses to certain tables, those rows will in fact
be filtered. I still don't expect it to foresee every possible
alternative way that a user might be able to infer something about the
contents of the data to which the user does not have direct access.

Is this fundamentally a semantic issue? If there's an asymmetry here
in what is being claimed, I'm not seeing it.

...Robert

On Mon, Feb 16, 2009 at 4:14 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> I'm not sure I understand what you mean by that. I expect that if I
> deny a particular user access to SELECT from a particular table the
> system will throw a permissions error if that user later enters
> "SELECT * FROM <table-name>". I don't expect that the system will
> foresee every possible alternative way that a user might able to infer
> something about the contents of that table and block it. I similarly
> expect that if I install SE-PostgreSQL and configure it to filter out
> certain rows from accesses to certain tables, those rows will in fact
> be filtered. I still don't expect it to foresee every possible
> alternative way that a user might be able to infer something about the
> contents of the data to which the user does not have direct access.
>
> Is this fundamentally a semantic issue? If there's an asymmetry here
> in what is being claimed, I'm not seeing it.

Well the asymmetry is that in the former case the verb is "deny" and
the latter it's "filter"...

--
greg

On 02/16/2009 04:23 PM, Kevin Grittner wrote:
>>>> Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> We have seen no evidence that anyone has a worked-out
>> set of design rules that make a SE-Postgres database secure against
>> these issues, so the whole thing is pie in the sky.
> I've seen several mentions of the rule "Don't use a column containing
> data you want to secure as part of the primary key." mentioned several
> times in these threads. I think that just might be the complete set.
> Can anyone show that it's not?
Well, there is at least one additional which currently is not discussed,
namely statistics/EXPLAIN [ANALYZE].
And it hits the often proposed method of using VIEWs as a form of row
level access control quite a bit harder than SE-Postgresql:

1. The planner selection estimates might tell you more than allowed
2. The planner execution statistics might tell you even more (view based
security only)

Generally view based security is not really secure if somebody is
allowed to create own functions (PGs restricted views for example are not):
Create a function with very low cost like:

CREATE OR REPLACE FUNCTION do_tell(anyelement)
RETURNS bool
COST 0.1
VOLATILE
LANGUAGE plpgsql
AS $body$
BEGIN
raise notice 'hah: %s', $1::text;
return true;
END;
$body$;

And do a simple query to the restricted schema:

SELECT * FROM restricted_view WHERE do_tell(secret_column);

PG will now happily raise NOTICEs for any columns because do_tell will
be checked first.

Thinking about it, this even sounds like a more general security issue?

Andres

On Mon, Feb 16, 2009 at 11:21 AM, Greg Stark <stark(at)enterprisedb(dot)com> wrote:
> On Mon, Feb 16, 2009 at 4:14 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>
>> I'm not sure I understand what you mean by that. I expect that if I
>> deny a particular user access to SELECT from a particular table the
>> system will throw a permissions error if that user later enters
>> "SELECT * FROM <table-name>". I don't expect that the system will
>> foresee every possible alternative way that a user might able to infer
>> something about the contents of that table and block it. I similarly
>> expect that if I install SE-PostgreSQL and configure it to filter out
>> certain rows from accesses to certain tables, those rows will in fact
>> be filtered. I still don't expect it to foresee every possible
>> alternative way that a user might be able to infer something about the
>> contents of the data to which the user does not have direct access.
>>
>> Is this fundamentally a semantic issue? If there's an asymmetry here
>> in what is being claimed, I'm not seeing it.
>
> Well the asymmetry is that in the former case the verb is "deny" and
> the latter it's "filter"...

Oh. Somehow I have a hard time getting worked up about that. It
seems like a trivial concession to ease of use. Otherwise, a query
with 12 joins will need to have 13 additional clauses explicitly
appended to the WHERE clause to prevent errors, and I'm not sure what
that really buys you. Surely I'm never going to intentionally write a
query that doesn't include the relevant permissions-based exclusion,
since otherwise my queries will blow up in my face.

I'm a little bothered by this issue with respect to INSERT, UPDATE,
and DELETE, since it's possible that I have permission to see rows but
not updated them, and it would be a little weird if select and update
with equivalent where clauses operated on different sets of records
(although that can happen anyway, because of BEFORE triggers, and it's
pretty irritating). It's not clear that there's a clean solution
here, but it's at least food for thought.

But for SELECT it just seems like nuisance value.

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I'm a little bothered by this issue with respect to INSERT, UPDATE,
> and DELETE, since it's possible that I have permission to see rows but
> not updated them, and it would be a little weird if select and update
> with equivalent where clauses operated on different sets of records
> (although that can happen anyway, because of BEFORE triggers, and it's
> pretty irritating). It's not clear that there's a clean solution
> here, but it's at least food for thought.

80% of the problem here is exactly that the proposed solution doesn't
seem very semantically clean. And once we accept it we're going to be
stuck with it for a long time --- compare for instance the multiple
serious annoyances with RULEs, which we can't fix easily because of
backwards compatibility considerations.

regards, tom lane

On Mon, Feb 16, 2009 at 11:43 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I'm a little bothered by this issue with respect to INSERT, UPDATE,
>> and DELETE, since it's possible that I have permission to see rows but
>> not updated them, and it would be a little weird if select and update
>> with equivalent where clauses operated on different sets of records
>> (although that can happen anyway, because of BEFORE triggers, and it's
>> pretty irritating). It's not clear that there's a clean solution
>> here, but it's at least food for thought.
>
> 80% of the problem here is exactly that the proposed solution doesn't
> seem very semantically clean. And once we accept it we're going to be
> stuck with it for a long time --- compare for instance the multiple
> serious annoyances with RULEs, which we can't fix easily because of
> backwards compatibility considerations.

I've found rules in their current form to be nearly useless, except
for views, which are wonderful. I do everything else with triggers.

With reference to row-level security, most of the complaining about
this feature has been along the lines of "I don't like the idea that
rows get filtered from my result-set that I didn't ask to have
filtered". To me, the fact that you didn't have to ask seems like a
huge convenience, and I can't imagine why you'd want it otherwise.
Sure, the behavior needs to be documented, but that doesn't seem like
a big deal.

There may well be more substantive issues here but I've been following
this discussion fairly closely and I don't have a clear understanding
of what they are.

...Robert

On Mon, Feb 16, 2009 at 12:18 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> With reference to row-level security, most of the complaining about
> this feature has been along the lines of "I don't like the idea that
> rows get filtered from my result-set that I didn't ask to have
> filtered".

yeah! because was filtered by powers above yours... ;)

i thing row level acl it's good feature for those that *really* need
it, as every other solution this is not for everyone and could and
will be misused sometimes... as far as the code maintain readibility
and doesn't interfer in an instalation that doesn't include
--enable-selinux i'm in favor of including it...

> To me, the fact that you didn't have to ask seems like a
> huge convenience, and I can't imagine why you'd want it otherwise.
> Sure, the behavior needs to be documented, but that doesn't seem like
> a big deal.
>

not only a convenience, it's a way to enforce policies that cannot be
circumvented easily from programming (if you have very secret info
that cost a lot, you can start being paranoic even of your own
developing team ;)

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

A couple of thoughts:
First off, I think the inclusion of row level security and an
unprecendented integration with OS level security are a stunning
example of what makes Open Source so much cooler than closed source
products. Great job! (and the speed of refactoring the patches was
pretty stunning as well)

My two cents on some of the debates going on...

1. Row level security is a useful security feature (just as are
encryption, statistical fuzzing and others). Successful database
security will still require careful thought and design. This is
particularly true when users require "ad-hoc" query access with
commercial tools whose security implimentation may or may not be
adequate.
2. It is not reasonable to expect SE-PG to be the answer to every
security issue. There are almost always ways to extract SOME
information. The proposed features are just additional tools for
helping to enforce a chose security policy.Please note the "VPD"
feature of oracle returns security related errors in such a way that
it is sometimes possible to extract information from which actions
provoke security errors versus no error.
3. The integration with the OS and file system security is neat, but
for most databases, the users of the database do NOT have ANY access
to the machine the database resides on other than through the
database. But as I said it is neat and symmetrical in a way commercial
product rarely are. So seperating out row level security makes a lot
of sense. Good call.
4. There are significant benefits to integrating row level security as
a standard feature of PG that users can switch on/off as needed.
However, the frequent return to the notion of "claims" for SE-PG
concern me a little. One of the key provisions of open source software
is that it imust be essentially "use at your own risk". Designing to
minimize covert channels is worthwhile and useful. Any claim in this
regard is however quite foolish as it would requre extensive
validation and re-validation every time a patch was accepted. This
should be considered carefully.
5. The needs of various communities of users are distinctly different
with respect to security. For many prospective users of row level
security, it is mostly a checkbox as part of an overall effort to
comply with privacy laws or policies to limit liability. For some
users, it is a lot more than that and they expect that there will be
active and well educated attempts to violate security. For an even
smaller subset of users, lives may depend on the effective
compartmentalization of information. These are distinctly different
needs and their needs for features will overlap some but not
completely. The debate over returning a security message when rows are
not returned is a good example of this divergence. A company I did
some work for at one point decided to implement row level security for
their HR information. They were using a technology that returned
security messages, but did not think this was a problem until it was
pointed out that for the most common cases of abusing HR information
(co-workers) the co-worker generally already had access to at least
some of the information in question and returning a security message
allowed a process of elminitation to be used to extract a significant
amount of data. On the other hand, it was also sometimes necessary to
allow rows to participate in aggregations or there was little point in
having the data. To my knowledge there is no database product that
allows the implementation of a truly comprehensive and effective
security policy without careful design to take these issues into
account (which may in fact require the violation of other "design
rules" to implement).

So, all that said... It hink the answer is to move ahead with SE-PG
but to make it clear that in this first release, the behavior is
subject to change.

- Martin Rusoff

On Mon, Feb 16, 2009 at 12:37 PM, Jaime Casanova
<jcasanov(at)systemguards(dot)com(dot)ec> wrote:
> On Mon, Feb 16, 2009 at 12:18 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>
>> With reference to row-level security, most of the complaining about
>> this feature has been along the lines of "I don't like the idea that
>> rows get filtered from my result-set that I didn't ask to have
>> filtered".
>
> yeah! because was filtered by powers above yours... ;)
>
> i thing row level acl it's good feature for those that *really* need
> it, as every other solution this is not for everyone and could and
> will be misused sometimes... as far as the code maintain readibility
> and doesn't interfer in an instalation that doesn't include
> --enable-selinux i'm in favor of including it...
>
>
>> To me, the fact that you didn't have to ask seems like a
>> huge convenience, and I can't imagine why you'd want it otherwise.
>> Sure, the behavior needs to be documented, but that doesn't seem like
>> a big deal.
>>
>
> not only a convenience, it's a way to enforce policies that cannot be
> circumvented easily from programming (if you have very secret info
> that cost a lot, you can start being paranoic even of your own
> developing team ;)
>
>
> --
> Atentamente,
> Jaime Casanova
> Soporte y capacitación de PostgreSQL
> Asesoría y desarrollo de sistemas
> Guayaquil - Ecuador
> Cel. +59387171157
>
> --
> 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
>

--
Martin Rusoff
614-208-0381
742 Autumn Branch Rd.
Westerville OH 43081

Tom Lane wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
>> One thing I keep missing in this discussion: the term "row-level
>> security" in the above senstence in not the important part. Right now
>> you can revoke SELECT permission on a table with a foreign key and it
>> will still prevent UPDATEs and DELETEs of the primary key, allowing
>> users to infer the existance of an invisible FK.
>
>> This is the same "covert channel", so why is it a problem for
>> SE-Postgres and not for normal Postgres?
>
> The reason it's a problem for SE-Postgres is that the entire row-level
> security feature is advertised on the premise that it allows you to
> hide the existence of data; a claim not made by regular SQL. If the
> feature doesn't do what it's claimed to do then it's fair to ask why
> have it.

I've never said SE-PostgreSQL hide the existence of invisible tuples.
It just filtered out violated tuple from result set, but it is not
same as hiding the existence.
In other word, it does not care about covert channels as I repeated
many times.

The purpose of row-level access control is to provide users more
flexibility in their security configuration, not to eliminate
covert channels.

For example, if PostgreSQL has no SELinux support, we have to set up
multiple instance for each security domain to ensure data separation,
but it also gives us a restriction when we want to share a data from
multiple domains.
(Please note that it is talked from the viewpoint of SELinux.)
When it got SELinux support in table level granularity, we can integrate
them into a single instance, since SE-PostgreSQL ensure correct access
controls in this level, but it still disallow to store them into a single
table.
When it got row-level granularity, we can integrate them stored within
separated tables into a single one with correct access controls.

It is also a reason why I accepted to separate row-level feature in
the v8.4 development.

Again, it is not a target for SE-PostgreSQL to eliminate covert channels.
It is same as major commercial databases with row-level granularity.

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

>> It is incorrect.
>> It seems to me you confound a covert channel and granularity in access
>> controls. The purpose of row-level security is to provide users more
>> flexibility in access controls, not related to covert channels.
>
> No, I claim it's you that's confounding the data hiding scheme with
> row-level granular access controls.

Please note that SE-PostgreSQL does not make an assurance to hide
existence of invisible data. It ensures to prevent to read invisible
data via formal route, not a covert channel.

> If a user types "SELECT count(*) from table" they should either get
> the correct count of records from that table or get a permission
> failure.
>
> If they want to get the count of records for which they have read
> access they should have to write "SELECT count(*) from table where
> access_check(security_label, current_user())" or something like that.

SE-PostgreSQL considers "SELECT count(*) FROM table;" is an unconditional
select from a simple view with a condition.

However, there is no fundamental differences between filtering-out and
raising-error in the point where user cannot see invisible data via
formal route.
For example, a GUC variable to switch the behavior may be an option.
(It will be necessary to consider more for conclusion.)

But needless to say, my preference is filtering-out because it does
not require users to add additional conditions to avoid violated
tuples for each queries.

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

Robert Haas wrote:
> On Mon, Feb 16, 2009 at 10:11 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I haven't seen anyone present a shred of evidence that this would be
>>> the case in SE-PostgreSQL.
>> Sorry, but the burden of proof is in the other direction.
>>
>> In any case, this was already discussed in detail in previous threads.
>> It's possible that you could make the database adequately secure given
>> appropriate design rules, such as "only use synthetic keys as foreign
>> keys". (For instance consider Kevin's example of needing to hide the
>> case caption. If the caption had been used directly as PK then he'd
>> have a problem.) We have seen no evidence that anyone has a worked-out
>> set of design rules that make a SE-Postgres database secure against
>> these issues, so the whole thing is pie in the sky.
>
> I agree that it would be useful to have some documentation that
> outlines the new covert channels that this creates (by virtue of
> blocking the overt channels), approaches to addressing those that can
> be addressed, and warnings about those that can't. I think the only
> ones we've been able to come up with so far are:

I agree. It is important to show users its specification, limitation
and practical workaround explicitly.

However, I think it is not necessary to enumerate all the cases of
covert channels. It is even impossible to define clearly.
What we can do is to introduce a few representative scenarios and
workarounds, and put a notification to use your own risk.
If necessary, I can make a documentation patch?

This is an aside:
The administration guide of Oracle Label Security simply ignores
these discussion. I guess they understand how the matter is difficult.

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

Robert Haas wrote:
> I'm a little bothered by this issue with respect to INSERT, UPDATE,
> and DELETE, since it's possible that I have permission to see rows but
> not updated them, and it would be a little weird if select and update
> with equivalent where clauses operated on different sets of records
> (although that can happen anyway, because of BEFORE triggers, and it's
> pretty irritating). It's not clear that there's a clean solution
> here, but it's at least food for thought.

When user tries to INSERT, UPDATE or DELETE tuples without enough
privileges, the current row-level feature filters out violated tuples
from the affected set.
This behavior is same as when BEFORE-ROW trigger returns NULL.

If the given query requires both of SELECT and UPDATE permissions,
only tuples with both of permissions are affected, like:

UPDATE t SET a = 1, b = 'aaa' RETURNING *;

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

Robert Haas wrote:
> On Mon, Feb 16, 2009 at 11:43 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> I'm a little bothered by this issue with respect to INSERT, UPDATE,
>>> and DELETE, since it's possible that I have permission to see rows but
>>> not updated them, and it would be a little weird if select and update
>>> with equivalent where clauses operated on different sets of records
>>> (although that can happen anyway, because of BEFORE triggers, and it's
>>> pretty irritating). It's not clear that there's a clean solution
>>> here, but it's at least food for thought.
>> 80% of the problem here is exactly that the proposed solution doesn't
>> seem very semantically clean. And once we accept it we're going to be
>> stuck with it for a long time --- compare for instance the multiple
>> serious annoyances with RULEs, which we can't fix easily because of
>> backwards compatibility considerations.
>
> I've found rules in their current form to be nearly useless, except
> for views, which are wonderful. I do everything else with triggers.
>
> With reference to row-level security, most of the complaining about
> this feature has been along the lines of "I don't like the idea that
> rows get filtered from my result-set that I didn't ask to have
> filtered". To me, the fact that you didn't have to ask seems like a
> huge convenience, and I can't imagine why you'd want it otherwise.
> Sure, the behavior needs to be documented, but that doesn't seem like
> a big deal.

Yes, I can provide documentations to introduce behaviors by the new
features. Any comments to point out unclear things will be helpfull
to improve them.

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

Jaime Casanova wrote:
> On Mon, Feb 16, 2009 at 12:18 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> With reference to row-level security, most of the complaining about
>> this feature has been along the lines of "I don't like the idea that
>> rows get filtered from my result-set that I didn't ask to have
>> filtered".
>
> yeah! because was filtered by powers above yours... ;)
>
> i thing row level acl it's good feature for those that *really* need
> it, as every other solution this is not for everyone and could and
> will be misused sometimes... as far as the code maintain readibility
> and doesn't interfer in an instalation that doesn't include
> --enable-selinux i'm in favor of including it...

If patched PostgreSQL binary, you can turn on row level acls with
a table option: row_level_acl=[on|off], independent from a compile
time option: --enable-selinux.

It was suggested on earlier phase that compile time option should
be used to avoid build dependency issues, so I removed the compile
option to turn on/off row level acl.

>> To me, the fact that you didn't have to ask seems like a
>> huge convenience, and I can't imagine why you'd want it otherwise.
>> Sure, the behavior needs to be documented, but that doesn't seem like
>> a big deal.
>>
>
> not only a convenience, it's a way to enforce policies that cannot be
> circumvented easily from programming (if you have very secret info
> that cost a lot, you can start being paranoic even of your own
> developing team ;)

In generally, from the viewpoint of security, it is not a good design
to check permissions in many places, and depending on all them are
implemented correctly.
We should not assume users/applications always gives correct queries.

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

All,

I thought we'd agreed to compromise on having SE without row-level in
8.4, and working on SE with row-level in 8.5. Why are we revisiting
this argument? 8.4 is *already* late; arguing further about the terms
of SE simply risk us being forced to reject it entirely.

--Josh

2009/2/17 Josh Berkus <josh(at)agliodbs(dot)com>:
> All,
>
> I thought we'd agreed to compromise on having SE without row-level in 8.4,
> and working on SE with row-level in 8.5. Why are we revisiting this
> argument? 8.4 is *already* late; arguing further about the terms of SE
> simply risk us being forced to reject it entirely.
>

I absolutely agree. It nonsense open again and again closed question.

regards
Pavel

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

Pavel Stehule wrote:
> 2009/2/17 Josh Berkus <josh(at)agliodbs(dot)com>:
>> All,
>>
>> I thought we'd agreed to compromise on having SE without row-level in 8.4,
>> and working on SE with row-level in 8.5. Why are we revisiting this
>> argument? 8.4 is *already* late; arguing further about the terms of SE
>> simply risk us being forced to reject it entirely.
>>
>
> I absolutely agree. It nonsense open again and again closed question.

I also agree. What we should do now is to make progress the proposed
feature for v8.4, not a upcoming feature.

BogDan,
As I noted before, I can understand your requirement, but we already
decided to postpone a part of features within originally proposed,
because we're paying effort to develop v8.4 within reasonable schedule.
I'd like you to guess who wanted the row-level stuff to be merged most.

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

> Pavel Stehule wrote:
>> 2009/2/17 Josh Berkus <josh(at)agliodbs(dot)com>:
>>> All,
>>>
>>> I thought we'd agreed to compromise on having SE without row-level in
>>> 8.4,
>>> and working on SE with row-level in 8.5. Why are we revisiting this
>>> argument? 8.4 is *already* late; arguing further about the terms of SE
>>> simply risk us being forced to reject it entirely.
>>>
>>
>> I absolutely agree. It nonsense open again and again closed question.
>
> I also agree. What we should do now is to make progress the proposed
> feature for v8.4, not a upcoming feature.
>
> BogDan,
> As I noted before, I can understand your requirement, but we already
> decided to postpone a part of features within originally proposed,
> because we're paying effort to develop v8.4 within reasonable schedule.
> I'd like you to guess who wanted the row-level stuff to be merged most.
>

I understand postgresql 8.4 should be shipped within reasonable schedule,
but you see, row-level security will help more people then integration
with se-linux. Please don't understand me wrong, I appreciate what you do,
but for me se-linux is a little "science fiction" (and I ensure you, they
are many like me).
I see a lot of new useful features on postgresql 8.4 and row-level should
be the cherry on the cake.

I hope row level security will be accepted soon on 8.5 and you'll continue
to provide a patch for 8.4.x too. A patch against 8.4 will help many of
us to start working with this feature on a stable server until 8.5 will be
out.

Thanks for your hard work,

BogDan,

Greg Stark wrote:
> On Mon, Feb 16, 2009 at 4:14 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> I'm not sure I understand what you mean by that. I expect that if I
>> deny a particular user access to SELECT from a particular table the
>> system will throw a permissions error if that user later enters
>> "SELECT * FROM <table-name>". I don't expect that the system will
>> foresee every possible alternative way that a user might able to infer
>> something about the contents of that table and block it. I similarly
>> expect that if I install SE-PostgreSQL and configure it to filter out
>> certain rows from accesses to certain tables, those rows will in fact
>> be filtered. I still don't expect it to foresee every possible
>> alternative way that a user might be able to infer something about the
>> contents of the data to which the user does not have direct access.
>>
>> Is this fundamentally a semantic issue? If there's an asymmetry here
>> in what is being claimed, I'm not seeing it.
>
>
> Well the asymmetry is that in the former case the verb is "deny" and
> the latter it's "filter"...

I had talked to some knowledgeable people involved in designing
operating system security systems about this. A major design principle
for adding "advanced" security features was to minimize the impact on
existing interfaces.

This makes a lot of sense here. The problem is that in SQL you can
uniquely address columns, but not rows. So to avoid getting permission
denied errors in the face of column-level privileges, you simply omit
the off-limits columns from the select list and restriction clauses.
You mostly do this anyway, selecting only the columns that you are
legitimately interested in. So the interface impact is low. With
row-level privileges, you can't do that. Using the "deny" approach,
you'd immediately get permission denied errors for almost all queries in
your application as soon as you start setting row-level restrictions.
The fix would be to change all queries to add a restriction clause to
get only the rows you have access to. So the interface impact would be
pretty high. With a "filter" approach, the impact is much lower.

As you try to SELinux or SEWhatever-equip a complete operating system,
including kernel, file system, networking, X windows, etc., you have to
apply this deny vs filter tradeoff over and over again, to avoid
upsetting existing interfaces.

So, according to my state of knowledge, the purpose of row-level
security is actually *not* primarily to hide the existence of
information, but merely to hide the information itself under a standard
access-control mechanism while minimizing the impact on existing interfaces.