SE-PostgreSQL and row level security

Started by BogDan Vatraabout 17 years ago48 messageshackers

taipan@omnidatagrup.ro

about 17 years ago

Hi,

I need SE-PostgreSQL *ONLY* for row level security, but AFAIK SE-PostgreSQL
works only on SELinux. This, for me, is unacceptable, because I want to use
row level security on windows too. I don't need all that fancy security
stuffs.

I want to share with you my "security" experience, my English is not so good
so, to be more clear, I give you an example and show you what I do instead
row
level security.
In this example I need row level security because I want an user who is
logged
in to see only a portion of a table (some filtered rows).

Let's say we have 2 tables:
1. customers
2. customers_products
and I want to let the user to select,insert, update or delete only what they
operate.

[SQL]

CREATE TABLE customers -- this is my "customers" table
(
id serial,
curstomer_name text,
login_user name DEFAULT session_user, -- the user who have the permission
to see this row
PRIMARY KEY (id)
) ;
ALTER TABLE customers OWNER TO postgres; -- the table is "invisible" to
normal
users
GRANT UPDATE, INSERT, DELETE, TRIGGER ON TABLE customers TO public; -- but
they can UPDATE, INSERT, DELETE and TRIGGER the table.

GRANT USAGE ON TABLE customers_id_seq TO public;

-- this is my solution to "row level security", user can query this view
only,
the table is "invisible" to them.
CREATE OR REPLACE VIEW view_customers AS
select * from customers where login_user=session_user;

CREATE TABLE customers_products
(
id serial,
id_customer integer NOT NULL, -- the customer id
product_name text NOT NULL,
login_user name DEFAULT session_user, -- the user who have the permission
to see this row
PRIMARY KEY (id),
FOREIGN KEY (id_customer) REFERENCES customers (id) ON UPDATE CASCADE
ON DELETE CASCADE --here it will be more useful if I can REFERENCE the view.
);
ALTER TABLE customers_products OWNER TO postgres; -- the table is "invisible"
to normal users
GRANT UPDATE, INSERT, DELETE, TRIGGER ON TABLE customers_products TO public;
-- but they can only UPDATE, INSERT, DELETE and TRIGGER the table.
GRANT USAGE ON TABLE customers_products_id_seq TO public;

-- this is my solution to "row level security", user can query this view
only,
the table is "invisible" to them.
CREATE OR REPLACE VIEW view_customers_products AS
select * from customers_products where login_user=session_user;

-- This trigger is executed every time you insert,update or delete from
table.

CREATE OR REPLACE FUNCTION customers_products_row_security()
RETURNS trigger AS
$BODY$
BEGIN
IF (TG_OP = 'DELETE') THEN
if OLD.id_customer NOT IN (SELECT id from view_customers)THEN
RETURN NULL;
END IF;
RETURN OLD;
END IF;
IF NEW.id_customer NOT IN (SELECT id from view_customers) THEN
RETURN NULL;
END IF;
NEW.login_user:=session_user;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
ALTER FUNCTION customers_products_row_security() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION customers_products_row_security() TO public;

CREATE TRIGGER customers_products_row_security_trigger BEFORE INSERT OR
UPDATE
OR DELETE
ON customers_products FOR EACH ROW
EXECUTE PROCEDURE public.customers_products_row_security();

[/SQL]

Another trigger should be created on customers table but you've got the
point.
As you can see there is a lot of code and possibility to make many mistakes.
What I my humble wish?
I wish I can make this more simple and elegant.
Here I see 2 solutions.
-real cross platform row level security, this seems to be very hard to do.

- the possibility to create "FOREIGN KEY"s who reference views or the
possibility to "CHECK" a cell of a row with a subquery in our example
something like this:"CHECK (id_customer IN (select id from view_customers))".
If I'll have this feature I don't have to create that triggers anymore. I
hope this is more simple for you to create.

Yours,
BogDan Vatra,

KaiGai Kohei

kaigai@ak.jp.nec.com

about 17 years ago

In reply to: BogDan Vatra (#1)

Re: SE-PostgreSQL and row level security

BogDan, Thanks for your interesting.

At first, I would like to confirm whether you know the row-level security
feature is postponed to v8.5, or not. Thus, the latest patch set (toward
v8.4 development cycle) does not contain the row-level one.
Please note that the following my comments assume the separated feature.

BogDan Vatra wrote:

Hi,

I need SE-PostgreSQL *ONLY* for row level security, but AFAIK SE-PostgreSQL
works only on SELinux. This, for me, is unacceptable, because I want to use
row level security on windows too. I don't need all that fancy security
stuffs.

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

-real cross platform row level security, this seems to be very hard to do.

I want to share with you my "security" experience, my English is not so good
so, to be more clear, I give you an example and show you what I do instead
row
level security.
In this example I need row level security because I want an user who is
logged
in to see only a portion of a table (some filtered rows).

Let's say we have 2 tables:
1. customers
2. customers_products
and I want to let the user to select,insert, update or delete only what they
operate.

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?

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?

Thanks,

[SQL]

CREATE TABLE customers -- this is my "customers" table
(
id serial,
curstomer_name text,
login_user name DEFAULT session_user, -- the user who have the permission
to see this row
PRIMARY KEY (id)
) ;
ALTER TABLE customers OWNER TO postgres; -- the table is "invisible" to
normal
users
GRANT UPDATE, INSERT, DELETE, TRIGGER ON TABLE customers TO public; -- but
they can UPDATE, INSERT, DELETE and TRIGGER the table.

GRANT USAGE ON TABLE customers_id_seq TO public;

-- this is my solution to "row level security", user can query this view
only,
the table is "invisible" to them.
CREATE OR REPLACE VIEW view_customers AS
select * from customers where login_user=session_user;

CREATE TABLE customers_products
(
id serial,
id_customer integer NOT NULL, -- the customer id
product_name text NOT NULL,
login_user name DEFAULT session_user, -- the user who have the permission
to see this row
PRIMARY KEY (id),
FOREIGN KEY (id_customer) REFERENCES customers (id) ON UPDATE CASCADE
ON DELETE CASCADE --here it will be more useful if I can REFERENCE the view.
);
ALTER TABLE customers_products OWNER TO postgres; -- the table is "invisible"
to normal users
GRANT UPDATE, INSERT, DELETE, TRIGGER ON TABLE customers_products TO public;
-- but they can only UPDATE, INSERT, DELETE and TRIGGER the table.
GRANT USAGE ON TABLE customers_products_id_seq TO public;

-- this is my solution to "row level security", user can query this view
only,
the table is "invisible" to them.
CREATE OR REPLACE VIEW view_customers_products AS
select * from customers_products where login_user=session_user;

-- This trigger is executed every time you insert,update or delete from
table.

CREATE OR REPLACE FUNCTION customers_products_row_security()
RETURNS trigger AS
$BODY$
BEGIN
IF (TG_OP = 'DELETE') THEN
if OLD.id_customer NOT IN (SELECT id from view_customers)THEN
RETURN NULL;
END IF;
RETURN OLD;
END IF;
IF NEW.id_customer NOT IN (SELECT id from view_customers) THEN
RETURN NULL;
END IF;
NEW.login_user:=session_user;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
ALTER FUNCTION customers_products_row_security() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION customers_products_row_security() TO public;

CREATE TRIGGER customers_products_row_security_trigger BEFORE INSERT OR
UPDATE
OR DELETE
ON customers_products FOR EACH ROW
EXECUTE PROCEDURE public.customers_products_row_security();

[/SQL]

Another trigger should be created on customers table but you've got the
point.
As you can see there is a lot of code and possibility to make many mistakes.
What I my humble wish?
I wish I can make this more simple and elegant.
Here I see 2 solutions.
-real cross platform row level security, this seems to be very hard to do.

- the possibility to create "FOREIGN KEY"s who reference views or the
possibility to "CHECK" a cell of a row with a subquery in our example
something like this:"CHECK (id_customer IN (select id from view_customers))".
If I'll have this feature I don't have to create that triggers anymore. I
hope this is more simple for you to create.

Yours,
BogDan Vatra,

--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>

BogDan Vatra

taipan@omnidatagrup.ro

about 17 years ago

In reply to: KaiGai Kohei (#2)

Re: SE-PostgreSQL and row level security

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,

Import Notes

Resolved by subject fallback

KaiGai Kohei

kaigai@ak.jp.nec.com

about 17 years ago

In reply to: BogDan Vatra (#3)

Re: SE-PostgreSQL and row level security

BogDan Vatra wrote:

Hi,
[...]

In my understanding, the row-level ACLs feature (plus a bit enhancement)

can

help your requirements. I developed it with SE-PostgreSQL in parallel,

but also postponed to v8.5 series.

It enables to assign database ACLs on individual tuples, and filter out

violated tupled from the result set of SELECT, UPDATE and DELETE.

So, it is not very hard. At least, we already have an implementation. :)

Where is it ? I like to try it?

The latest full-functional revision (r1467) is here:
http://code.google.com/p/sepgsql/downloads/list

However, it has a few confliction to the latest CVS HEAD, so I modified
the patch a bit and added a feature to support the replacement rule in
default acl configuration. It is the attached one (r1537).

If is working why is not included in 8.4?
IMHO this is a killer feature. I like to try this, and if you want I like
to give you more feedbacks.

We are standing on open source project, so it is impossible to do anything
in my own way.

However, I guess it will match with what you want to do.

---- Example: drink table is shared by several normal users

postgres=# CREATE TABLE drink (
postgres(# id serial primary key,
postgres(# name text,
postgres(# price int
postgres(# ) with (row_level_acl=on, default_row_acl='{%u=rwx/kaigai}');
NOTICE: CREATE TABLE will create implicit sequence "drink_id_seq" for serial column "drink.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "drink_pkey" for table "drink"
CREATE TABLE
postgres=# GRANT SELECT, UPDATE, INSERT, DELETE ON drink TO public;
GRANT
postgres=# GRANT USAGE ON drink_id_seq TO public;
GRANT
postgres=# INSERT INTO drink (name, price) VALUES ('coke', 120);
INSERT 0 1
postgres=# SELECT security_acl, * FROM drink;
security_acl | id | name | price
---------------------+----+------+-------
{kaigai=rwx/kaigai} | 1 | coke | 120
(1 row)

-- NOTE: "%u" in the default_row_acl is replaced by 'kaigai'

postgres=# \q
[kaigai@saba ~]$ psql postgres -Uymj
psql (8.4devel)
Type "help" for help.

postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
--------------+----+------+-------
(0 rows)

-- NOTE: violated tuples are filtered out from the viewpoint of 'ymj'.

postgres=> INSERT INTO drink (name, price) VALUES ('juice', 140), ('beer', 240);
INSERT 0 2
postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
------------------+----+-------+-------
{ymj=rwx/kaigai} | 2 | juice | 140
{ymj=rwx/kaigai} | 3 | beer | 240
(2 rows)

postgres=> \q
[kaigai@saba ~]$ psql postgres -Utak
psql (8.4devel)
Type "help" for help.

postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
--------------+----+------+-------
(0 rows)

postgres=> INSERT INTO drink (name, price) VALUES ('tea', 120), ('water', 100);
INSERT 0 2
postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
------------------+----+-------+-------
{tak=rwx/kaigai} | 4 | tea | 120
{tak=rwx/kaigai} | 5 | water | 100
(2 rows)

-- NOTE: A normal user 'tak' cannot see tuples by others.

postgres=> UPDATE drink SET price = price * 1.2;
UPDATE 2
postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
------------------+----+-------+-------
{tak=rwx/kaigai} | 4 | tea | 144
{tak=rwx/kaigai} | 5 | water | 120
(2 rows)

-- NOTE: Only his tuples are affected.

postgres=> UPDATE drink SET security_acl = '{=rwx/tak}';
ERROR: Only owner or superuser can set ACL

-- NOTE: He is not allowed to update ACL

postgres=> \q
[kaigai@saba ~]$ psql postgres
psql (8.4devel)
Type "help" for help.

postgres=# SELECT security_acl, * FROM drink;
security_acl | id | name | price
---------------------+----+-------+-------
{kaigai=rwx/kaigai} | 1 | coke | 120
{ymj=rwx/kaigai} | 2 | juice | 140
{ymj=rwx/kaigai} | 3 | beer | 240
{tak=rwx/kaigai} | 4 | tea | 144
{tak=rwx/kaigai} | 5 | water | 120
(5 rows)

-- NOTE: From the viewpoint of superuser again.

Thanks for your interesting.
--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>

BogDan Vatra

taipan@omnidatagrup.ro

about 17 years ago

In reply to: KaiGai Kohei (#4)

Re: SE-PostgreSQL and row level security

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

A message for postgresql decision board:

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

BogDan,

Show quoted text

BogDan Vatra wrote:

Hi,
[...]

In my understanding, the row-level ACLs feature (plus a bit
enhancement)

can

help your requirements. I developed it with SE-PostgreSQL in parallel,

but also postponed to v8.5 series.

It enables to assign database ACLs on individual tuples, and filter out

violated tupled from the result set of SELECT, UPDATE and DELETE.

So, it is not very hard. At least, we already have an implementation.
:)

Where is it ? I like to try it?

The latest full-functional revision (r1467) is here:
http://code.google.com/p/sepgsql/downloads/list

However, it has a few confliction to the latest CVS HEAD, so I modified
the patch a bit and added a feature to support the replacement rule in
default acl configuration. It is the attached one (r1537).

If is working why is not included in 8.4?
IMHO this is a killer feature. I like to try this, and if you want I

like

to give you more feedbacks.

We are standing on open source project, so it is impossible to do anything
in my own way.

However, I guess it will match with what you want to do.

---- Example: drink table is shared by several normal users

postgres=# CREATE TABLE drink (
postgres(# id serial primary key,
postgres(# name text,
postgres(# price int
postgres(# ) with (row_level_acl=on, default_row_acl='{%u=rwx/kaigai}');
NOTICE: CREATE TABLE will create implicit sequence "drink_id_seq" for
serial column "drink.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"drink_pkey" for table "drink"
CREATE TABLE
postgres=# GRANT SELECT, UPDATE, INSERT, DELETE ON drink TO public;
GRANT
postgres=# GRANT USAGE ON drink_id_seq TO public;
GRANT
postgres=# INSERT INTO drink (name, price) VALUES ('coke', 120);
INSERT 0 1
postgres=# SELECT security_acl, * FROM drink;
security_acl | id | name | price
---------------------+----+------+-------
{kaigai=rwx/kaigai} | 1 | coke | 120
(1 row)

-- NOTE: "%u" in the default_row_acl is replaced by 'kaigai'

postgres=# \q
[kaigai@saba ~]$ psql postgres -Uymj
psql (8.4devel)
Type "help" for help.

postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
--------------+----+------+-------
(0 rows)

-- NOTE: violated tuples are filtered out from the viewpoint of 'ymj'.

postgres=> INSERT INTO drink (name, price) VALUES ('juice', 140), ('beer',
240);
INSERT 0 2
postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
------------------+----+-------+-------
{ymj=rwx/kaigai} | 2 | juice | 140
{ymj=rwx/kaigai} | 3 | beer | 240
(2 rows)

postgres=> \q
[kaigai@saba ~]$ psql postgres -Utak
psql (8.4devel)
Type "help" for help.

postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
--------------+----+------+-------
(0 rows)

postgres=> INSERT INTO drink (name, price) VALUES ('tea', 120), ('water',
100);
INSERT 0 2
postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
------------------+----+-------+-------
{tak=rwx/kaigai} | 4 | tea | 120
{tak=rwx/kaigai} | 5 | water | 100
(2 rows)

-- NOTE: A normal user 'tak' cannot see tuples by others.

postgres=> UPDATE drink SET price = price * 1.2;
UPDATE 2
postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
------------------+----+-------+-------
{tak=rwx/kaigai} | 4 | tea | 144
{tak=rwx/kaigai} | 5 | water | 120
(2 rows)

-- NOTE: Only his tuples are affected.

postgres=> UPDATE drink SET security_acl = '{=rwx/tak}';
ERROR: Only owner or superuser can set ACL

-- NOTE: He is not allowed to update ACL

postgres=> \q
[kaigai@saba ~]$ psql postgres
psql (8.4devel)
Type "help" for help.

postgres=# SELECT security_acl, * FROM drink;
security_acl | id | name | price
---------------------+----+-------+-------
{kaigai=rwx/kaigai} | 1 | coke | 120
{ymj=rwx/kaigai} | 2 | juice | 140
{ymj=rwx/kaigai} | 3 | beer | 240
{tak=rwx/kaigai} | 4 | tea | 144
{tak=rwx/kaigai} | 5 | water | 120
(5 rows)

-- NOTE: From the viewpoint of superuser again.

Thanks for your interesting.
--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

KaiGai Kohei

kaigai@ak.jp.nec.com

about 17 years ago

In reply to: BogDan Vatra (#5)

Re: SE-PostgreSQL and row level security

BogDan Vatra wrote:

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

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

A message for postgresql decision board:

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

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

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

Thanks,

BogDan,

BogDan Vatra wrote:

Hi,
[...]

In my understanding, the row-level ACLs feature (plus a bit
enhancement)

can

help your requirements. I developed it with SE-PostgreSQL in parallel,

but also postponed to v8.5 series.

It enables to assign database ACLs on individual tuples, and filter out

violated tupled from the result set of SELECT, UPDATE and DELETE.

So, it is not very hard. At least, we already have an implementation.
:)

Where is it ? I like to try it?

The latest full-functional revision (r1467) is here:
http://code.google.com/p/sepgsql/downloads/list

However, it has a few confliction to the latest CVS HEAD, so I modified
the patch a bit and added a feature to support the replacement rule in
default acl configuration. It is the attached one (r1537).

If is working why is not included in 8.4?
IMHO this is a killer feature. I like to try this, and if you want I

like

to give you more feedbacks.

We are standing on open source project, so it is impossible to do anything
in my own way.

However, I guess it will match with what you want to do.

---- Example: drink table is shared by several normal users

postgres=# CREATE TABLE drink (
postgres(# id serial primary key,
postgres(# name text,
postgres(# price int
postgres(# ) with (row_level_acl=on, default_row_acl='{%u=rwx/kaigai}');
NOTICE: CREATE TABLE will create implicit sequence "drink_id_seq" for
serial column "drink.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"drink_pkey" for table "drink"
CREATE TABLE
postgres=# GRANT SELECT, UPDATE, INSERT, DELETE ON drink TO public;
GRANT
postgres=# GRANT USAGE ON drink_id_seq TO public;
GRANT
postgres=# INSERT INTO drink (name, price) VALUES ('coke', 120);
INSERT 0 1
postgres=# SELECT security_acl, * FROM drink;
security_acl | id | name | price
---------------------+----+------+-------
{kaigai=rwx/kaigai} | 1 | coke | 120
(1 row)

-- NOTE: "%u" in the default_row_acl is replaced by 'kaigai'

postgres=# \q
[kaigai@saba ~]$ psql postgres -Uymj
psql (8.4devel)
Type "help" for help.

postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
--------------+----+------+-------
(0 rows)

-- NOTE: violated tuples are filtered out from the viewpoint of 'ymj'.

postgres=> INSERT INTO drink (name, price) VALUES ('juice', 140), ('beer',
240);
INSERT 0 2
postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
------------------+----+-------+-------
{ymj=rwx/kaigai} | 2 | juice | 140
{ymj=rwx/kaigai} | 3 | beer | 240
(2 rows)

postgres=> \q
[kaigai@saba ~]$ psql postgres -Utak
psql (8.4devel)
Type "help" for help.

postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
--------------+----+------+-------
(0 rows)

postgres=> INSERT INTO drink (name, price) VALUES ('tea', 120), ('water',
100);
INSERT 0 2
postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
------------------+----+-------+-------
{tak=rwx/kaigai} | 4 | tea | 120
{tak=rwx/kaigai} | 5 | water | 100
(2 rows)

-- NOTE: A normal user 'tak' cannot see tuples by others.

postgres=> UPDATE drink SET price = price * 1.2;
UPDATE 2
postgres=> SELECT security_acl, * FROM drink;
security_acl | id | name | price
------------------+----+-------+-------
{tak=rwx/kaigai} | 4 | tea | 144
{tak=rwx/kaigai} | 5 | water | 120
(2 rows)

-- NOTE: Only his tuples are affected.

postgres=> UPDATE drink SET security_acl = '{=rwx/tak}';
ERROR: Only owner or superuser can set ACL

-- NOTE: He is not allowed to update ACL

postgres=> \q
[kaigai@saba ~]$ psql postgres
psql (8.4devel)
Type "help" for help.

postgres=# SELECT security_acl, * FROM drink;
security_acl | id | name | price
---------------------+----+-------+-------
{kaigai=rwx/kaigai} | 1 | coke | 120
{ymj=rwx/kaigai} | 2 | juice | 140
{ymj=rwx/kaigai} | 3 | beer | 240
{tak=rwx/kaigai} | 4 | tea | 144
{tak=rwx/kaigai} | 5 | water | 120
(5 rows)

-- NOTE: From the viewpoint of superuser again.

Thanks for your interesting.
--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

------------------------------------------------------------------------

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

BogDan Vatra

taipan@omnidatagrup.ro

about 17 years ago

In reply to: KaiGai Kohei (#6)

Re: SE-PostgreSQL and row level security

[..]

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,

[..]

David Fetter

david@fetter.org

about 17 years ago

In reply to: BogDan Vatra (#7)

Re: SE-PostgreSQL and row level security

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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

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

BogDan Vatra

bogdan@omnidatagrup.ro

about 17 years ago

In reply to: David Fetter (#8)

Re: SE-PostgreSQL and row level security

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,

[..]

#10

KaiGai Kohei

kaigai@ak.jp.nec.com

about 17 years ago

In reply to: BogDan Vatra (#9)

Re: SE-PostgreSQL and row level security

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

#11

Martijn van Oosterhout

kleptog@svana.org

about 17 years ago

In reply to: KaiGai Kohei (#10)

Re: SE-PostgreSQL and row level security

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@svana.org> http://svana.org/kleptog/

Show quoted text

Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.

#12

KaiGai Kohei

kaigai@ak.jp.nec.com

about 17 years ago

In reply to: Martijn van Oosterhout (#11)

Re: SE-PostgreSQL and row level security

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

#13

Bruce Momjian

bruce@momjian.us

about 17 years ago

In reply to: KaiGai Kohei (#12)

Re: SE-PostgreSQL and row level security

KaiGai Kohei <kaigai@ak.jp.nec.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!

#14

KaiGai Kohei

kaigai@ak.jp.nec.com

about 17 years ago

In reply to: Bruce Momjian (#13)

Re: SE-PostgreSQL and row level security

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@kaigai.gr.jp>

#15

Kevin Grittner

Kevin.Grittner@wicourts.gov

about 17 years ago

In reply to: Bruce Momjian (#13)

Re: SE-PostgreSQL and row level security

Gregory Stark <stark@enterprisedb.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

#16

Martijn van Oosterhout

kleptog@svana.org

about 17 years ago

In reply to: Bruce Momjian (#13)

Re: SE-PostgreSQL and row level security

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@svana.org> http://svana.org/kleptog/

Show quoted text

Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.

#17

Bruce Momjian

bruce@momjian.us

about 17 years ago

In reply to: KaiGai Kohei (#14)

Re: SE-PostgreSQL and row level security

On Mon, Feb 16, 2009 at 12:08 PM, KaiGai Kohei <kaigai@kaigai.gr.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

#18

Tom Lane

tgl@sss.pgh.pa.us

about 17 years ago

In reply to: Kevin Grittner (#15)

Re: SE-PostgreSQL and row level security

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

Gregory Stark <stark@enterprisedb.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

#19

Tom Lane

tgl@sss.pgh.pa.us

about 17 years ago

In reply to: Martijn van Oosterhout (#11)

Re: SE-PostgreSQL and row level security

Martijn van Oosterhout <kleptog@svana.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

#20

Robert Haas

robertmhaas@gmail.com

about 17 years ago

In reply to: Bruce Momjian (#13)

Re: SE-PostgreSQL and row level security

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

#21