Обсуждение: PostgreSQL BugTool Submission
NAGY Andras (nagya@inf.elte.hu) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description Foreign keys referencing read-only tables fail Long Description Consider two tables, foo(barid) and bar(id, str), where foo has a foreign key referencing bar(id). It is expected that auser having only read access to bar and read/write to foo will be able to do insertions in foo. However, it is not thecase. Postgresql 7.0.2 (from debian woody) on debian gnu/linux 2.2 (potato). Sample Code create table bar(id int primary key, str text); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'bar_pkey' for table 'bar' CREATE insert into bar values(0, 'zero'); INSERT 139693 1 insert into bar values(1, 'one'); INSERT 139694 1 insert into bar values(2, 'two'); INSERT 139695 1 revoke all on bar from public; CHANGE grant select on bar to nagya; CHANGE select * from bar; id | str ----+------ 0 | zero 1 | one 2 | two (3 rows) create table foo (barid int references bar(id)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE insert into foo values(0); ERROR: bar: Permission denied. insert into foo values(5); ERROR: bar: Permission denied. ----------- postgres log for the insert command: 000823.20:52:18.170 [22793] StartTransactionCommand 000823.20:52:18.170 [22793] query: insert into foo values(0); 000823.20:52:18.172 [22793] ProcessQuery 000823.20:52:18.177 [22793] query: SELECT oid FROM "bar" WHERE "id" = $1 FOR UPDATE OF "bar" 000823.20:52:18.186 [22793] ERROR: bar: Permission denied. 000823.20:52:18.187 [22793] AbortCurrentTransaction No file was uploaded with this report
Yes, this is a known issue due to the fact that the triggers use SPI and need to use SELECT ... FOR UPDATE to lock the rows it is reading (and select for update requires the update permission). The workaround I know about for now is to give update permission and make triggers to disallow updates as appropriate. (If this isn't in the TODO list or FAQ yet, it probably should be.) Stephan Szabo sszabo@bigpanda.com On Wed, 23 Aug 2000 pgsql-bugs@postgresql.org wrote: > NAGY Andras (nagya@inf.elte.hu) reports a bug with a severity of 3 > The lower the number the more severe it is. > > Short Description > Foreign keys referencing read-only tables fail
Can someone give me a good description of this for TODO? > > Yes, this is a known issue due to the fact that the > triggers use SPI and need to use SELECT ... FOR UPDATE > to lock the rows it is reading (and select for update > requires the update permission). The workaround I > know about for now is to give update permission and make > triggers to disallow updates as appropriate. > > (If this isn't in the TODO list or FAQ yet, it probably > should be.) > > Stephan Szabo > sszabo@bigpanda.com > > On Wed, 23 Aug 2000 pgsql-bugs@postgresql.org wrote: > > > NAGY Andras (nagya@inf.elte.hu) reports a bug with a severity of 3 > > The lower the number the more severe it is. > > > > Short Description > > Foreign keys referencing read-only tables fail > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Actually Peter did a patch for this fairly recently I believe. I haven't grabbed CVS recently enough to know if it got committed. There's a related question of what permissions you need to follow referential actions (currently it's the same permission as if you were doing the implied statement). Stephan Szabo sszabo@bigpanda.com On Sat, 14 Oct 2000, Bruce Momjian wrote: > Can someone give me a good description of this for TODO? > > > > > Yes, this is a known issue due to the fact that the > > triggers use SPI and need to use SELECT ... FOR UPDATE > > to lock the rows it is reading (and select for update > > requires the update permission). The workaround I > > know about for now is to give update permission and make > > triggers to disallow updates as appropriate. > > > > (If this isn't in the TODO list or FAQ yet, it probably > > should be.) > > > > Stephan Szabo > > sszabo@bigpanda.com > > > > On Wed, 23 Aug 2000 pgsql-bugs@postgresql.org wrote: > > > > > NAGY Andras (nagya@inf.elte.hu) reports a bug with a severity of 3 > > > The lower the number the more severe it is. > > > > > > Short Description > > > Foreign keys referencing read-only tables fail > > > > > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 >
Oh, OK. I will forget it. > > Actually Peter did a patch for this fairly recently I > believe. I haven't grabbed CVS recently enough to know > if it got committed. There's a related question of what > permissions you need to follow referential actions (currently > it's the same permission as if you were doing the implied > statement). > > Stephan Szabo > sszabo@bigpanda.com > > On Sat, 14 Oct 2000, Bruce Momjian wrote: > > > Can someone give me a good description of this for TODO? > > > > > > > > Yes, this is a known issue due to the fact that the > > > triggers use SPI and need to use SELECT ... FOR UPDATE > > > to lock the rows it is reading (and select for update > > > requires the update permission). The workaround I > > > know about for now is to give update permission and make > > > triggers to disallow updates as appropriate. > > > > > > (If this isn't in the TODO list or FAQ yet, it probably > > > should be.) > > > > > > Stephan Szabo > > > sszabo@bigpanda.com > > > > > > On Wed, 23 Aug 2000 pgsql-bugs@postgresql.org wrote: > > > > > > > NAGY Andras (nagya@inf.elte.hu) reports a bug with a severity of 3 > > > > The lower the number the more severe it is. > > > > > > > > Short Description > > > > Foreign keys referencing read-only tables fail > > > > > > > > > > > > -- > > Bruce Momjian | http://candle.pha.pa.us > > pgman@candle.pha.pa.us | (610) 853-3000 > > + If your life is a hard drive, | 830 Blythe Avenue > > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Well, actually the question of whether failing referential actions due to permission deficits of the user doing the delete/update on the pk table is a bug or feature still stands. It would be fairly trivial to extend Peter's patch to effectively setuid on the actions, but the question is whether or not that's useful and correct. Stephan Szabo sszabo@bigpanda.com On Sat, 14 Oct 2000, Bruce Momjian wrote: > Oh, OK. I will forget it. > > > > > Actually Peter did a patch for this fairly recently I > > believe. I haven't grabbed CVS recently enough to know > > if it got committed. There's a related question of what > > permissions you need to follow referential actions (currently > > it's the same permission as if you were doing the implied > > statement).
At 23:20 14/10/00 -0700, Stephan Szabo wrote:
>
>Well, actually the question of whether failing referential actions
>due to permission deficits of the user doing the delete/update
>on the pk table is a bug or feature still stands. It would be
>fairly trivial to extend Peter's patch to effectively setuid on
>the actions, but the question is whether or not that's useful and
>correct.
>
I haven't looked at the standard yet, but maybe the correct behaviour
should be to setuid to the creator of the constraint for the purpose of the
checks.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/