Description of problem: system_access_policy.system_id is NULLable, and there are no suitable cascade rules, so it is possible to have orphaned systemd_access_policy rows (not belonging to any system). Most likely they are left behind when deleting a system with bkr system-delete, need to confirm. Version-Release number of selected component (if applicable): 19.2
The system_id is dropped in http://gerrit.beaker-project.org/#/c/4011/ so I donnot think this is an issue any more.
(In reply to matt jia from comment #2) The orphans still exist, just the query to find them is a bit more complicated. select * from system_access_policy where not exists (select 1 from system where custom_access_policy_id = system_access_policy.id) and not exists (select 1 from system_pool where access_policy_id = system_access_policy.id);
On Gerrit: http://gerrit.beaker-project.org/#/c/4217/
Verified steps: 1. create an system 2. delete this system with bkr system-delete 3. check if the associated record in system_access_policy is deleted
The migration for deleting old policies is not quite correct, it doesn't work when the policies actually exist with rules in them. sqlalchemy.exc.IntegrityError: (IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`beaker`.`system_access_policy_rule`, CONSTRAINT `system_access_policy_rule_policy_id_fk` FOREIGN KEY (`policy_id`) REFERENCES `system_access_policy` (`id`))') '\n DELETE FROM system_access_policy WHERE\n NOT EXISTS (SELECT 1 FROM system WHERE custom_access_policy_id =\n system_access_policy.id) AND NOT EXISTS(SELECT 1 FROM system_pool\n WHERE access_policy_id = system_access_policy.id)\n ' ()
http://gerrit.beaker-project.org/4338
Beaker 21.0 has been released.