From: Düster Horst <Horst.Duester@bd.so.ch>
To: mabewlun <mabewlun@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Deny access materialzsed view
Date: Fri, 26 Nov 2010 12:28:46 +0100
Message-ID: <H00002eb084968e1.1290770926.srsofaioi6145.ktso.ch@MHS> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Horst.Duester@bd.so.ch, mabewlun@gmail.com
Subject: Re: Deny access materialzsed view
In-Reply-To: <H00002eb084968e1.1290770926.srsofaioi6145.ktso.ch@MHS>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox