Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 4789C1337B47 for ; Fri, 26 Nov 2010 07:28:58 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 03224-06 for ; Fri, 26 Nov 2010 11:28:50 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from in-mail1.so.ch (in-mail1.so.ch [193.135.75.102]) by mail.postgresql.org (Postfix) with ESMTP id 114451337999 for ; Fri, 26 Nov 2010 07:28:49 -0400 (AST) X-IronPort-AV: E=Sophos;i="4.59,260,1288566000"; d="scan'208,217";a="24790348" Received: from srsofaioi11504.ktso.ch (HELO so.ch) ([10.36.21.182]) by out-mail1.so.ch with ESMTP; 26 Nov 2010 12:28:46 +0100 Received: from srsofaioi6145.ktso.ch (localhost.localdomain [127.0.0.1]) by so.ch (8.13.8/8.13.8) with ESMTP id oAQBSkwZ013947; Fri, 26 Nov 2010 12:28:46 +0100 Date: Fri, 26 Nov 2010 12:28:46 +0100 From: =?UTF-8?Q?D=C3=BCster_Horst?= To: mabewlun cc: pgsql-admin Message-ID: Subject: Re: Deny access materialzsed view x-scalix-Hops: 1 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="2_0_2a6_2505fca6MHTML_=_01" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-4.209 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/227 X-Sequence-Number: 36193 --2_0_2a6_2505fca6MHTML_=_01 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi Szymon Guz =20 =20 Thanks a lot for you explanations. I'll give it a try. =20 Regards Horst =20 ------------------------------------------------=20 Dr. Horst D=FCster=20 Stv. Amtschef / kantonaler GIS-Koordinator=20 Kanton Solothurn=20 Bau- und Justizdepartement=20 Amt f=FCr Geoinformation=20 SO!GIS Koordination=20 R=F6tistrasse 4=20 CH-4501 Solothurn=20 Telefon ++41(0)32 627 25 32=20 Mobil ++41(0)79 511 54 12=20 Telefax ++41(0)32 627 22 14=20 mailto:horst.duester@bd.so.ch=20 http://www.agi.so.ch=20 =20 -----Urspr=FCngliche Nachricht----- Von: Szymon Guz [mailto:mabewlun@gmail.com] Gesendet am: Mittwoch, 24. November 2010 15:26 An: D=FCster Horst Cc: pgsql-admin Betreff: Re: Re: [ADMIN] Deny access materialzsed view =20 =20 =20 On 24 November 2010 14:56, D=FCster Horst < Horst.Duester@bd.so.ch> wrote: =20 Hi Szymon Guz =20 =20 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):=20 =20 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).=20 2. Now I add a record to myView with: insert into myView (id) values (1).=20 3. The insert rule adds the value of id to myTable and sets a timestamp additionally=20 =20 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.=20 =20 Maybe you have further hints or ideas?=20 =20 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. =20 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 =20 --2_0_2a6_2505fca6MHTML_=_01 Content-Type: text/html Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Hi Szymon Guz =20
 
Thanks=20 a lot for you explanations. I'll give it a try.
 
Regards
Horst
 

------------------------------------------------=20

Dr. Horst Düster
Stv. Amtschef / kantonaler GIS-Koordinator

Kanton Solothurn
Bau- und Justizdepartement
Amt für=20 Geoinformation
SO!GIS Koordination=20
Rötistrasse 4
CH-4501 Solothurn=20

Telefon ++41(0)32 627 25 32
Mobil   ++41(0)79 511 54 12
Telefax ++41(0)32 627 22 14=20

mailto:horst.duester@bd.so.ch<= /FONT>=20
http://www.agi.so.ch=20

 
-----Ursprüngliche=20 Nachricht-----
Von: Szymon Guz=20 [mailto:mabewlun@gmail.com]
Gesendet am: Mittwoch, 24. November 2010=20 15:26
An: Düster Horst
Cc: pgsql-admin
Betreff:=20 Re: Re: [ADMIN] Deny access materialzsed view


On 24 November 2010 14:56, Düster Horst <Horst.Duester@bd.so.ch>=20 wrote:
=20

Hi Szymon Guz =20
=20
Thanks a lot f= or your=20 response. I think the SECURITY DEFINER doesn't solve my problem. Here= an=20 example (stupid I know but just for explanation):=20
= =20
1. I have crea= ted the=20 view  myView (select id from myTable) with an insert rule and I h= ave=20 created a table myTable (id integer, time timestamp).=20
2. Now I add a= record to=20 myView with: insert into myView (id) values (1).
3. The insert r= ule adds the=20 value of id to myTable and sets a timestamp additionally=20
= =20
My problem now= is that all=20 users which have write access to myView shoudn't have write access to= =20 myTable to avoid manipulations of myTable apart from the logic of the= myView=20 rule. In the present configuration they must have write access t= o=20 myTable for inserting data at the moment. Only the db admin should ha= ve=20 write access to myTable and nobody else. Additionally in this approac= h there=20 is no function. As the result I can't use the SECURITY DEFINER statem= ent. As=20 I understand does the SECURITY DEFINER statement only modify the exec= ution=20 rights of a function.
= =20
Maybe you have= further=20 hints or ideas?=20
 

Hi,
I don't get it fully, but I will try:

1. myView is read/write, myTable is readonly, dbadmin can write to= =20 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= =20 procedures are defined with security definer, so they can insert/update= =20 myTable.

With this configuration, a normal user can select from the view, a= nd=20 update it, as there will be executed procedures with the DbAdmin rights= , and=20 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

--2_0_2a6_2505fca6MHTML_=_01--