Обсуждение: Delete denied?

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 BerkusAglio Database SolutionsSan Francisco

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 BerkusAglio Database SolutionsSan Francisco

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

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?

--- 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