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.albe@xxxxxxxxxx> wrote:
Your approach with views should work just fine - deny the usersKraus 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.
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
