Bug 1198914

Summary:	orphan access policy rows
Product:	[Retired] Beaker	Reporter:	Dan Callaghan <dcallagh>
Component:	general	Assignee:	matt jia <mjia>
Status:	CLOSED CURRENTRELEASE	QA Contact:	tools-bugs <tools-bugs>
Severity:	unspecified	Docs Contact:
Priority:	unspecified
Version:	19	CC:	aigao, dcallagh, dowang, ebaak, mjia
Target Milestone:	21.0	Keywords:	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.