Skip site navigation (1) Skip section navigation (2)

bad message or bad privilege check in foreign key constraint

From: hubert depesz lubaczewski <depesz(at)depesz(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: bad message or bad privilege check in foreign key constraint
Date: 2008-01-22 08:59:28
Message-ID: 20080122085928.GA31199@depesz.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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, so maybe 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)

Responses

pgsql-bugs by date

Next:From: Michael AkindeDate: 2008-01-22 10:16:51
Subject: Re: BUG #3881: lo_open leaks memory
Previous:From: ykhuangDate: 2008-01-22 05:55:34
Subject: Re: why provide cross type arithmetic operators

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group