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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
