Re: referential integrity requires write permission to a table which only needs to be read

On Wed, 30 Aug 2000 pgsql-bugs(at)postgresql(dot)org wrote:

> Short Description
> referential integrity requires write permission to a table which only needs to be read

Yes, this is a known problem.

> And, why is the trigger trying to select WITH UPDATE? (locking?)
Yes, if you don't grab a row lock, another transaction can come
along and delete the row you're referencing after you've checked
it, but before that transaction sees that you've added a referencing
row (which could lead to orphened children)

> In any case, this is requiring us to grant update permission to this
> user group, and we really only want them to have select permission to
> the table in question.

Right now, the suggested workaround is to do a trigger before updates
that prevents the unwanted writes in plpgsql. Not particularly good,
but functional until this gets fixed (and I don't know precisely when
that'll be)