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

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.
-----------------------------------------------------------------