Re: Deny access materialzsed view

[Szymon Guz <mabewlun@xxxxxxxxx>]

On 24 November 2010 14:56, DÃster Horst <Horst.Duester@xxxxxxxx> 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