Re: Delete failing with -- permission denied

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(at)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(dot)klaver(at)aklaver(dot)com