I did something like that some years ago. Albe, are rules out of grace? Philipp, here's some code:
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; HTH, WBL On Wed, Jun 6, 2012 at 9:24 AM, Albe Laurenz <laurenz.a...@wien.gv.at>wrote: > Kraus Philipp wrote: > > I new on this mailing list and I need a little bit help for an idea to > create different accesses to a > > database with Postgres 9.1. > > I'm using this PG version at the time and I have created a database > with a scheme "storage". Within > > this schema are some > > tables, datatypes and stored procedure and each table has got a field > "owner" with is filled with the > > current_user on insert. > > The tables does not have any constraint to the pg system tables > because the username need not to be > > null, so I use the > > full character user name. > > > > I don't want that any user can do something like "select * from > storage.table". My target ist, that > > the user can only see > > these datasets, which he/she is owned (the field owner must be equal > to current_user). IMHO I have > > created some > > view within the public scheme, so the user can select the datasets on > this views, but I can't insert / > > update on views, so > > I would like to write some procedure which can be updated and insert > new data. So on this case my > > question is: > > Can I suppress any access to the "storage" schema only the datbase > itself should be do anything on it? > > Is this a good idea to create this different access? Is there a better > solution with postgres? > > > > I would like to denied any access to all datasets which are not owned. > > Your approach with views should work just fine - deny the users > all privileges on the base table and allow them access on the view. > > You can define INSTEAD OF triggers on a view so that you can insert, > update and delete on it. The trigger performs an operation on the > base table instead. > > Read up on triggers: > http://www.postgresql.org/docs/current/static/trigger-definition.html > > Yours, > Laurenz Albe > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
