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') 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,
BogDan, Thanks for your interesting.
At first, I would like to confirm whether you know the row-level security
feature is postponed to v8.5, or not. Thus, the latest patch set (toward
v8.4 development cycle) does not contain the row-level one.
Please note that the following my comments assume the separated feature.
BogDan Vatra wrote:
Hi,
I need SE-PostgreSQL *ONLY* for row level security, but AFAIK SE-PostgreSQL
works only on SELinux. This, for me, is unacceptable, because I want to use
row level security on windows too. I don't need all that fancy security
stuffs.
In my understanding, the row-level ACLs feature (plus a bit enhancement) can
help your requirements. I developed it with SE-PostgreSQL in parallel, but
also postponed to v8.5 series.
It enables to assign database ACLs on individual tuples, and filter out
violated tupled from the result set of SELECT, UPDATE and DELETE.
So, it is not very hard. At least, we already have an implementation. :)
-real cross platform row level security, this seems to be very hard to do.
I want to share with you my "security" experience, my English is not so good
so, to be more clear, I give you an example and show you what I do instead
row
level security.
In this example I need row level security because I want an user who is
logged
in to see only a portion of a table (some filtered rows).Let's say we have 2 tables:
1. customers
2. customers_products
and I want to let the user to select,insert, update or delete only what they
operate.
I guess you concerned about:
- It is necessary to set up many trigger functions for each tables, which
provide similar functionality.
- Users have to specify different names between reference and modification.
And, you want to make clear how the row-level access control resolves it.
Is it OK?
Your requirement is a simple separation between different users.
Thus, what we have to do is:
- When a tuple is inserted, the backend automatically assigns an ACL which
allows anything for the current user, but nothing for others.
- So, when user tries to select, update and delete this table, tuples which
inserted by others to be filtered out from the result set or affected rows.
- Normal users are disallowed to change automatically assigned ACLs.
(I don't think you want to restrict superuser's operations.)
The row-level ACLs have a functionality named as "default acl".
It enables table's owner to specify ACLs to be assigned to newly inserted
tuple, like:
CREATE TABLE customer_products (
id serial,
:
) WITH (default_row_acl='{rwd=kaigai}');
Currently, it does not allow replacement rules like "{rwd=%current_user}",
but it is not a hard enhancement. If such an ACL is assigned, the tuple
is not visible from other users without any triggers.
For example, please consider when a user "kaigai" insert a tuple into
"customer_products", the "{rwd=kaigai}" is assigned to the tuple, but
the "{rwd=bogdan}" is assigned when a user "bogdan" do same thing.
In this case, any users must not be an owner of the table, because owner
of the table is allowed to change the ACLs.
This is an aside. If you want different access controls, like read-only
for other's tuples but read-writable for own tuples, it will be possible
with different default acl configuration.
Does it help you to understand about the row-level security currently
we are in development?
Thanks,
[SQL]
CREATE TABLE customers -- this is my "customers" table
(
id serial,
curstomer_name text,
login_user name DEFAULT session_user, -- the user who have the permission
to see this row
PRIMARY KEY (id)
) ;
ALTER TABLE customers OWNER TO postgres; -- the table is "invisible" to
normal
users
GRANT UPDATE, INSERT, DELETE, TRIGGER ON TABLE customers TO public; -- but
they can UPDATE, INSERT, DELETE and TRIGGER the table.GRANT USAGE ON TABLE customers_id_seq TO public;
-- this is my solution to "row level security", user can query this view
only,
the table is "invisible" to them.
CREATE OR REPLACE VIEW view_customers AS
select * from customers where login_user=session_user;CREATE TABLE customers_products
(
id serial,
id_customer integer NOT NULL, -- the customer id
product_name text NOT NULL,
login_user name DEFAULT session_user, -- the user who have the permission
to see this row
PRIMARY KEY (id),
FOREIGN KEY (id_customer) REFERENCES customers (id) ON UPDATE CASCADE
ON DELETE CASCADE --here it will be more useful if I can REFERENCE the view.
);
ALTER TABLE customers_products OWNER TO postgres; -- the table is "invisible"
to normal users
GRANT UPDATE, INSERT, DELETE, TRIGGER ON TABLE customers_products TO public;
-- but they can only UPDATE, INSERT, DELETE and TRIGGER the table.
GRANT USAGE ON TABLE customers_products_id_seq TO public;-- this is my solution to "row level security", user can query this view
only,
the table is "invisible" to them.
CREATE OR REPLACE VIEW view_customers_products AS
select * from customers_products where login_user=session_user;-- This trigger is executed every time you insert,update or delete from
table.CREATE OR REPLACE FUNCTION customers_products_row_security()
RETURNS trigger AS
$BODY$
BEGIN
IF (TG_OP = 'DELETE') THEN
if OLD.id_customer NOT IN (SELECT id from view_customers)THEN
RETURN NULL;
END IF;
RETURN OLD;
END IF;
IF NEW.id_customer NOT IN (SELECT id from view_customers) THEN
RETURN NULL;
END IF;
NEW.login_user:=session_user;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
ALTER FUNCTION customers_products_row_security() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION customers_products_row_security() TO public;CREATE TRIGGER customers_products_row_security_trigger BEFORE INSERT OR
UPDATE
OR DELETE
ON customers_products FOR EACH ROW
EXECUTE PROCEDURE public.customers_products_row_security();[/SQL]
Another trigger should be created on customers table but you've got the
point.
As you can see there is a lot of code and possibility to make many mistakes.
What I my humble wish?
I wish I can make this more simple and elegant.
Here I see 2 solutions.
-real cross platform row level security, this seems to be very hard to do.- the possibility to create "FOREIGN KEY"s who reference views or the
possibility to "CHECK" a cell of a row with a subquery in our example
something like this:"CHECK (id_customer IN (select id from view_customers))".
If I'll have this feature I don't have to create that triggers anymore. I
hope this is more simple for you to create.Yours,
BogDan Vatra,
--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>
Hi,
[...]
In my understanding, the row-level ACLs feature (plus a bit enhancement)
can
help your requirements. I developed it with SE-PostgreSQL in parallel,
but also postponed to v8.5 series.
It enables to assign database ACLs on individual tuples, and filter out
violated tupled from the result set of SELECT, UPDATE and DELETE.
So, it is not very hard. At least, we already have an implementation. :)
Where is it ? I like to try it? If is working why is not included in 8.4?
IMHO this is a killer feature. I like to try this, and if you want I like
to give you more feedbacks.
[..]
I guess you concerned about:
- It is necessary to set up many trigger functions for each tables, which
provide similar functionality.
- Users have to specify different names between reference and
modification.And, you want to make clear how the row-level access control resolves
it. Is it OK?
Yes.
Your requirement is a simple separation between different users. Thus,
what we have to do is:
- When a tuple is inserted, the backend automatically assigns an ACL
which
allows anything for the current user, but nothing for others.
- So, when user tries to select, update and delete this table, tuples
which
inserted by others to be filtered out from the result set or affected
rows.
- Normal users are disallowed to change automatically assigned ACLs.
(I don't think you want to restrict superuser's operations.)The row-level ACLs have a functionality named as "default acl".
It enables table's owner to specify ACLs to be assigned to newly
inserted tuple, like:
CREATE TABLE customer_products (
id serial,
:
) WITH (default_row_acl='{rwd=kaigai}');Currently, it does not allow replacement rules like
"{rwd=%current_user}", but it is not a hard enhancement. If such an ACL
is assigned, the tuple is not visible from other users without any
triggers.
For example, please consider when a user "kaigai" insert a tuple into
"customer_products", the "{rwd=kaigai}" is assigned to the tuple, but
the "{rwd=bogdan}" is assigned when a user "bogdan" do same thing.
In this case, any users must not be an owner of the table, because owner
of the table is allowed to change the ACLs.
This is an aside. If you want different access controls, like read-only
for other's tuples but read-writable for own tuples, it will be possible
with different default acl configuration.
Does it help you to understand about the row-level security currently we
are in development?
Yes and I like to try it (with more complex situations).
I have C/C++ knowledge maybe I can help you with this.
BIG TANKS
BogDan,
Import Notes
Resolved by subject fallback
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>
Attachments:
sepostgresql-full-8.4devel-3-r1537.patch.gzapplication/gzip; name=sepostgresql-full-8.4devel-3-r1537.patch.gzDownload
� ���I �<�[�6�?��b��� 8!N��h�B`�ca��}��n�U%q��S��c����3�?d� �����pli4��F3#��5Ae�]�0`>�1]gd������ove4���������F�V�Tj�J�z�[kw��Z���Z�V��R�,�7�����z�����|+��[+��5������^�Zz�R�%� �+�@��A�������!XT� �������
���3������[��E��'F����.g��:��������V[��FB u���x#����a�g#�L�G�mq'