On May24, 2012, at 16:19 , Kohei KaiGai wrote: > So, the proposed interface might be revised as follows: > ALTER TABLE <tblname> ADD SECURITY POLICY > <func_name>(<args>, ...) [FOR SELECT | > INSERT | > [BEFORE|AFTER] UPDATE > | > DELETE]; > > In case of INSERT or AFTER UPDATE, I assume the check shall be applied > on the tail of before-row triggers.
I'd go with just "SELECT, UPDATE, DELETE", and leave the INSERT and BEFORE UPDATE case to regular triggers, for two reasons First, it's conceptually much simpler, since the policy always just adds an implicit WHERE clause, period. This of course assumes that DELETE and (BEFORE) UPDATE simply skips rows for which the policy function returns false, instead of reporting 'permission denied' or something. But that's the most reasonable behaviour anyway, I think, because otherwise you'd make batch UPDATEs and DELETEs pretty much unusable, 'cause there'd always be the risk of tripping over some invisible row and getting and error. And second, it avoids mimicking functionality that is already provided by an existing feature, namely triggers. People will have to deal with the trigger ordering issue, but that's nothing new, and I bet most people have a system in place for that. I usually prefix my trigger names with 'a_' to 'z_', for example, to make the ordering explicit. Another issue, BTW, are FOREIGN KEY constraints. Should you be allowed to created references to rows which are invisible to you, or should FOREIGN KEY constraints be exempt from security policies? I'd say they shouldn't be, i.e. the policy WHERE clause should be added to constraint checking queries like usual. But maybe I'm missing some reason why that'd be undesirable… best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers