Re: [HACKERS] Inheritance of foregn key constraints - dropping isinternal triggers by a
On Tuesday 01 of April 2014 11:06:00 you wrote: On Tue, Apr 1, 2014 at 9:13 AM, Andrzej Mazurkiewicz andr...@mazurkiewicz.org wrote: 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 Try using a quoted identifier: DROP TRIGGER RI_ConstraintTrigger_c_19322 ON master; Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Good Afternoon. Sorry for my mistake. I have forgotten about modifications of names to lower case. The owner of a table can drop trigger using the command DROP TRIGGER. However, where I can find information on philosophy of that approach. For me internal triggers are marked isinternal not to be accessed using SQL commands. Removing internals, f. e. FK triggers, by hand by a programmer and even by an administrator seems not to be a recommended practice. Kind Regards Andrzej Mazurkiewicz -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Inheritance of foregn key constraints.
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 andr...@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
Re: [HACKERS] Inheritance of foregn key constraints.
On Tue, Apr 1, 2014 at 9:13 AM, Andrzej Mazurkiewicz andr...@mazurkiewicz.org wrote: 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. 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) Try using a quoted identifier: DROP TRIGGER RI_ConstraintTrigger_c_19322 ON master; Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Inheritance of foregn key constraints.
Andrzej Mazurkiewicz andr...@mazurkiewicz.org writes: 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. I'm not sure which part of no you didn't understand, but we're not doing that. lipa= DROP TRIGGER RI_ConstraintTrigger_c_19322 ON master; ERROR: trigger ri_constrainttrigger_c_19322 for table master does not exist This has to do with case-folding and lack of double quotes, not anything more subtle than that. A correct test would've given results like this: regression=# drop trigger RI_ConstraintTrigger_a_43528 on master; ERROR: cannot drop trigger RI_ConstraintTrigger_a_43528 on table master because constraint fk0_detail on table detail requires it HINT: You can drop constraint fk0_detail on table detail instead. which is the behavior we need. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Inheritance of foregn key constraints.
On Tue, Apr 1, 2014 at 8:13 AM, Andrzej Mazurkiewicz andr...@mazurkiewicz.org wrote: 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. Nobody here is going to accept that goal as a valid reason to set the dependency type to the wrong value. The value we assign for the dependency type has important user-visible semantics which we are not going to break for the purpose of making some feature simpler to implement. Of course, PostgreSQL is open source, so you can change your own copy however you like. But such modifications won't be accepted here. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Inheritance of foregn key constraints.
Good Morning. 1. At the beginning some explanations. I am a lazy person that tries not to reinvent a wheel. So I try to use postgres way of automatic processing, i. e. automatic removing dependent objects (which I consider an elegant solution and I really like it). A a result, I have used the pg_depend table to force to remove dependent entries. 2. At the moment the following behavior is a standard one for postgres. - a child table (inheriting form a parent table(s) no FK) can be dropped; - a referred table (master) can be freely dropped with a CASCADE option (causing dropping of the FK); - a referring table (detail) can be freely dropped (causing automatic dropping of the FK); - a CHECK constraint is inherited and the inheritance can be removed freely although leaving the CHECK constraint (no FK); - an inherited table with CHECK constraint can be freely dropped (no FK); - inheritance can be added for existing tables and it can be removed (no FK). 3. The following decisions should be taken for the FK inheritance (partly common issues, however I try to be precise). - (GENERAL statement) Are modifications of a master side hierarchy (a referred side) allowed without dropping the FK? - (GENERAL statement) Are modifications of a detail side hierarchy (a referred side) allowed without dropping the FK? - Is detaching childs allowed in the master and detail hierarchy without dropping the FK? - Is dropping tables allowed in the master and detail hierarchy without dropping the FK? - Is adding inheritance allowed to the master and detail hierarchies without dropping the FK? - Is creating inheriting tables allowed in the master and detail hierarchies without dropping the FK? It would be good if the decisions were consistent with the existing behavior. The consequences of the decisions are rather far going. For large databases adding the FK constraint might last hours or days or perhaps weeks. For my databases, although such modification would last hours and sometimes I have strange and changing ideas - I can live with those hours. Personally I would vote that the above modifications SHOULD BE ALLOWED. Simply, because we do not drop the whole master or detail hierarchy but modify it and it gives certain flexibility to manipulating the schema. The above flexibility is similar to adding inheritance to the existing tables and removing inheritance for them. We do not need to create another inherited table and to move data into it from the existing table. 3. Perhaps , after making the above decisions, a discussion about an implementing changes should be continued. 4. My patch need one change that might be of significance. A type of the depencencies (pg_depend) among the FK constraint (pg_constraint) and the corresponding RI_ConstraintTrigger triggers has to be changed from DEPENDENCY_INTERNAL to DEPENDENCY_AUTO. So in other words, somebody could (accidentally or maliciously) break the constraint by dropping one of its implementation triggers. I doubt that's acceptable. The present postgres behavior ALLOWS accidental or malicious break the constraint by dropping one of its implementation triggers. Please ref. to the following example. The following script has been run by the postgres user. CREATE DATABASE lipa; \c lipa CREATE TABLE master (master_a int, CONSTRAINT pk_master PRIMARY KEY (master_a)); CREATE TABLE detail (master_a int, detail_a int, CONSTRAINT fk0_detail FOREIGN KEY (master_a) REFERENCES master(master_a)); SELECT oid, tgrelid, tgname FROM pg_trigger ; DELETE FROM pg_trigger WHERE oid = (SELECT min(oid) FROM pg_trigger WHERE tgname LIKE 'RI_ConstraintTrigger%' LIMIT 1); SELECT oid, tgrelid, tgname FROM pg_trigger ; DROP TABLE detail; DROP TABLE master; \c postgres DROP DATABASE lipa; The results of the run are as follows. psql -f test-malicious-dropping-FK-triggers.sql postgres CREATE DATABASE You are now connected to database lipa as user postgres. CREATE TABLE CREATE TABLE oid | tgrelid |tgname ---+-+-- 39898 | 39889 | RI_ConstraintTrigger_a_39898 39899 | 39889 | RI_ConstraintTrigger_a_39899 39900 | 39894 | RI_ConstraintTrigger_c_39900 39901 | 39894 | RI_ConstraintTrigger_c_39901 (4 rows) DELETE 1 oid | tgrelid |tgname ---+-+-- 39899 | 39889 | RI_ConstraintTrigger_a_39899 39900 | 39894 | RI_ConstraintTrigger_c_39900 39901 | 39894 | RI_ConstraintTrigger_c_39901 (3 rows) psql:test-malicious-dropping-FK-triggers.sql:8: ERROR: could not find tuple for trigger 39898 psql:test-malicious-dropping-FK-triggers.sql:9: ERROR: could not find tuple for trigger 39898 You are now connected to database postgres as user postgres. DROP DATABASE If this modification is not applied, the detail child table cannot be dropped without prevous dropping the whole FK constraint because the removing operation
Re: [HACKERS] Inheritance of foregn key constraints.
Andrzej Mazurkiewicz andr...@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. The present postgres behavior ALLOWS accidental or malicious break the constraint by dropping one of its implementation triggers. Please ref. to the following example. The following script has been run by the postgres user. Well, right there you lost me, because superusers are exempt from all permissions checks by definition; and in particular, direct manipulations of the system catalogs by superusers are always out of scope for discussions of what the system should try to protect itself against. (Try delete from pg_proc; in a scratch database sometime.) My point is that without the internal dependency, a normal user could do standard SQL commands (ie DROP TRIGGER) and break the FK that way. That's the case that's not acceptable. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Inheritance of foregn key constraints.
Andrzej Mazurkiewicz andr...@mazurkiewicz.org writes: My patch need one change that might be of significance. A type of the depencencies (pg_depend) among the FK constraint (pg_constraint) and the corresponding RI_ConstraintTrigger triggers has to be changed from DEPENDENCY_INTERNAL to DEPENDENCY_AUTO. So in other words, somebody could (accidentally or maliciously) break the constraint by dropping one of its implementation triggers. I doubt that's acceptable. If this modification is not applied, the detail child table cannot be dropped without prevous dropping the whole FK constraint because the removing operation depend on the FK constraint of its parent table. Dropping a child table is going to have much larger problems than that, no? What about the values in the child table --- don't you risk orphaning referencing rows? Or are you only supporting this on the referencing side? In any case, it seems like DROP TABLE could remove the dependency entries for itself, rather than taking the risk of weakening the dependency type. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
