Re: acessibility for tables

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(dot)albe(at)wien(dot)gv(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth