Re: Hot to restrict access to subset of data

[Samuel Thoraval <samuel.thoraval@xxxxxxxxxxxxx>]

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@xxxxxxx> wrote in message 
news:CB2AF562-2A4D-4A9C-BC2A-E55C9029FB56@xxxxxxx...
  

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