From: Adrian Klaver <adrian.klaver@aklaver.com>
To: anand086 <anand086@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Delete failing with -- permission denied
Date: Mon, 5 Jun 2017 15:14:27 -0700
Message-ID: <5af9dd50-8b18-0562-8cbb-620b1b228759@aklaver.com> (raw)
In-Reply-To: <1496695493471-5964882.post@n3.nabble.com>
References: <1496695493471-5964882.post@n3.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@aklaver.com, anand086@gmail.com
Subject: Re: Delete failing with -- permission denied
In-Reply-To: <5af9dd50-8b18-0562-8cbb-620b1b228759@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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