Thread: acessibility for tables

Hello,

I new on this mailing list and I need a little bit help for an idea to create different accesses to a database with
Postgres9.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.

Thanks

Phil

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

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




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

Willy-Bas Loos wrote:
> I did something like that some years ago.
> Albe, are rules out of grace?

Sort of, for many people:
http://archives.postgresql.org/pgsql-hackers/2012-04/msg00395.php

They are difficult to get right and usually not better than triggers.
Ever since there were INSTEAD OF Triggers for all actions, this is
generally the prefered option.

Yours,
Laurenz Albe

Do you mean, you want everyone to see the data, but only the "owner" can
Also, you asked if this is a good idea.
Well, it works. But it is certainly not advisable to use the postgres authorisation system for anything else than postgres. I mean, don't try to use this for a  web site of sorts.
Then there are other ways to do it, if you really just want to make a database. Maybe inheritance could come in handy: a table per user and a parent table with select-only rights for all. (http://www.postgresql.org/docs/9.1/static/ddl-inherit.html)

HTH

WBL

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

Hello,

On 2012-06-06 09:24:16 +0200, Albe Laurenz said:

> 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.

I have created the trigger with "insead of". If I try to insert a row into the
view I get an error:

You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF
INSERT trigger

Should I create a rule !?

Thanks

Phil

the error sounds clear enough.
you should make a very simple test case that shows your problem (including some test data).
then people on the list can help

cheers,

WBL




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

Hi,

I have recreated the view and the trigger function, now it works. I don't know why it does not run on my first try.

My functions shows now:

BEGIN

    IF TG_OP = 'INSERT' then

        insert into mytargettable values NEW;

    ELSIF TG_OP = 'UPDATE' then

        raise notice 'UPDATE trigger, OLD = [%], NEW = [%]', OLD, NEW;

    ELSE

        raise notice 'DELETE trigger, OLD = [%]', OLD;

    END IF;

    raise exception 'trigger uncomplete ended';

END;

Is this a correct structure, so that each trigger-typ must be run a SQL statement?

Can I pass the NEW and OLD argument to the original table eg insert into mytargettable values NEW,

or update mytargettable from NEW?

Thanks

Phil

On 2012-06-07 10:55:48 +0200, Willy-Bas Loos said:

the error sounds clear enough.

you should make a very simple test case that shows your problem (including some test data).

then people on the list can help

cheers,

WBL

On Thu, Jun 7, 2012 at 1:59 AM, Philipp Kraus <philipp.kraus@flashpixx.de> wrote:

Hello,

On 2012-06-06 09:24:16 +0200, Albe Laurenz said:

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.

I have created the trigger with "insead of". If I try to insert a row into the

view I get an error:

You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger

Should I create a rule !?

Thanks

Phil

-- 

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