pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDeny access materialzsed view
5+ messages / 2 participants
[nested] [flat]
* Deny access materialzsed view
@ 2010-11-24 07:57 Düster Horst <Horst.Duester@bd.so.ch>
0 siblings, 1 reply; 5+ messages in thread
From: Düster Horst @ 2010-11-24 07:57 UTC (permalink / raw)
To: pgsql-admin
Hi
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?
I'll appreciate any hint.
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Deny access materialzsed view
@ 2010-11-24 08:16 Szymon Guz <mabewlun@gmail.com>
parent: Düster Horst <Horst.Duester@bd.so.ch>
0 siblings, 0 replies; 5+ messages in thread
From: Szymon Guz @ 2010-11-24 08:16 UTC (permalink / raw)
To: Düster Horst <Horst.Duester@bd.so.ch>; +Cc: pgsql-admin
On 24 November 2010 08:57, Düster Horst <Horst.Duester@bd.so.ch> wrote:
> Hi
>
> 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?
>
> I'll appreciate any hint.
>
> Regards
> Horst
>
>
> -
>
Hi,
I'd use the security definer/invoker mechanism (
http://www.postgresql.org/docs/9.0/interactive/sql-createfunction.html).
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Deny access materialzsed view
@ 2010-11-24 13:56 Düster Horst <Horst.Duester@bd.so.ch>
0 siblings, 1 reply; 5+ messages in thread
From: Düster Horst @ 2010-11-24 13:56 UTC (permalink / raw)
To: mabewlun <mabewlun@gmail.com>; +Cc: pgsql-admin
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?
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 09:17
An: Düster Horst
Cc: pgsql-admin
Betreff: Re: [ADMIN] Deny access materialzsed view
On 24 November 2010 08:57, Düster Horst < Horst.Duester@bd.so.ch>
wrote:
Hi
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?
I'll appreciate any hint.
Regards
Horst
-
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Deny access materialzsed view
@ 2010-11-24 14:25 Szymon Guz <mabewlun@gmail.com>
parent: Düster Horst <Horst.Duester@bd.so.ch>
0 siblings, 0 replies; 5+ messages in thread
From: Szymon Guz @ 2010-11-24 14:25 UTC (permalink / raw)
To: Düster Horst <Horst.Duester@bd.so.ch>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Deny access materialzsed view
@ 2010-11-26 11:28 Düster Horst <Horst.Duester@bd.so.ch>
0 siblings, 0 replies; 5+ messages in thread
From: Düster Horst @ 2010-11-26 11:28 UTC (permalink / raw)
To: mabewlun <mabewlun@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-11-26 11:28 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-24 07:57 Deny access materialzsed view Düster Horst <Horst.Duester@bd.so.ch>
2010-11-24 08:16 ` Szymon Guz <mabewlun@gmail.com>
2010-11-24 13:56 Re: Deny access materialzsed view Düster Horst <Horst.Duester@bd.so.ch>
2010-11-24 14:25 ` Szymon Guz <mabewlun@gmail.com>
2010-11-26 11:28 Re: Deny access materialzsed view Düster Horst <Horst.Duester@bd.so.ch>
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