From: Szymon Guz <mabewlun@gmail.com>
To: Düster Horst <Horst.Duester@bd.so.ch>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Deny access materialzsed view
Date: Wed, 24 Nov 2010 15:25:48 +0100
Message-ID: <AANLkTimeJ1CWkr_85eud1qqbMuEJkLPkt_+O_rxnWBW7@mail.gmail.com> (raw)
In-Reply-To: <H00002eb08444904.1290607016.srsofaioi6145.ktso.ch@MHS>
References: <H00002eb08444904.1290607016.srsofaioi6145.ktso.ch@MHS>
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: mabewlun@gmail.com, Horst.Duester@bd.so.ch
Subject: Re: Deny access materialzsed view
In-Reply-To: <AANLkTimeJ1CWkr_85eud1qqbMuEJkLPkt_+O_rxnWBW7@mail.gmail.com>
* 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