agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedDelete failing with -- permission denied
5+ messages / 4 participants
[nested] [flat]
* Delete failing with -- permission denied
@ 2017-06-05 20:44 anand086 <anand086@gmail.com>
2017-06-05 21:15 ` Re: Delete failing with -- permission denied Tom Lane <tgl@sss.pgh.pa.us>
2017-06-05 22:14 ` Re: Delete failing with -- permission denied Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 5+ messages in thread
From: anand086 @ 2017-06-05 20:44 UTC (permalink / raw)
To: pgsql-sql
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 trigger
LANGUAGE 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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Delete failing with -- permission denied
2017-06-05 20:44 Delete failing with -- permission denied anand086 <anand086@gmail.com>
@ 2017-06-05 21:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-06-05 21:34 ` Re: Delete failing with -- permission denied anand086 <anand086@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2017-06-05 21:15 UTC (permalink / raw)
To: anand086 <anand086@gmail.com>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Delete failing with -- permission denied
2017-06-05 20:44 Delete failing with -- permission denied anand086 <anand086@gmail.com>
2017-06-05 21:15 ` Re: Delete failing with -- permission denied Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-06-05 21:34 ` anand086 <anand086@gmail.com>
2017-06-05 21:43 ` Re: Delete failing with -- permission denied David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: anand086 @ 2017-06-05 21:34 UTC (permalink / raw)
To: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Delete failing with -- permission denied
2017-06-05 20:44 Delete failing with -- permission denied anand086 <anand086@gmail.com>
2017-06-05 21:15 ` Re: Delete failing with -- permission denied Tom Lane <tgl@sss.pgh.pa.us>
2017-06-05 21:34 ` Re: Delete failing with -- permission denied anand086 <anand086@gmail.com>
@ 2017-06-05 21:43 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2017-06-05 21:43 UTC (permalink / raw)
To: anand086 <anand086@gmail.com>; +Cc: pgsql-sql
On Mon, Jun 5, 2017 at 2:34 PM, anand086 <anand086@gmail.com> wrote:
> 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Delete failing with -- permission denied
2017-06-05 20:44 Delete failing with -- permission denied anand086 <anand086@gmail.com>
@ 2017-06-05 22:14 ` Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 0 replies; 5+ messages in thread
From: Adrian Klaver @ 2017-06-05 22:14 UTC (permalink / raw)
To: anand086 <anand086@gmail.com>; pgsql-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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2017-06-05 22:14 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-06-05 20:44 Delete failing with -- permission denied anand086 <anand086@gmail.com>
2017-06-05 21:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-06-05 21:34 ` anand086 <anand086@gmail.com>
2017-06-05 21:43 ` David G. Johnston <david.g.johnston@gmail.com>
2017-06-05 22:14 ` Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox