Re: Restricting access to rows?

Benjamin Smith <lists(at)benjamindsmith(dot)com> writes:

> 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?

It's not hard to give them access to *view* their records using a view. You
just create the view with WHERE customer_id = .. and then grant SELECT access
to that view but not the underlying table.

In theory that would be enough to give them update access as well. However
Postgres doesn't yet support updateable views, at least not automatically.

You would have to write rules for each view to implement updateable views
which isn't hard but would get pretty tiresome if you're doing this for a lot
of tables and a lot of clients.

There was a project around where someone had implemented some scripts to do
this automatically. You might be able to find it searching back through the
lists.

There are also people interested in working on it as a built-in feature for
Postgres, but I don't think there's any time-line on though or even any
preliminary results yet, so I wouldn't depend on it any time soon.

--
greg