pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stephan Szabo <sszabo@kick.com>
To: Jon Lapham <jlapham@gandalf.bioqmed.ufrj.br>
Cc: pgsql-sql@postgresql.org
Subject: Re: Why do I need to set UPDATE permissions for fkey check?
Date: Fri, 21 Jul 2000 14:00:00 -0700
Message-ID: <01f801bff356$a2ef6c00$0c64010a@kick.com> (raw)
References: <20000721163616.A27873@gandalf.bioqmed.ufrj.br>


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.
There's still some question of how to get around this.  A normal
select is insufficient.  Although not a complete solution, setuid triggers
would help (requiring only that the trigger owner had update permissions
not the rest of the users), but I'm not sure when/if this would get done.

----- Original Message -----
From: "Jon Lapham" <jlapham@gandalf.bioqmed.ufrj.br>
To: <pgsql-sql@postgresql.org>
Sent: Friday, July 21, 2000 12:36 PM
Subject: [SQL] Why do I need to set UPDATE permissions for fkey check?


> Hello all-
>
> Running: Pg v7.0.2, home rolled, RedHat 6.2 linux.
>
> I am trying to set up a read-only static lookup table, to which other
> tables will reference.  However, it seems I need to GRANT SELECT, UPDATE
> permissions (at least) on the lookup table in order to perform foreign key
> integrity checking.  This seems strange to me, any ideas as to
> why?  After filling with data, nothing ever gets updated in this
> table!  Any suggestions on how I could set up a read-only lookup table
> that is involved in foreign key integrity checking?
>
> Thanks!  -Jon
>
> Here is the simplest example I could create:
>
> #######################################################
> -- Create a read-only static lookup table
> CREATE TABLE lookup ( id int, value text );
> INSERT INTO  lookup (id, value) VALUES (1,'hello');
> INSERT INTO  lookup (id, value) VALUES (2,'world');
> REVOKE ALL ON lookup FROM PUBLIC;
> GRANT SELECT ON lookup TO PUBLIC;
>
> -- Create the read/write dynamic work table
> CREATE TABLE work ( info int references lookup (id) );
> REVOKE ALL ON work FROM PUBLIC;
> GRANT ALL ON work TO PUBLIC;
> #######################################################
>
> Now, if I attempt to insert something into the 'work' table:
>
> template1=> \z
> Access permissions for database "template1"
>  Relation | Access permissions
> ----------+--------------------
>  lookup   | {"=r"}
>  work     | {"=arwR"}
>
> template1=> INSERT INTO work (info) VALUES (1);
> ERROR:  lookup: Permission denied.
>
> #######################################################
>
> But:
> template1=> GRANT UPDATE ON lookup TO PUBLIC;
> CHANGE
> template1=> \z
> Access permissions for database "template1"
>  Relation | Access permissions
> ----------+--------------------
>  lookup   | {"=rw"}
>  work     | {"=arwR"}
>
> template1=> INSERT INTO work (info) VALUES (1);
> INSERT 331226 1





view thread (4+ messages)  latest in thread

Message-ID: <01f801bff356$a2ef6c00$0c64010a@kick.com>
Permalink:  ../01f801bff356$a2ef6c00$0c64010a@kick.com/
Also on:    postgresql.org/message-id/01f801bff356$a2ef6c00$0c64010a@kick.com

 · 

reply

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: sszabo@kick.com, jlapham@gandalf.bioqmed.ufrj.br
  Subject: Re: Why do I need to set UPDATE permissions for fkey check?
  In-Reply-To: <01f801bff356$a2ef6c00$0c64010a@kick.com>
  References: <20000721163616.A27873@gandalf.bioqmed.ufrj.br> <01f801bff356$a2ef6c00$0c64010a@kick.com>

* 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