Re: [GENERAL] Row-level permissions?
Hey gvim, 2010/12/30 gvim gvi...@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.
[GENERAL] Row-level permissions?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Row-level permissions?
On 30 December 2010 16:30, gvim gvi...@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
Re: [GENERAL] Row-level permissions?
On Thu, Dec 30, 2010 at 7:30 AM, gvim gvi...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
