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 <kai...@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
