Row-level permissions?

On 30 December 2010 16:30, gvim <gvimrc@gmail.com> wrote:

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.

gvim

I think the simplest way would be creating a trigger in which you would
check all operations you want to restrict.

regards
Szymon

On Thu, Dec 30, 2010 at 7:30 AM, gvim <gvimrc@gmail.com> wrote:

I want a user to be able to INSERT new rows but not UPDATE or
DELETE rows ...

This part is easily done:
http://www.postgresql.org/docs/9.0/interactive/sql-grant.html
http://www.postgresql.org/docs/9.0/interactive/sql-revoke.html
http://www.postgresql.org/docs/9.0/interactive/sql-createrole.html

... with `id` < 1616.

This cannot be done using ordinary DDL.

--
Regards,
Richard Broersma Jr.

Hey gvim,

2010/12/30 gvim <gvimrc@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
// Dmitriy.