Обсуждение: Re: [pgadmin-support] Separate Sessions?? (View data <-> Query tool)
how about this:
create role firm1 nologin;
create role john password 'secret' login;
grant firm1 to john;
create role firm2 nologin;
create role amy password 'secret' login;
grant firm2 to amy;
create table table1 (id serial primary key,firm integer, val integer);
insert into table1 (firm, val) values (1, 101);
insert into table1 (firm, val) values (1, 102);
insert into table1 (firm, val) values (1, 103);
insert into table1 (firm, val) values (1, 104);
insert into table1 (firm, val) values (1, 105);
insert into table1 (firm, val) values (2, 206);
insert into table1 (firm, val) values (2, 207);
insert into table1 (firm, val) values (2, 208);
insert into table1 (firm, val) values (2, 209);
insert into table1 (firm, val) values (2, 210);
revoke all on table1 from john;
revoke all on table1 from amy;
revoke all on table1 from firm1;
revoke all on table1 from firm2;
create view view_firm1 as select * from table1 where firm =1;
create view view_firm2 as select * from table1 where firm =2;
grant select, update on view_firm1 to firm1;
grant select, update on view_firm2 to firm2;
create or replace rule _update as on update
to view_firm1 do instead
update table1 set val = NEW.val where id=old.id;
create or replace rule _update as on update
to view_firm2 do instead
update table1 set val = NEW.val where id=old.id;
--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
create role firm1 nologin;
create role john password 'secret' login;
grant firm1 to john;
create role firm2 nologin;
create role amy password 'secret' login;
grant firm2 to amy;
create table table1 (id serial primary key,firm integer, val integer);
insert into table1 (firm, val) values (1, 101);
insert into table1 (firm, val) values (1, 102);
insert into table1 (firm, val) values (1, 103);
insert into table1 (firm, val) values (1, 104);
insert into table1 (firm, val) values (1, 105);
insert into table1 (firm, val) values (2, 206);
insert into table1 (firm, val) values (2, 207);
insert into table1 (firm, val) values (2, 208);
insert into table1 (firm, val) values (2, 209);
insert into table1 (firm, val) values (2, 210);
revoke all on table1 from john;
revoke all on table1 from amy;
revoke all on table1 from firm1;
revoke all on table1 from firm2;
create view view_firm1 as select * from table1 where firm =1;
create view view_firm2 as select * from table1 where firm =2;
grant select, update on view_firm1 to firm1;
grant select, update on view_firm2 to firm2;
create or replace rule _update as on update
to view_firm1 do instead
update table1 set val = NEW.val where id=old.id;
create or replace rule _update as on update
to view_firm2 do instead
update table1 set val = NEW.val where id=old.id;
On Thu, Nov 27, 2008 at 3:06 PM, Csaba Együd <csegyud@gmail.com> wrote:
""Willy-Bas Loos"" <willybas@gmail.com> a következoket írta üzenetében news:1dd6057e0811270036s67a6b8baqda17273b0484e53d@mail.gmail.com...Willy,The problem is that my triggers refuse modifying a record if a given Temp table is not created before, because one or more fields have their default values from that temp table
So why don't you either use a normal table, or if you need simultaneous use of the trigger with different values, use an array instead of a table. Or you might create the temp table in the trigger function..
If you create a normal table you can still delete it afterwards.
hth
WBL
--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
you are right but... this is a bit difficult but I try to explain. I develop an office management software which uses Postgres as a RDB server. This software has to handle many firms separately but by design it has to use only one database (customer requirement) so I had to find out a solid way to separate the firms form each other. I find out that I qualify every firm-dependent record with a firmid.
After logging in the client software will create a TEMP table in which it stores the selected firmid. This TEMP table will live durring the login session. After this point every views will filter to this firmid and won't give back other firms' data just this. I know that other client softwares will be able to select data from the tables. (Unfortunatelly I don't know a way to revoke select right from a table while a view can select from it... Do you know such thing?) Because of this I know that the clean way would be using separate databases for separate firms but this was not my decision. :( If you know a setting which can improve the security pleas let me know.
Also I had to assure that the INSERT and UPDATE operations will only work for the selected firm and the user won't be able to potter with other firms' data even not accidentally. This is done in triggers and this causes my "View data tool" problem because viewing is possible but modifying is enabled only when a firmid is set in the TEMP table.
BTW, I can use script templates with Query tool (like INSERT or UPDATE script) which are very handy.
Thx,
--
Best Regards,
Csaba Együd
IN-FO Studio
--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
Only, it's too bad that you can't update the records from the "view data" dialog.
It works in access2000, it doesn't know the difference between views and tables (the difference is actually very small).
You probably get some annoying error when you try to edit a normal select view from access, though.
I guess pg_admin assumes that views cannot be edited, which is incorrect in this case.
Maybe this is something that could be enhanced in a future release?
oh, i see i forgot:
revoke all on view_firm1 from amy;
revoke all on view_firm1 from firm2;
revoke all on view_firm2 from john;
revoke all on view_firm2 from firm1;
Cheers,
WBL
--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
It works in access2000, it doesn't know the difference between views and tables (the difference is actually very small).
You probably get some annoying error when you try to edit a normal select view from access, though.
I guess pg_admin assumes that views cannot be edited, which is incorrect in this case.
Maybe this is something that could be enhanced in a future release?
oh, i see i forgot:
revoke all on view_firm1 from amy;
revoke all on view_firm1 from firm2;
revoke all on view_firm2 from john;
revoke all on view_firm2 from firm1;
Cheers,
WBL
--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
On Fri, Nov 28, 2008 at 11:04 AM, Willy-Bas Loos <willybas@gmail.com> wrote: > Only, it's too bad that you can't update the records from the "view data" > dialog. > It works in access2000, it doesn't know the difference between views and > tables (the difference is actually very small). > You probably get some annoying error when you try to edit a normal select > view from access, though. Yeah, it's actually pretty hard to do in pgAdmin, because we have no easy way of telling what the effects of the update are. *thinks* - I wonder if we can use RETURNING for that now... -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
oh, and :
revoke all on table1 from public;
revoke all on view_firm1 from public;
revoke all on view_firm2 from public;
duh
--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
revoke all on table1 from public;
revoke all on view_firm1 from public;
revoke all on view_firm2 from public;
duh
--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
>If I understand well you say that I could isolate data by using separate group roles for each firms and defining updatable views to work on.
yes. it's one way to do it.
As simple as this example is, you might as well make a seperate table for each firm. Then you wouldn't need to bother with views and rules.
The views get interesting when you want to allow one record to be selected by more than one firm.
Or if you want to be able to do stuff with all of the data in table1.
The disadvantage is that each firm has to use diffent code, they select from a different table. If you want to get really crazy you can add a username to table1 and make just one view that filters on applicable_roles (all the roles granted to the current_role). But that doesn't scale so well i guess.
>This sounds quite promissing.
Well, it only works as long as you use the database authentication (and authorisation) system. Meaning that your application logs in to the database with a seperate account for each user.
Works great for database clients like pgadmin, but it doesn't make you happy in PHP or standard webservice stuff.
In that case you don't need this kind of authorisation in the database layer, but you do it in a different application.
>The most strange thing for me is revoking all rights from table1 but I guess this is the heart of it... If I revoke all rights on table1 from the roles how will the user be able to modify the data of table1 trough the views?
> I read somewhere that when a user wants to select rows from a view the user needs to have the select permission for the undelaying table as well, and thats the case with updating or inserting too.
The view uses the privileges of its owner, not those of the current_useryes. it's one way to do it.
As simple as this example is, you might as well make a seperate table for each firm. Then you wouldn't need to bother with views and rules.
The views get interesting when you want to allow one record to be selected by more than one firm.
Or if you want to be able to do stuff with all of the data in table1.
The disadvantage is that each firm has to use diffent code, they select from a different table. If you want to get really crazy you can add a username to table1 and make just one view that filters on applicable_roles (all the roles granted to the current_role). But that doesn't scale so well i guess.
>This sounds quite promissing.
Well, it only works as long as you use the database authentication (and authorisation) system. Meaning that your application logs in to the database with a seperate account for each user.
Works great for database clients like pgadmin, but it doesn't make you happy in PHP or standard webservice stuff.
In that case you don't need this kind of authorisation in the database layer, but you do it in a different application.
>The most strange thing for me is revoking all rights from table1 but I guess this is the heart of it... If I revoke all rights on table1 from the roles how will the user be able to modify the data of table1 trough the views?
> I read somewhere that when a user wants to select rows from a view the user needs to have the select permission for the undelaying table as well, and thats the case with updating or inserting too.
So for example:
john has no rights on table1, but can make a view "create view bla as select * from table1". There will be a "permission denied" error for anyone who selects from the view, even superusers.
the other way around:
fred is a superuser. When he makes the same view, all records will be visible to those that (may) select from it.
just try it:
--based on the code from before, you now have the right to select from table1 since you created it and are the owner
set role john;
create view bla as select * from table1;
select * from bla;
reset role;
select current_role;
select * from bla;
cheers,
WBL
--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw