Note: This bug is displayed in read-only format because the product is no longer active in Red Hat Bugzilla.

Bug 1198914

Summary: orphan access policy rows
Product: [Retired] Beaker Reporter: Dan Callaghan <dcallagh>
Component: generalAssignee: matt jia <mjia>
Status: CLOSED CURRENTRELEASE QA Contact: tools-bugs <tools-bugs>
Severity: unspecified Docs Contact:
Priority: unspecified    
Version: 19CC: aigao, dcallagh, dowang, ebaak, mjia
Target Milestone: 21.0Keywords: Patch
Target Release: ---   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2015-08-26 06:17:22 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:

Description Dan Callaghan 2015-03-05 05:25:41 UTC 
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
Comment 2 matt jia 2015-05-06 00:31:20 UTC 
The system_id is dropped in 

http://gerrit.beaker-project.org/#/c/4011/

so I donnot think this is an issue any more.
Comment 3 Dan Callaghan 2015-05-06 05:30:40 UTC 
(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);
Comment 7 matt jia 2015-05-19 03:58:46 UTC 
On Gerrit:

  http://gerrit.beaker-project.org/#/c/4217/
Comment 9 matt jia 2015-06-16 00:22:05 UTC 
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
Comment 13 Dan Callaghan 2015-08-11 01:45:10 UTC 
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        ' ()
Comment 14 Dan Callaghan 2015-08-11 02:30:53 UTC 
http://gerrit.beaker-project.org/4338
Comment 17 Dan Callaghan 2015-08-26 06:17:22 UTC 
Beaker 21.0 has been released.