Thread: SE-PostgreSQL and row level security
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') THENif 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) THENRETURN 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,
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
seemsto 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
anythingfor the current user, but nothing for others. - So, when user tries to select, update and delete this table,
tupleswhich inserted by others to be filtered out from the result set or affected rows. - Normal users are disallowed
tochange 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
assignedto 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>
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@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>
Attachment
I've tested you patch in windows and in linux and it just work, it's a killer feature. I have to tank you and all who worked on this. On windows I have one little problem, mingw does not have "strtok_r" function and I have to add it myself (see attached file). A message for postgresql decision board: Dear postgresql hackers, if I can do something to push row level acl for 8.4 please tell me, I do anything to have this feature, it will help me, and I hope many others, this feature will help to develop client to postgres applications without a server application or tones of triggers and viewers. BogDan, > BogDan Vatra wrote: >> Hi, >> [...] >>> In my understanding, the row-level ACLs feature (plus a bit >>> enhancement) >> can >>> help your requirements. I developed it with SE-PostgreSQL in parallel, >> but also postponed to v8.5 series. >>> It enables to assign database ACLs on individual tuples, and filter out >> violated tupled from the result set of SELECT, UPDATE and DELETE. >>> So, it is not very hard. At least, we already have an implementation. >>> :) >> >> Where is it ? I like to try it? > > The latest full-functional revision (r1467) is here: > http://code.google.com/p/sepgsql/downloads/list > > However, it has a few confliction to the latest CVS HEAD, so I modified > the patch a bit and added a feature to support the replacement rule in > default acl configuration. It is the attached one (r1537). > > > If is working why is not included in 8.4? > > IMHO this is a killer feature. I like to try this, and if you want I > like > > to give you more feedbacks. > > We are standing on open source project, so it is impossible to do anything > in my own way. > > However, I guess it will match with what you want to do. > > ---- Example: drink table is shared by several normal users > > postgres=# CREATE TABLE drink ( > postgres(# id serial primary key, > postgres(# name text, > postgres(# price int > postgres(# ) with (row_level_acl=on, default_row_acl='{%u=rwx/kaigai}'); > NOTICE: CREATE TABLE will create implicit sequence "drink_id_seq" for > serial column "drink.id" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "drink_pkey" for table "drink" > CREATE TABLE > postgres=# GRANT SELECT, UPDATE, INSERT, DELETE ON drink TO public; > GRANT > postgres=# GRANT USAGE ON drink_id_seq TO public; > GRANT > postgres=# INSERT INTO drink (name, price) VALUES ('coke', 120); > INSERT 0 1 > postgres=# SELECT security_acl, * FROM drink; > security_acl | id | name | price > ---------------------+----+------+------- > {kaigai=rwx/kaigai} | 1 | coke | 120 > (1 row) > > -- NOTE: "%u" in the default_row_acl is replaced by 'kaigai' > > postgres=# \q > [kaigai@saba ~]$ psql postgres -Uymj > psql (8.4devel) > Type "help" for help. > > postgres=> SELECT security_acl, * FROM drink; > security_acl | id | name | price > --------------+----+------+------- > (0 rows) > > -- NOTE: violated tuples are filtered out from the viewpoint of 'ymj'. > > postgres=> INSERT INTO drink (name, price) VALUES ('juice', 140), ('beer', > 240); > INSERT 0 2 > postgres=> SELECT security_acl, * FROM drink; > security_acl | id | name | price > ------------------+----+-------+------- > {ymj=rwx/kaigai} | 2 | juice | 140 > {ymj=rwx/kaigai} | 3 | beer | 240 > (2 rows) > > postgres=> \q > [kaigai@saba ~]$ psql postgres -Utak > psql (8.4devel) > Type "help" for help. > > postgres=> SELECT security_acl, * FROM drink; > security_acl | id | name | price > --------------+----+------+------- > (0 rows) > > postgres=> INSERT INTO drink (name, price) VALUES ('tea', 120), ('water', > 100); > INSERT 0 2 > postgres=> SELECT security_acl, * FROM drink; > security_acl | id | name | price > ------------------+----+-------+------- > {tak=rwx/kaigai} | 4 | tea | 120 > {tak=rwx/kaigai} | 5 | water | 100 > (2 rows) > > -- NOTE: A normal user 'tak' cannot see tuples by others. > > postgres=> UPDATE drink SET price = price * 1.2; > UPDATE 2 > postgres=> SELECT security_acl, * FROM drink; > security_acl | id | name | price > ------------------+----+-------+------- > {tak=rwx/kaigai} | 4 | tea | 144 > {tak=rwx/kaigai} | 5 | water | 120 > (2 rows) > > -- NOTE: Only his tuples are affected. > > postgres=> UPDATE drink SET security_acl = '{=rwx/tak}'; > ERROR: Only owner or superuser can set ACL > > -- NOTE: He is not allowed to update ACL > > postgres=> \q > [kaigai@saba ~]$ psql postgres > psql (8.4devel) > Type "help" for help. > > postgres=# SELECT security_acl, * FROM drink; > security_acl | id | name | price > ---------------------+----+-------+------- > {kaigai=rwx/kaigai} | 1 | coke | 120 > {ymj=rwx/kaigai} | 2 | juice | 140 > {ymj=rwx/kaigai} | 3 | beer | 240 > {tak=rwx/kaigai} | 4 | tea | 144 > {tak=rwx/kaigai} | 5 | water | 120 > (5 rows) > > -- NOTE: From the viewpoint of superuser again. > > Thanks for your interesting. > -- > OSS Platform Development Division, NEC > KaiGai Kohei <kaigai@ak.jp.nec.com> > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
Attachment
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>
[..] >> 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@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
> 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@ak.jp.nec.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@svana.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@ak.jp.nec.com>
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 arein 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 themon demand. 3) The data hiding scheme -- which frankly I think is dead in the water. It amounts to a major change to the SQL semanticswhere 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@kaigai.gr.jp>
>>> 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
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/ > 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@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
"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
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
>> 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@sss.pgh.pa.us> wrote: > "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. 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@sss.pgh.pa.us> wrote: > "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. 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@anarazel.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@gmail.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@sss.pgh.pa.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.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.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@sss.pgh.pa.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@gmail.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@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.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@gmail.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@sss.pgh.pa.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 boolCOST 0.1VOLATILELANGUAGE plpgsqlAS $body$ BEGIN raisenotice '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@enterprisedb.com> wrote: > On Mon, Feb 16, 2009 at 4:14 PM, Robert Haas <robertmhaas@gmail.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@gmail.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@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.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@gmail.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@systemguards.com.ec> wrote: > On Mon, Feb 16, 2009 at 12:18 PM, Robert Haas <robertmhaas@gmail.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@postgresql.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@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. 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@ak.jp.nec.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@ak.jp.nec.com>
Robert Haas wrote: > On Mon, Feb 16, 2009 at 10:11 AM, Tom Lane <tgl@sss.pgh.pa.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@ak.jp.nec.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@ak.jp.nec.com>
Robert Haas wrote: > On Mon, Feb 16, 2009 at 11:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Robert Haas <robertmhaas@gmail.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@ak.jp.nec.com>
Jaime Casanova wrote: > On Mon, Feb 16, 2009 at 12:18 PM, Robert Haas <robertmhaas@gmail.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@ak.jp.nec.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@agliodbs.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
Pavel Stehule wrote: > 2009/2/17 Josh Berkus <josh@agliodbs.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@ak.jp.nec.com>
> Pavel Stehule wrote: >> 2009/2/17 Josh Berkus <josh@agliodbs.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@gmail.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.