Thread: [SQL] Delete failing with -- permission denied
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 ############################# Table test.entities_all has below ############################# Referenced by: TABLE "test.entities_all" CONSTRAINT "entities_all_fk2" FOREIGN KEY (owner_id) REFERENCES test.entities_all(entity_id) ON DELETE SET NULL TABLE "test.attribute_types" CONSTRAINT "fkey_at_attribute_entity" FOREIGN KEY (attribute_type_entity_id) REFERENCES test.entities_all(entity_id) ON DELETE CASCADE TABLE "test.requests" CONSTRAINT "requests_fk2" FOREIGN KEY (requester_entity_id) REFERENCES test.entities_all(entity_id) ON DELETE CASCADE ####################################### Table test.attribute_types has ####################################### Referenced by: TABLE "test.attributes" CONSTRAINT "fkey_attribute_type" FOREIGN KEY (attribute_type_id) REFERENCES test.attribute_types(attribute_type_id) ############################### Deleting each row manually ############################### user_test@testdbpg #begin; BEGIN Time: 21.895 ms user_test@testdbpg # delete from test.attributes where (attribute_type_id)=(1220010); DELETE 1 Time: 24.145 ms user_test@testdbpg # DELETE FROM ONLY "test"."attribute_types" where (attribute_type_id)=(1220010); DELETE 1 Time: 35.638 ms user_test@testdbpg # delete from test.entities_all where ( entity_type_id = 254::INTEGER AND entity_id = 20043093223::NUMERIC); DELETE 1 Time: 56.104 ms user_test@testdbpg # rollback; ROLLBACK Time: 28.266 ms user_test@testdbpg # ############################### Function ############################## CREATE OR REPLACE FUNCTION test."attribute_types_history$attribute_types"()RETURNS triggerLANGUAGE plpgsql AS $function$ DECLARE new_change_num numeric; BEGIN PERFORM test.changes_package$init(); new_change_num := test.changes_package$get_change_num(); INSERT INTO test.attribute_types_history(attribute_type_id, for_entity_type_id, attribute_name, attribute_description, attribute_type_entity_id, last_modified_by, last_modified_date, creation_change_num, deletion_change_num) VALUES (OLD.attribute_type_id, OLD.for_entity_type_id, OLD.attribute_name, OLD.attribute_description, OLD.attribute_type_entity_id, OLD.last_modified_by, OLD.last_modified_date, OLD.creation_change_num, new_change_num); RETURN OLD; END; $function$ ###################################################### Able to select from changes_package$get_change_num ###################################################### user_test@testdbpg # select * from test.changes_package$get_change_num(); +--------------------------------+ | changes_package$get_change_num | +--------------------------------+ | 100759111 | +--------------------------------+ (1 row) Time: 27.829 ms -- View this message in context: http://www.postgresql-archive.org/Delete-failing-with-permission-denied-tp5964882.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
anand086 <anand086@gmail.com> writes:
> 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.
Probably you have permissions to do such deletion, but the owner of the
entities_all table does not. FK CASCADE queries are run as the owner
of the table in question.
regards, tom lane
Thank you Tom for your quick reply. "FK CASCADE queries are run as the owner of the table in question." All the table belong to TEST and application user is user_test. Below grants were granted to "user_test" GRANT ALL PRIVILEGES ON ALL TABLES IN schema "test" TO user_test; grant usage on schema "test" to user_test; What would be needed to rectify the issue? -- View this message in context: http://www.postgresql-archive.org/Delete-failing-with-permission-denied-tp5964882p5964888.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
What would be needed to rectify the issue?
IMO you need to supply a self-contained script that goes from nothing to problem. I say this particularly since the only place the relation name "current_change$tmp" appears in your email is in the error message.
David J.
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