From: | "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "Kraus Philipp *EXTERN*" <philipp(dot)kraus(at)flashpixx(dot)de>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: acessibility for tables |
Date: | 2012-06-06 07:24:16 |
Message-ID: | D960CB61B694CF459DCFB4B0128514C207F812A0@exadv11.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Willy-Bas Loos | 2012-06-06 11:03:56 | column definition for setof record functions (like dblink and crosstabs) |
Previous Message | Albe Laurenz | 2012-06-06 07:14:58 | Re: db alias |