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

> -----Original Message-----
> From: Peter Csaba [mailto:cpeter@webnova.ro]
> Sent: Thursday, April 03, 2003 1:18 PM
> To: Tamir Halperin; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Rules, Triggers something more challenging
>
>
> Thank you for your suggestions Tamir.
>
> It is ok to make the business layer handle where the user can
> insert and
> what data. My problem is the security concern. The business layer
> (interface) is using ODBC (PostgreSQL) which is logging the login and
> password in a PLAIN TEXT file :(.

I wasn't envisioning the business layer being wrapped up with the user interface. If you wanted to, you could divide
theminto two distinct environments. A web front end that makes calls to a Python (my favorite) object which, in turn
connectsto PostgreSQL (not via odbc). In this scenario, you're business layer (the python code) could be used by the
userlayer to begin a transaction. The business layer object will get to PostgreSQL in a secure fashion, submit context
informationand retrieve context-specific data. It would then return it to the user layer with, possibly, some
formattingto make it easy for the user layer to display it. 

> Using this information
> anybody can access
> the database with the given login and password, and he would
> be able to
> access all the rown (insert into it) from the table the user
> is allowed to
> get access. That's the reason why I have to do on server side
> (data layer).
>
> I also tried to make a function and trigger but without luck.
> I don't know
> how to access trigger parameters from functions.
> create function verify_permission() returns integer as
> 'select user_id from permissions
> WHERE permissions.disco_id = NEW.disco_id
> AND  permissions.username = CURRENT_USER' LANGUAGE SQL;
>
>
> CREATE TRIGGER verify_insert BEFORE INSERT ON visitors FOR EACH ROW
> EXECUTE PROCEDURE verify_permission('disco_id');

I wish I could help here with the syntax but all my db development experience is outside of PostgreSQL. I'm here to
learnas well. 

>
> Thank you for any help!
> -Peter
>
>
> ----- Original Message -----
> From: "Tamir Halperin" <tamir@brobus.net>
> To: "Peter Csaba" <cpeter@webnova.ro>; <pgsql-general@postgresql.org>
> Sent: Thursday, April 03, 2003 8:52 PM
> Subject: RE: [GENERAL] Rules, Triggers something more challenging
>
>
> > 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're beginning
> 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 logic is because
> at some point in
> the future your business logic may change and then you're required to
> heavily modify your database 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 performance
> issues to consider
> there as well. Alternatively, having a business layer of
> software technology
> between your user interface 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 making the
> request. Then, only
> data that is within the user's context can be deleted or
> modified. This
> would be constrained by 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 assigning
> context related
> information to the insert after the user is finished
> composing it in the
> user layer. The business layer can do this because it is
> managing the user
> layer's connection to the data layer and so it knows which user is
> 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.
>
>