Subject: Re: Delete failing with -- permission denied
To: anand086 <anand086@gmail.com>, pgsql-sql@postgresql.org
References: <1496695493471-5964882.post@n3.nabble.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <5af9dd50-8b18-0562-8cbb-620b1b228759@aklaver.com>
Date: Mon, 5 Jun 2017 15:14:27 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.1.1
MIME-Version: 1.0
In-Reply-To: <1496695493471-5964882.post@n3.nabble.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On 06/05/2017 01:44 PM, anand086 wrote:
> Delete from table test.entities_all is failing with "permission denied for
> relation". The table from which row has to be deleted, is referenced by
> another table "attribute_types" with ON DELETE CASCADE.
> 
> I tried deleting the row from attribute_types table and then deleting from
> test.entities_all succeed.
> 
> I am not able to understand why this delete sql is failing.
> 
> ######################
> Delete from table is failing with
> ######################
> 
> user_test@testdbpg # delete from test.entities_all where entity_type_id =
> 254 AND entity_id = 20043093223;
> ERROR:  permission denied for relation current_change$tmp
> CONTEXT:  PL/pgSQL function test.current_change() line 11 at RETURN QUERY
> SQL statement "SELECT
>          change_num
>                                             FROM test.current_change"
> PL/pgSQL function test."changes_package$get_change_num"() line 5 at SQL
> statement
> PL/pgSQL function test."attribute_types_history$attribute_types"() line 6 at
> assignment
> SQL statement "DELETE FROM ONLY "test"."attribute_types" WHERE $1
> OPERATOR(pg_catalog.=) "attribute_type_entity_id""
> Time: 65.536 ms

You seem to have a chain of triggers/functions on this table. It would 
be nice to see how those cascade. In particular from above:

PL/pgSQL function test."attribute_types_history$attribute_types"() line 6 at
assignment
SQL statement "DELETE FROM ONLY "test"."attribute_types" WHERE $1
OPERATOR(pg_catalog.=) "attribute_type_entity_id""

which leads me to believe this is the problem:

CREATE OR REPLACE FUNCTION test."attribute_types_history$attribute_types"()

...

new_change_num := test.changes_package$get_change_num();

...

which then leads to what is in test.changes_package$get_change_num(), 
though I suspect it includes:

SQL statement "DELETE FROM ONLY "test"."attribute_types" WHERE $1
OPERATOR(pg_catalog.=) "attribute_type_entity_id""

Also would be nice to know what user(s) the functions are running as?


-- 
Adrian Klaver
adrian.klaver@aklaver.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql