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