SE-PostgreSQL and row level security - Mailing list pgsql-hackers

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,