[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-admin
Subject: Re: [ADMIN] Deny access materialzsed view
From: Düster_Horst <Horst.Duester () bd ! so ! ch>
Date: 2010-11-26 11:28:46
Message-ID: H00002eb084968e1.1290770926.srsofaioi6145.ktso.ch () MHS
[Download RAW message or body]
Hi Szymon Guz
Thanks a lot for you explanations. I'll give it a try.
Regards
Horst
------------------------------------------------
Dr. Horst Düster
Stv. Amtschef / kantonaler GIS-Koordinator
Kanton Solothurn
Bau- und Justizdepartement
Amt für Geoinformation
SO!GIS Koordination
Rötistrasse 4
CH-4501 Solothurn
Telefon ++41(0)32 627 25 32
Mobil ++41(0)79 511 54 12
Telefax ++41(0)32 627 22 14
mailto:horst.duester@bd.so.ch
http://www.agi.so.ch
-----Ursprüngliche Nachricht-----
Von: Szymon Guz [mailto:mabewlun@gmail.com]
Gesendet am: Mittwoch, 24. November 2010 15:26
An: Düster Horst
Cc: pgsql-admin
Betreff: Re: Re: [ADMIN] Deny access materialzsed view
On 24 November 2010 14:56, Düster Horst < Horst.Duester@bd.so.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
[Attachment #3 (text/html)]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="MSHTML 5.50.4522.1800" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=62022811-26112010><SPAN>Hi </SPAN>Szymon Guz
</SPAN>
</DIV>
<DIV><SPAN class=62022811-26112010></SPAN>
</DIV>
<DIV><SPAN class=62022811-26112010><FONT face=Arial color=#0000ff size=2>Thanks
a lot for you explanations. I'll give it a try.</FONT></SPAN>
</DIV>
<DIV><SPAN class=62022811-26112010><FONT face=Arial color=#0000ff
size=2></FONT></SPAN>
</DIV>
<DIV><SPAN class=62022811-26112010><FONT face=Arial color=#0000ff
size=2>Regards</FONT></SPAN>
</DIV>
<DIV><SPAN class=62022811-26112010><FONT face=Arial color=#0000ff
size=2>Horst</FONT></SPAN>
</DIV>
<DIV>
</DIV>
<P>
<FONT face=Arial
size=2>------------------------------------------------</FONT>
</P>
<P><FONT face=Arial size=2>Dr. Horst Düster</FONT> <BR>
<FONT face=Arial
size=2>Stv. Amtschef / kantonaler GIS-Koordinator </FONT>
</P>
<P><FONT face=Arial size=2>Kanton Solothurn</FONT> <BR>
<FONT face=Arial
size=2>Bau- und Justizdepartement</FONT> <BR>
<FONT face=Arial size=2>Amt für
Geoinformation</FONT> <BR>
<FONT face=Arial size=2>SO!GIS Koordination</FONT>
<BR>
<FONT face=Arial size=2>Rötistrasse 4</FONT> <BR>
<FONT face=Arial
size=2>CH-4501 Solothurn</FONT>
</P>
<P><FONT face=Arial size=2>Telefon ++41(0)32 627 25 32</FONT> <BR>
<FONT
face=Arial size=2>Mobil ++41(0)79 511 54 12</FONT> <BR>
<FONT
face=Arial size=2>Telefax ++41(0)32 627 22 14</FONT>
</P>
<P><FONT face=Arial color=#000000 size=2><A target=_blank
href="mailto:horst.duester@bd.so.ch">mailto:horst.duester@bd.so.ch</A></FONT>
<BR>
<FONT face=Arial color=#000000 size=2><A target=_blank
href="http://www.agi.so.ch/">http://www.agi.so.ch</A></FONT>
</P>
<DIV>
</DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV><FONT face="Times New Roman" size=2>-----Ursprüngliche
Nachricht-----<BR>
<B>Von:</B> Szymon Guz
[mailto:mabewlun@gmail.com]<BR>
<B>Gesendet am:</B> Mittwoch, 24. November 2010
15:26<BR>
<B>An:</B> Düster Horst<BR>
<B>Cc:</B> pgsql-admin<BR>
<B>Betreff:</B>
Re: Re: [ADMIN] Deny access materialzsed view<BR>
</FONT><BR>
<BR>
</DIV>
<DIV class=gmail_quote>
<P>
On 24 November 2010 14:56, Düster Horst <SPAN dir=ltr><<A
href="mailto:Horst.Duester@bd.so.ch">Horst.Duester@bd.so.ch</A>></SPAN>
wrote:<BR>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">
<DIV>
<DIV>
<DIV><SPAN>Hi </SPAN>Szymon Guz
</DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT>
</DIV>
<DIV><SPAN><FONT face=Arial color=#0000ff size=2>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):</FONT></SPAN>
</DIV>
<DIV><SPAN><FONT face=Arial color=#0000ff size=2></FONT></SPAN>
</DIV>
<DIV><SPAN><FONT face=Arial color=#0000ff size=2>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).</FONT></SPAN>
</DIV>
<DIV><SPAN><FONT face=Arial color=#0000ff size=2>2. Now I add a record to
myView with: insert into myView (id) values (1). </FONT></SPAN>
</DIV>
<DIV><SPAN><FONT face=Arial color=#0000ff size=2>3. The insert rule adds the
value of id to myTable and sets a timestamp additionally</FONT></SPAN>
</DIV>
<DIV><SPAN><FONT face=Arial color=#0000ff size=2></FONT></SPAN>
</DIV>
<DIV><SPAN><FONT face=Arial color=#0000ff size=2>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. </FONT></SPAN>
</DIV>
<DIV><SPAN><FONT face=Arial color=#0000ff size=2></FONT></SPAN>
</DIV>
<DIV><SPAN><FONT face=Arial color=#0000ff size=2>Maybe you have further
hints or ideas?</FONT></SPAN>
</DIV>
<DIV><SPAN><FONT face=Arial color=#0000ff
size=2></FONT></SPAN>
</DIV></DIV></DIV></BLOCKQUOTE>
<DIV><BR>
</DIV>
<DIV>Hi,
</DIV>
<DIV>I don't get it fully, but I will try:
</DIV>
<DIV><BR>
</DIV>
<DIV>1. myView is read/write, myTable is readonly, dbadmin can write to
myTable
</DIV>
<DIV><BR>
</DIV>
<DIV>All users can select myTable (revoke all, grant select).
</DIV>
<DIV>DbAdmin can update/insert myTable. (grant all)
</DIV>
<DIV>DbAdmin creates procedures executed at update/insert myView, those
procedures are defined with security definer, so they can insert/update
myTable.
</DIV>
<DIV><BR>
</DIV>
<DIV>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.
</DIV>
<DIV><BR>
</DIV>
<DIV>2. myTable is read/write for normal user
</DIV>
<DIV><BR>
</DIV>
<DIV>Just grant proper rights for a normal user.
</DIV>
<DIV>
</DIV>
<DIV>More about granting rights you can find here: <A
href="http://www.postgresql.org/docs/9.0/static/sql-grant.html">http://www.postgresql.org/docs/9.0/static/sql-grant.html</A>
</DIV>
<DIV><BR>
</DIV>
<DIV>Hope that helped a little.
</DIV>
<DIV><BR>
</DIV>
<DIV>regards
</DIV>
<DIV>Szymon
</DIV></DIV>
<P>
</P></BLOCKQUOTE></BODY></HTML>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic