From: JanWieck@t-online.de
To: Jon Lapham <jlapham@gandalf.bioqmed.ufrj.br>
Cc: Stephan Szabo <sszabo@kick.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Why do I need to set UPDATE permissions for fkey check?
Date: Sat, 22 Jul 2000 11:01:05 +0200 (MEST)
Message-ID: <200007220901.LAA08299@hot.jw.home> (raw)
In-Reply-To: <20000721183900.A28484@gandalf.bioqmed.ufrj.br>
Jon Lapham wrote:
> On Fri, Jul 21, 2000 at 02:00:00PM -0700, Stephan Szabo wrote:
> >
> > It's a known problem in the foreign key code. The reason is that
> > the fk triggers use SELECT FOR UPDATE to select the matching
> > rows that it is checking and the reason for using FOR UPDATE is
> > to lock those rows so that someone cannot delete/change them out
> > from under your nose while you're looking at them. However,
> > SELECT FOR UPDATE is asking for update permissions because it
> > grabs that row lock.
>
> Oh, okay, I understand your explanation, and it fits with what I am
> seeing.
>
> But...
>
> ...this is a READ ONLY table! Maybe it would be possible to have the fkey
> triggers look to see if the table is read-only, and then simply use SELECT
> instead of SELECT FOR UPDATE and then not perform the row locking? Since
> this is a read-only table, there would be no risk of deleting/changing any
> of the data. Yeah, I realize that with this solution, you cannot
> guarantee that the table doesn't become 'writable' sometime during the
> fkey lookup.
The problem only exists for concurrent access. If the rows
don't get locked, any user with write permissions could
delete a row where another one actually inserts a reference
for. And you cannot take write permissions away from
superusers. This would violate the constraint "silently",
because the "check" on the fkey table is already done, but
the insert not yet committed, while the "referential action"
on the pkey table saw no references and permits deletion.
> It would seem to me that this is a serious problem. I absolutely cannot
> have my data table be writable, and I need to maintain fkey integrity.
> Urg.... this is very bad, the fkey integrity check is the reason I
> installed Pg v7. I would think that keeping read-only static data table
> would be a common database occurance, any suggestions on how to get around
> this issue? Possibly with a (gulp) permissions switching trigger (gulp)?
It is a serious problem, indeed.
I'll post a proposal to fix it for 7.1 in a separate message.
I have something in mind so far, but need to play around with
the code before knowing all the odds and ends.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: JanWieck@t-online.de, jlapham@gandalf.bioqmed.ufrj.br, sszabo@kick.com
Subject: Re: Why do I need to set UPDATE permissions for fkey check?
In-Reply-To: <200007220901.LAA08299@hot.jw.home>
References: <20000721183900.A28484@gandalf.bioqmed.ufrj.br> <200007220901.LAA08299@hot.jw.home>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox