hi, I'd like to implement row level security in a PostgreSQL 8.4.1 database, but after several unsuccessful trial I got stuck a little bit. I have a fact table (project) with a unique id (lets call this project_id) which is going to be secured. There is another table (access) containing the access data in the following format: user_id, project_id. If a user is assigned to a project, a new record is entered in this table. With the concept above I can restrict the projects using a simple view: create view project_v as select * from project inner join access a using(project_id) where a.user_id = current_user::text; grant select on project_v to public; revoke select on project from public; Users won't be able to select any other rows, than theirs. But what if I want to let them update or delete from the original table? grant delete, update on project to public; CREATE TRIGGER "projekt_1_jogosultsag" BEFORE UPDATE OR DELETE ON "project" FOR EACH ROW EXECUTE PROCEDURE "public"."jogosultsag_trigger"(); CREATE OR REPLACE FUNCTION "public"."jogosultsag_trigger" ( ) RETURNS trigger AS $body$ BEGIN --nem saját projekt adatait nem lehet módosítani IF OLD.project_id NOT IN(SELECT project_id FROM project_v) THEN RAISE EXCEPTION 'You cannot modify this project! (%)', OLD.project_id; END IF; IF TG_OP='UPDATE' THEN RETURN NEW; ELSE RETURN OLD; END IF; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100; Using the grants above, users clearly can't update or delete from the original fact table, since WHERE condition won't work without SELECT privileges. I also considered using rules on the view, but if I understood well, it isn't possible to use a similar IF condition in the rule system. How could I solve this problem? Any help is appreciated! Balazs -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
