Re: Restricting access to rows?

Josue E. Maldonado wrote:

> Benjamin Smith wrote:
>
>> How can I set up a user so that Bob can update his records, without
>> letting Bob update Jane's records? Is it possible, say with a view or
>> some other intermediate data type?
>
>
> I've done something similar using a separate control table where I set
> what accounts an user can "see", then I wrote a psql that returns just
> the rows for that especific user, it could also be done with pure SQL
> joins tough.
>
>
>
You can put in a some triggers that do a few things, and I think a rule
on SELECT will round it off.

on Insert: populate a column with CURRENT_USER
on Update and Delete: refuse unless CURRENT_USER matches the column
on SELECT rules, apply a filter that column = CURRENT_USER

You also may put in an override for all three that if the CURRENT_USER
is in some particular group these filters will not apply.  One level
might be just for selects, a higher level for updates/deletes.

Or you can do the reverse, and say that these filters only apply if the
user is in a certain group.