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

I'd like to make a suggestion, Peter:

You may very well find a way to contstrain inserts using pgsql according to your business rules, but I observe that
you'rebeginning to develop a dependency on the data layer for your business logic. 

The reason you may not want to rely on db componentry (rules, triggers, etc...) to implement this type of business
logicis because at some point in the future your business logic may change and then you're required to heavily modify
yourdatabase when it may not be a problem with the data. 

Also, once you go down this road you begin to add more and more "data handling" code to your database and there are
performanceissues to consider there as well. Alternatively, having a business layer of software technology between your
userinterface and your database will probably have long term benefits in light of the problems I point out above. 

It appears to me that the user layer (interface) could ask for data that is within a context applicable to the user
makingthe request. Then, only data that is within the user's context can be deleted or modified. This would be
constrainedby a combination of features in the user and business layers. 

Likewise, when the user is presented with an interface for inserting visitors, the business layer can take care of
assigningcontext related information to the insert after the user is finished composing it in the user layer. The
businesslayer can do this because it is managing the user layer's connection to the data layer and so it knows which
useris attempting to insert data and, therefore, which context information should be included with the inserted data. 

How do these concerns and suggestions sound to you?

Tamir

> -----Original Message-----
> From: Peter Csaba [mailto:cpeter@webnova.ro]
> Sent: Tuesday, April 01, 2003 10:54 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Rules, Triggers something more challenging
>
>
>
> 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
>