Re: Inheritance of foregn key constraints. - Mailing list pgsql-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_1932219323 |   19313 |
RI_ConstraintTrigger_a_1932319324|   19318 | RI_ConstraintTrigger_c_1932419325 |   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 |
1932119323|   19313 | RI_ConstraintTrigger_a_19323 |        1932119324 |   19318 | RI_ConstraintTrigger_c_19324 |
1932119325 |   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)