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
