Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 7B9FC1337B4E for ; Wed, 24 Nov 2010 09:57:05 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 05557-04 for ; Wed, 24 Nov 2010 13:56:58 +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 592931337B34 for ; Wed, 24 Nov 2010 09:56:57 -0400 (AST) X-IronPort-AV: E=Sophos;i="4.59,248,1288566000"; d="scan'208,217";a="24736778" Received: from srsofaioi11504.ktso.ch (HELO so.ch) ([10.36.21.182]) by out-mail1.so.ch with ESMTP; 24 Nov 2010 14:56:57 +0100 Received: from srsofaioi6145.ktso.ch (localhost.localdomain [127.0.0.1]) by so.ch (8.13.8/8.13.8) with ESMTP id oAODuu5L012374; Wed, 24 Nov 2010 14:56:57 +0100 Date: Wed, 24 Nov 2010 14:56:56 +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_da_24f1233dMHTML_=_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/213 X-Sequence-Number: 36179 --2_0_da_24f1233dMHTML_=_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 your response. I think the SECURITY DEFINER doesn't solve my problem. Here an example (stupid I know but just for explanation): =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). 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 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 Regards Horst ------------------------------------------------=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 09:17 An: D=FCster Horst Cc: pgsql-admin Betreff: Re: [ADMIN] Deny access materialzsed view =20 =20 =20 On 24 November 2010 08:57, D=FCster Horst < Horst.Duester@bd.so.ch> wrote: =20 Hi=20 =20 I created a materialized view to manage editings via a real postgres view. The view has some rules to deliver all inserts, updates and deletes to the underlaying table with the appropriate privileges, insert, update and delete, for members of an editing role. To avoid editings on the underlaying table I want to deny any access except select access to the table for all users. Does anyone outside has an idea how to manage the described situation?=20 =20 I'll appreciate any hint.=20 =20 Regards=20 Horst=20 - Hi, I'd use the security definer/invoker mechanism ( http://www.postgresql.org/docs/9.0/interactive/sql-createfunction.htm l). This way you could create special user who is the owner of the rule functions, and can read/write to the table, and normal user, who cannot read/write to the table, but can run those functions. regards Szymon Guz=20 =20 --2_0_da_24f1233dMHTML_=_01 Content-Type: text/html Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Hi Szymon Guz 
 
Thanks=20 a lot for your response. I think the SECURITY DEFINER doesn't solve my pr= oblem.=20 Here an example (stupid I know but just for explanation):
 
1. I=20 have created the view  myView (select id from myTable) with an i= nsert=20 rule and I have created a table myTable (id integer, time=20 timestamp).
2. Now=20 I add a record to myView with: insert into myView (id) values (1).=20
3. The=20 insert rule adds the value of id to myTable and sets a timestamp=20 additionally
 
My=20 problem now is that all users which have write access to myView shoudn't h= ave=20 write access to myTable to avoid manipulations of myTable apart from the l= ogic=20 of the myView rule. In the present configuration they must have writ= e=20 access to myTable for inserting data at the moment. Only the db admin sho= uld=20 have write access to myTable and nobody else. Additionally in this approa= ch=20 there is no function. As the result I can't use the SECURITY DEFINER stat= ement.=20 As I understand does the SECURITY DEFINER statement only modify the execu= tion=20 rights of a function.
 
Maybe=20 you have further hints or ideas?
 
Regards
Horst

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

Dr. Horst Düster
Stv. Amtschef / kantonaler GIS-Koor= dinator=20

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

Telefon ++41(0)32 627 25 3= 2=20
Mobil   ++41(0)79 5= 11 54=20 12
Telefax ++41(0)32 627 22=20 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 09:17
An: Düster Horst
Cc: pgsql-admin
Betreff:=20 Re: [ADMIN] Deny access materialzsed view


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

Hi
=20
I created a materialized v= iew to=20 manage editings via a real postgres view. The view has some rules to d= eliver=20 all inserts, updates and deletes to the underlaying table with the=20 appropriate privileges, insert, update and delete, for members o= f an=20 editing role. To avoid editings on the underlaying table I want to de= ny any=20 access except select access to the table for all users. Doe= s=20 anyone outside has an idea how to manage the described=20 situation?=20
=20
I'll appreciate any hint.=20 =20
=20
Regards=20
Horst=20

-


Hi,
I'd use the security definer/invoker mechanism (http://www.postgresql.org/docs/9.0/interactive/sql-createfunctio= n.html).=20 This way you could create special user who is the owner of the rule fun= ctions,=20 and can read/write to the table, and normal user, who cannot read/write= to the=20 table, but can run those functions.

regards
Szymon Guz 

--2_0_da_24f1233dMHTML_=_01--