On 24 November 2010 14:56, Düster Horst <horst.dues...@bd.so.ch> wrote:
> Hi Szymon Guz > > Thanks a lot for your response. I think the SECURITY DEFINER doesn't solve > my problem. Here an example (stupid I know but just for explanation): > > 1. I have created the view myView (select id from myTable) with an insert > rule and I have created a table myTable (id integer, time timestamp). > 2. Now I add a record to myView with: insert into myView (id) values (1). > 3. The insert rule adds the value of id to myTable and sets a timestamp > additionally > > My problem now is that all users which have write access to myView shoudn't > have write access to myTable to avoid manipulations of myTable apart from > the logic of the myView rule. In the present configuration they must have > write access to myTable for inserting data at the moment. Only the db admin > should have write access to myTable and nobody else. Additionally in this > approach there is no function. As the result I can't use the SECURITY > DEFINER statement. As I understand does the SECURITY DEFINER statement only > modify the execution rights of a function. > > Maybe you have further hints or ideas? > > Hi, I don't get it fully, but I will try: 1. myView is read/write, myTable is readonly, dbadmin can write to myTable All users can select myTable (revoke all, grant select). DbAdmin can update/insert myTable. (grant all) DbAdmin creates procedures executed at update/insert myView, those procedures are defined with security definer, so they can insert/update myTable. With this configuration, a normal user can select from the view, and update it, as there will be executed procedures with the DbAdmin rights, and he can update myTable. 2. myTable is read/write for normal user Just grant proper rights for a normal user. More about granting rights you can find here: http://www.postgresql.org/docs/9.0/static/sql-grant.html Hope that helped a little. regards Szymon
