row level security best practice

From: "Keresztury Balazs" <balazs(dot)keresztury(at)gmail(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: row level security best practice
Date: 2010-01-26 17:33:28
Message-ID: 4b5f276b.0338560a.0636.6a92@mx.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2010-01-26 19:33:12 Re: Postgres Host
Previous Message Moe 2010-01-26 17:25:50 Re: pg dump.. issue with when using crontab