Re: Hot to restrict access to subset of data

I have been trying to use views to restrict access to a subset of data
as stated :

Using Andrus's example for user B with document in public schema :

REVOKE ALL FROM public.document;

CREATE SCHEMA b AUTHORIZATION b;
CREATE VIEW b.document AS SELECT * FROM public.document WHERE
DocumentType = 'Z';
GRANT SELECT ON b.document TO b;

This way when user B connects, with its search_path variable properly
set, he will see datas from view b.document instead of from table
public.document.

But let's say we also want user B being able to update VIEW b.document ?
Then we'd have to grant UPDATE privilege and define a RULE :

-- GRANT UPDATE ON b.document TO b; let's try without it
CREATE RULE document_b AS ON UPDATE TO b.document DO INSTEAD
UPDATE public.document set bla bla bla where bla bla bla...

I have been trying this example not executing the GRANT UPDATE statement
at first to check that user b doesn't have the right to update. The
problem is that even though B was not granted the update privilege, it
worked anyway. In other words, simply executing " GRANT SELECT ON
b.document TO b;" is sufficient for user b to be able to update the
view, and thus the public.document table for DocumentType = Z.

Anybody has an explanation to this ?

Sam

Andrus Moor a écrit :

>Greg,
>
>using views would be nice.
>
>I have also a add privilege which allows to add only new documents. I think
>that this requires writing triggers in Postgres.
>
>This seems to be a lot of work.
>I do'nt have enough knowledge to implement this in Postgres.
>
>So it seems to more reasonable to run my application as Postgres superuser
>and implement security in application.
>
>Andrus.
>
>"Gregory Youngblood" <gsyoungblood(at)mac(dot)com> wrote in message
>news:CB2AF562-2A4D-4A9C-BC2A-E55C9029FB56(at)mac(dot)com(dot)(dot)(dot)
>
>
>>I believe you can probably use views to accomplish this.
>>
>>You create a view that is populated based on their username. Then you
>>remove access to the actual table, and grant access to the view.
>>
>>When people look at the table, they will only see the data in the view
>>and will not have access to the other.
>>
>>Of course, this assumes they do not need to update the data. I've not
>>played around with rules to make a view allow updates. I believe it is
>>possible, I've just not done it yet. This also assumes you have data
>>somewhere that maps user names to document types.
>>
>>The postgresql docs should provide the syntax and additional details if
>>you want to try this. I have also found pgAdmin very useful to create
>>views and other schema related activities as well.
>>
>>Hope this helps,
>>Greg
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 4: Don't 'kill -9' the postmaster
>>
>>
>>
>
>
>
>
>