From: Andrzej Mazurkiewicz <andrzej@mazurkiewicz.org>
To: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: Inheritance of foregn key constraints.
Date: Tue, 01 Apr 2014 14:13:52 +0200
Message-ID: <5154748.0thC7X2zN4@tata> (raw)
In-Reply-To: <18757.1395501236@sss.pgh.pa.us>
References: <1523734.6NloWteI3S@tata>
<1547752.joDV9Ig9SK@tata>
<18757.1395501236@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Good Afternoon.
Enclosed please find continuation of the discussion of an accidental or
malicious breaking a server consistency.
After reading please comment if there are more objections for changing the
depedency type for trigger to constraint dependency from the
DEPENDENCY_INTERNAL to DEPENDENCY_AUTO.
That change is necessary to reduce scope of modifications necessary for an
implementation of the inheritance of foregn key constraints, particularly for
removing of objects.
Kind Regards
Andrzej Mazurkiewicz
On Saturday 22 of March 2014 11:13:56 you wrote:
> Andrzej Mazurkiewicz <andrzej@mazurkiewicz.org> writes:
> >> So in other words, somebody could (accidentally or maliciously) break the
> >> constraint by dropping one of its implementation triggers. I doubt
> >> that's
> >> acceptable.
I have done some more digging in the subject.
All following tests are perfomed on my patched 9.3 postgres server where the
depedency type for trigger to constraint dependency has been changed to the
DEPENDENCY_AUTOMATIC.
It seems that if the trigger is internal (tgisinternal = true) it is not
visible to the DROP TRIGGER command. So it cannot be deleted using DROP
TRIGGER command, although the dependency type is DEPENDENCY_AUTOMATIC (ref. to
the last SELECT).
Please have a look at the following actions.
Kind regards
Andrzej Mazurkiewicz
They are performed by a lipa user. The lipa user is not a superuser;
postgres=# CREATE USER lipa;
CREATE ROLE
postgres=# CREATE DATABASE lipa OWNER lipa;
CREATE DATABASE
postgres93@tata:~$ psql -W lipa lipa
Password for user lipa:
psql (9.3.3)
Type "help" for help.
lipa=> SELECT CURRENT_USER;
current_user
--------------
lipa
(1 row)
lipa=> CREATE TABLE master (master_a int, CONSTRAINT pk_master PRIMARY KEY
(master_a));
CREATE TABLE
lipa=> CREATE TABLE detail (master_a int, detail_a int, CONSTRAINT fk0_detail
FOREIGN KEY (master_a) REFERENCES master(master_a));
CREATE TABLE
lipa=> SELECT oid, tgrelid, tgname FROM pg_trigger ;
oid | tgrelid | tgname
-------+---------+------------------------------
19322 | 19313 | RI_ConstraintTrigger_a_19322
19323 | 19313 | RI_ConstraintTrigger_a_19323
19324 | 19318 | RI_ConstraintTrigger_c_19324
19325 | 19318 | RI_ConstraintTrigger_c_19325
(4 rows)
lipa=> DROP TRIGGER RI_ConstraintTrigger_c_19322 ON master;
ERROR: trigger "ri_constrainttrigger_c_19322" for table "master" does not
exist
lipa=> DROP TRIGGER RI_ConstraintTrigger_c_19322 ON detail;
ERROR: trigger "ri_constrainttrigger_c_19322" for table "detail" does not
exist
lipa=> SELECT oid, tgrelid, tgname, tgconstraint FROM pg_trigger ;
oid | tgrelid | tgname | tgconstraint
-------+---------+------------------------------+--------------
19322 | 19313 | RI_ConstraintTrigger_a_19322 | 19321
19323 | 19313 | RI_ConstraintTrigger_a_19323 | 19321
19324 | 19318 | RI_ConstraintTrigger_c_19324 | 19321
19325 | 19318 | RI_ConstraintTrigger_c_19325 | 19321
(4 rows)
lipa=> SELECT * FROM pg_depend WHERE refobjid = 19321;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+-------+----------+------------+----------+-------------+---------
2620 | 19322 | 0 | 2606 | 19321 | 0 | a
2620 | 19323 | 0 | 2606 | 19321 | 0 | a
2620 | 19324 | 0 | 2606 | 19321 | 0 | a
2620 | 19325 | 0 | 2606 | 19321 | 0 | a
(4 rows)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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-hackers@postgresql.org
Cc: andrzej@mazurkiewicz.org
Subject: Re: Inheritance of foregn key constraints.
In-Reply-To: <5154748.0thC7X2zN4@tata>
* 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