Re: Deny access materialzsed view

On 24 November 2010 14:56, Düster Horst <Horst(dot)Duester(at)bd(dot)so(dot)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