Re: SE-PostgreSQL and row level security

BogDan, Thanks for your interesting.

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

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

In my understanding, the row-level ACLs feature (plus a bit enhancement) can
help your requirements. I developed it with SE-PostgreSQL in parallel, but
also postponed to v8.5 series.
It enables to assign database ACLs on individual tuples, and filter out
violated tupled from the result set of SELECT, UPDATE and DELETE.

So, it is not very hard. At least, we already have an implementation. :)> -real cross platform row level security, this
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>