Re: Row-level permissions?

Hey gvim,

2010/12/30 gvim <gvimrc(at)gmail(dot)com>

> Is it possible, with PostgreSQL 9.0, to restrict access to specific table
> rows by `id`? I want a user to be able to INSERT new rows but not UPDATE or
> DELETE rows with `id` < 1616.
>
I believe that first you need to restrict SELECT. You can do it by creating
view:
CREATE VIEW myview AS SELECT ... FROM mytable ... WHERE id < 1616;

Next, you need define rules on UPDATE and DELETE to the view, e.g:
CREATE RULE myview_rule_upd AS ON UPDATE TO myview
DO INSTEAD
UPDATE mytable SET (column1, column2, ...) = (NEW.column1, NEW.column2,
... );

CREATE RULE myview_rule_ins AS ON DELETE TO myview
...

For details please see "The rule system" chapter of documentation.

> gvim
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
// Dmitriy.