Rules / Triggers something a little bit more chellanging - Mailing list pgsql-hackers

<p>Hello,<p>I have the following problem. I have a database with different tables. <br />This database is accessed from
differentusers using different logins to access some of the tables.<br />It's not a problem to limit the access of
theseusers to certain tables.<br />They can be included into a group and allowed access based on group granting to
tables.<p>Myproblem is to set these users to be able to access (SELECT| MODIFY| UPDATE) some rows<br />from a given
tablebased on some information from the given row.<p> <br />For example:<br />We have various locations (discos) where
peopleare visitors. These locations store the visitors into a table.<p>Table:<br /> <br />CREATE TABLE "visitors" (<br
/> "visitor_id" SERIAL,<br />  "login" text,<br />  "password" text,<br />  "disco_id" int4<br />);<br /> <br />Each
disco(location) is accessing the database with their own login (ie: disco1, disco2).<br />Each disco has a disco_id. It
islinked to the login which the disco uses to access the database.<br />For one login more than one disco_id can be
assigned,so with a given login several disco_id accesses are allowed.<p> <br />For this I set up a permission table
wherewe have:<p>create table permissions (<br />         disco_id int4,<br />         username name not null<br />);<br
/>herewe have for example:<br /> 35    disco1<br /> 40    disco1<br /> 44    disco2<p>Users logged in with �disco1�
shouldbe able to INSERT, SELECT, MODIFY data from the visitors table where the disco_id is 35 or 40 in our example.<br
/> <p>Let'shide the visitors table from there users and let them think that we use besucher table to store these
visitorsdata.<p>For this I  define a view:<p>create view besucher as<br />         select v.* from visitors v,
permissions P<br />        where  v.disco_id=P.disco_id<br />        AND P.username =  CURRENT_USER;<p><br />So  if I
login as user �disco1� and enter:<br />select * from besucher;                        then I get only user from disco
35and 40. <p>This is good. SELECT IS SOLVED.<p><br /> <br />Now if I set a RULE like:<p>create  rule visitors_del as ON
DELETETO besucher<br />        DO  INSTEAD  DELETE FROM visitors WHERE<br />        visitor_id=OLD.visitor_id<br
/>       AND  permissions.username = CURRENT_USER<br />        AND  visitors.disco_id=permissions.disco_id;<br /> <br
/>Thisallows  me to not to be able to delete just the visitors belonging to  disco  35 and 40.<p>So:<br />delete from
visitors;- would  only delete the users belonging to disco 35,  40. So far this is  ok aswell.<br /> <br />The problem
isthat I can't  create rules for insert and update.<br />For insert I wanted  to set up something like:<p>create rule
visitors_insas  ON INSERT TO besucher<br />        WHERE  NEW.disco_id!=permissions.disco_id<br />        AND
permissions.username=  CURRENT_USER<br />        DO INSTEAD  NOTHING;<p>So if I want to insert a row where disco_id is
notavailable in the permissions table to the current user - just skip it, do nothing.<br />Unfortunately this  rule
cannotbe created the way I wrote above.<br /> <br />Can anybody tell me how this can be realized or to give some better
solutionsideas?<p>The ideea is, to not to allow users who logged in with user �disco1� for example to access<br />data
othenthan they are allowed to access in the permissions table.<p><br />Best Regards,<br />Peter<p><br /><hr size="1"
/>Doyou Yahoo!?<br /><a href="http://rd.yahoo.com/platinum/evt=8162/*http://platinum.yahoo.com/splash.html">Yahoo!
Platinum</a>- Watch CBS' NCAA March Madness, <a
href="http://rd.yahoo.com/platinum/evt=8162/*http://platinum.yahoo.com/splash.html">liveon your desktop</a>!