Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id A35581337B39 for ; Wed, 24 Nov 2010 10:25:56 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 72966-02 for ; Wed, 24 Nov 2010 14:25:51 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iw0-f174.google.com (mail-iw0-f174.google.com [209.85.214.174]) by mail.postgresql.org (Postfix) with ESMTP id D891D1337B34 for ; Wed, 24 Nov 2010 10:25:48 -0400 (AST) Received: by iwn33 with SMTP id 33so1400156iwn.19 for ; Wed, 24 Nov 2010 06:25:49 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type; bh=kUFxezvNBorIgpABuoRiAkNXOYaVKhXtFOrvEnn6fFg=; b=Iyg0QLgn6iwjQAfLaL0zdpFvOw4M2ckQMBsDfv4ZzkL1xEblKChDRnvbb4XSP7+Hnt oofYAdDqQR2fbTE3cTz47WQRn8Cxf6x9cmmC3FKRj4ESK5NOIdAvEOI1yq7Ty8ejzAlj hEQ88dN9um2CAavL0lUGa4SgYzKkAFIGygrbs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=YFMrhExgKE8LJ1njJ9nd6MhXWkRQA0GDTy95beSid6LYLnwRf+9yWq8EOihjEuF/OC Q2zlL+OMp882zQvZoHGvMBlttqEcq5qgnAvG5jOs+slgho9iXH4uK6NgeVikPpu6TMLC o4LDjtcACX0+L9flziQ3pzTqklVsb2PyJPves= MIME-Version: 1.0 Received: by 10.229.98.71 with SMTP id p7mr7421899qcn.289.1290608748209; Wed, 24 Nov 2010 06:25:48 -0800 (PST) Received: by 10.229.238.85 with HTTP; Wed, 24 Nov 2010 06:25:48 -0800 (PST) In-Reply-To: References: Date: Wed, 24 Nov 2010 15:25:48 +0100 Message-ID: Subject: Re: Deny access materialzsed view From: Szymon Guz To: =?UTF-8?Q?D=C3=BCster_Horst?= Cc: pgsql-admin Content-Type: multipart/alternative; boundary=0016367f9b66fb26500495cd430c X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201011/214 X-Sequence-Number: 36180 --0016367f9b66fb26500495cd430c Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On 24 November 2010 14:56, D=C3=BCster Horst wrote= : > Hi Szymon Guz > > Thanks a lot for your response. I think the SECURITY DEFINER doesn't solv= e > 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 inser= t > 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 adm= in > 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 on= ly > 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 ca= n 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 --0016367f9b66fb26500495cd430c Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

On 24 November 2010 14:56, D=C3=BCster H= orst <Horst.= Duester@bd.so.ch> wrote:
Hi Szymon Guz=C2=A0
=C2=A0
Thanks=20 a lot for your response. I think the SECURITY DEFINER doesn't solve my = problem.=20 Here an example (stupid I know but just for explanation):
= =C2=A0
1. I=20 have created=C2=A0the view=C2=A0 myView (select id from myTable) with an in= sert=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
= =C2=A0
My=20 problem now is that all users which have write access to myView shoudn'= t have=20 write access to myTable to avoid manipulations of myTable apart from the lo= gic=20 of the myView rule.=C2=A0In the present configuration they must have write= =20 access to myTable for inserting data at the moment. Only the db admin shoul= d=20 have write access to myTable and nobody else. Additionally in this approach= =20 there is no function. As the result I can't use the SECURITY DEFINER st= atement.=20 As I understand does the SECURITY DEFINER statement only modify the executi= on=20 rights of a function.
= =C2=A0
Maybe=20 you have further hints or ideas?
= =C2=A0

Hi,
I do= n'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)
DbAdmi= n 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 DbAd= min rights, and he can update myTable.

2. myTable = is read/write for normal user

Just grant proper rights for a normal user.
= =C2=A0=C2=A0 =C2=A0
More about granting rights you can find here:= =C2=A0= http://www.postgresql.org/docs/9.0/static/sql-grant.html

Hope that helped a little.

reg= ards
Szymon
--0016367f9b66fb26500495cd430c--