Re: Rules, Triggers something more challenging - Mailing list pgsql-general

Haven't you asked tihs question already? :-) I keep seeing what to me is the
exact same question with no replies in the question. And I remember replying to
it, and seeing replies to it.

did any of the answers work?

Peter Csaba wrote:
> Hello,
>
> I have the following problem. I have a database with different tables.
> This database is accessed from different users using different logins to
> access some of the tables.
> It's not a problem to limit the access of these users to certain tables.
> They can be included into a group and allowed access based on group granting
> to tables.
>
> My problem is to set these users to be able to access (SELECT| MODIFY|
> UPDATE) some rows
> from a given table based on some information from the given row.
>
>
> For example:
> We have various locations (discos) where people are visitors. These
> locations store the visitors into a table.
>
> Table:
>
> CREATE TABLE "visitors" (
>   "visitor_id" SERIAL,
>   "login" text,
>   "password" text,
>   "disco_id" int4
> );
>
> Each disco (location) is accessing the database with their own login (ie:
> disco1, disco2).
> Each disco has a disco_id. It is linked to the login which the disco uses to
> access the database.
> For one login more than one disco_id can be assigned, so with a given login
> several disco_id accesses are allowed.
>
>
> For this I set up a permission table where we have:
>
> create table permissions (
>          disco_id int4,
>          username name not null
> );
> here we have for example:
>  35    disco1
>  40    disco1
>  44    disco2
>
> Users logged in with "disco1" should be able to INSERT, SELECT, MODIFY data
> from the visitors table where the disco_id is 35 or 40 in our example.
>
>
> Let's hide the visitors table from there users and let them think that we
> use besucher table to store these visitors data.
>
> For this I  define a view:
>
> create view besucher as
>          select v.* from visitors v, permissions  P
>         where  v.disco_id=P.disco_id
>         AND P.username =  CURRENT_USER;
>
>
> So  if I log in as user "disco1" and enter:
> select * from besucher;                        then I get only user from
> disco 35 and 40.
>
> This is good. SELECT IS SOLVED.
>
>
>
> Now if I set a RULE like:
>
> create  rule visitors_del as ON DELETE TO besucher
>         DO  INSTEAD  DELETE FROM visitors WHERE
>         visitor_id=OLD.visitor_id
>         AND  permissions.username = CURRENT_USER
>         AND  visitors.disco_id=permissions.disco_id;
>
> This allows  me to not to be able to delete just the visitors belonging to
> disco  35 and 40.
>
> So:
> delete from visitors; - would  only delete the users belonging to disco 35,
> 40. So far this is  ok aswell.
>
> The problem is that I can't  create rules for insert and update.
> For insert I wanted  to set up something like:
>
> create rule visitors_ins as  ON INSERT TO besucher
>         WHERE  NEW.disco_id!=permissions.disco_id
>         AND permissions.username =  CURRENT_USER
>         DO INSTEAD  NOTHING;
>
> So if I want to insert a row where disco_id is not available in the
> permissions table to the current user - just skip it, do nothing.
> Unfortunately this  rule cannot be created the way I wrote above.
>
> Can anybody tell me how this can be realized or to give some better
> solutions ideas?
>
> The ideea is, to not to allow users who logged in with user "disco1" for
> example to access
> data othen than they are allowed to access in the permissions table.
>
>
> Best regards,
> Peter Csaba
> Director General
> WebNova Romania
> www.webnova.ro
> www.muresinfo.ro
>
> str. Bradului nr. 8
> Tg.-Mures, 4300, Romania
> Tel: +40-265-162417
> Mobile: +40-722-505295
>
> -----------------------------------------------------------------
> ATTENTION:
> No legal consequences can be derived from the content of this
> e-mail and/or its attachments. Neither is sender committed to
> these. The content of this e-mail is exclusively intended for
> addressee(s) and information purposes. Should you receive this
> message by mistake, you are hereby notified that any disclosure,
> reproduction, distribution or use of this message is strictly
> prohibited. Sender accepts no liability for any damage resulting
> from the use and/or acceptation of the content of this e-mail.
> Always scan attachments for viruses before opening them.
> -----------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>