Thread: 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.
-----------------------------------------------------------------
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 >
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 >
> -----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.
>
>
On Thu, Apr 03, 2003 at 12:52:10 -0500, Tamir Halperin <tamir@brobus.net> wrote: > 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. On the other hand implementing security in the application doesn't work if the application runs on the user's machine. For example Peoplesoft's security for version 7.x is totally broken if you let people run two tier (which you have to to let people do some things). The app eventually connects to the data base with full update access to all tables and relies on the user not tampering with the app. If the app runs on a secure machine then implementing security on the app server is reasonable (at least under some circumstances).
One technique I used was to include a system profile in the data layer that allows the business layer to retrieve an informationfor a user account such as what is the user allowed to see and do. This way, none of the security related informationis stored at the user layer. You can certainly employ encryption of various kinds to ensure that it isn't evenvisible during transmission between user and business layers. The profile, being the basis for the data driven security management, can be constructed at the time the user account iscreated. It can be modified by an administrator to increase or decrease exposure to other modules / data in the app / db.All of the authentication happens, then, between the business layer and the data layer so that no account informationcan ever be retreived outside the server farm environment. Just some thoughts. Tamir > -----Original Message----- > From: Bruno Wolff III [mailto:bruno@wolff.to] > Sent: Thursday, April 03, 2003 3:41 PM > To: Tamir Halperin > Cc: Peter Csaba; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Rules, Triggers something more challenging > > > On Thu, Apr 03, 2003 at 12:52:10 -0500, > Tamir Halperin <tamir@brobus.net> wrote: > > 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. > > On the other hand implementing security in the application > doesn't work > if the application runs on the user's machine. For example > Peoplesoft's > security for version 7.x is totally broken if you let people run two > tier (which you have to to let people do some things). The > app eventually > connects to the data base with full update access to all > tables and relies > on the user not tampering with the app. > > If the app runs on a secure machine then implementing security on the > app server is reasonable (at least under some circumstances). >
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 :(. 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');
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.