pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDelete denied?
5+ messages / 4 participants
[nested] [flat]
* Delete denied?
@ 2003-08-22 23:24 Josh Berkus <josh@agliodbs.com>
0 siblings, 3 replies; 5+ messages in thread
From: Josh Berkus @ 2003-08-22 23:24 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
Stephan, Tom:
Hey, I have a function which can involve some records being deleted at the
end. The user calling the function has permission to delete records (and
I've tested this), but when I run the function I get "permission denied".
Any clue? I remember in 7.1.x that you couldn't delete records in a function,
but this is on 7.2.4.
Contributing could be that the table holding the deleted records is a child
table with an "ON CASCADE DELETE" of one of the tables being updated
elsewhere in the function. And the function works if called by the owner of
the table (and the function).
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Delete denied?
@ 2003-08-22 23:48 Josh Berkus <josh@agliodbs.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2003-08-22 23:48 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
Guys:
> Contributing could be that the table holding the deleted records is a child
> table with an "ON CASCADE DELETE" of one of the tables being updated
> elsewhere in the function. And the function works if called by the owner of
> the table (and the function).
Further update: I tried changing the owner of the function and table to no
avail. Also tried dropping the FK. It seems to work if called by the
owner of the database, but not otherwise.
I'm really baffled ... I've written several hundred procedures for 7.2.4, and
have never seen anything like this.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Delete denied?
@ 2003-08-22 23:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2003-08-22 23:55 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
Josh Berkus <josh@agliodbs.com> writes:
> Contributing could be that the table holding the deleted records is a child
> table with an "ON CASCADE DELETE" of one of the tables being updated
> elsewhere in the function. And the function works if called by the owner of
> the table (and the function).
I seem to recall that the original coding of the RI triggers was
careless about executing the RI operations as the "right" user (namely
the table owner). This very possibly was still broken in 7.2.4.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Delete denied?
@ 2003-08-23 01:22 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2003-08-23 01:22 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
On Fri, 22 Aug 2003, Josh Berkus wrote:
> Guys:
>
> > Contributing could be that the table holding the deleted records is a child
> > table with an "ON CASCADE DELETE" of one of the tables being updated
> > elsewhere in the function. And the function works if called by the owner of
> > the table (and the function).
>
> Further update: I tried changing the owner of the function and table to no
> avail. Also tried dropping the FK. It seems to work if called by the
> owner of the database, but not otherwise.
It still failed after you dropped the FK (I'd have otherwise agreed with
Tom that there were some cases that I think were broken as recently as
7.3.x for some value of x on permissions)? How much of the definitions
can you let us see?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Delete denied?
@ 2003-08-23 16:55 Jeff Eckermann <jeff_eckermann@yahoo.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 5+ messages in thread
From: Jeff Eckermann @ 2003-08-23 16:55 UTC (permalink / raw)
To: josh@agliodbs.com; pgsql-sql
--- Josh Berkus <josh@agliodbs.com> wrote:
> Stephan, Tom:
>
> Hey, I have a function which can involve some
> records being deleted at the
> end. The user calling the function has permission
> to delete records (and
> I've tested this), but when I run the function I get
> "permission denied".
>
I had a similar weird problem recently, also with
7.2.4. Whether the two problems are related I can't
tell, but...
Situation: user a updates table1 (owned by a), kicks
off a trigger which updates table2 (owned by user b).
Wouldn't work. I tried every ownership and permission
combination that I could think of without success,
before succeeding by doing a "grant all on table2 to
a".
Since the table access is all mediated through the
application I was not worried about the "all", but the
question remains: why would "grant all" permit
updates, when "grant update" would not?
> Any clue? I remember in 7.1.x that you couldn't
> delete records in a function,
> but this is on 7.2.4.
>
> Contributing could be that the table holding the
> deleted records is a child
> table with an "ON CASCADE DELETE" of one of the
> tables being updated
> elsewhere in the function. And the function works
> if called by the owner of
> the table (and the function).
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-08-23 16:55 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-22 23:24 Delete denied? Josh Berkus <josh@agliodbs.com>
2003-08-22 23:48 ` Josh Berkus <josh@agliodbs.com>
2003-08-23 01:22 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-22 23:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-23 16:55 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
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