apparently revoking update rights on referencing table blocks deletes on master table:
(user test, database test, user test is not superuser)
> create table a (id serial primary key, x text);
NOTICE: CREATE TABLE will create implicit sequence "a_id_seq" for serial column "a.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a"
CREATE TABLE
> create table b (id serial primary key, a_id int4 references a(id) );
NOTICE: CREATE TABLE will create implicit sequence "b_id_seq" for serial column "b.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "b_pkey" for table "b"
CREATE TABLE
> insert into a (id) values (1);
INSERT 0 1
> insert into b (a_id) values (1);
INSERT 0 1
> revoke update on b from test;
REVOKE
> delete from a where id = 1;
ERROR: permission denied for relation b
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."b" x WHERE $1 OPERATOR(pg_catalog.=) "a_id" FOR SHARE OF x"
now, i know that the sql shown in context comes from foreign key trigger, but is definitely not obvious for everybody,
somaybe the message should be cleared
a bit in this case.
on the other hand - i'm not really sure that update rights should be neccessary in such case.
best regards,
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)