Hostgroup deletion does not remove hostgroup references from policy in Red Hat Satellite

Environment

• Red Hat Satellite 6

Issue

• When trying to list the policies or get details for a policy, receive hammer error.

hammer> policy update --name 'SCAP-policy-test2'
Could not update the policy:
  Couldn't find Hostgroup with 'id'=5
hammer> policy list
Couldn't find all Hostgroups with 'id': (8, 2, 5, 3) (found 3 results, but was looking for 4)

Resolution

The This content is not included.underlying bugzilla has been closed as WONTFIX / not expected to be implemented in the product in the foreseeable future.

As a workaround to not working policy update --name .. command: use policy update --id .. instead. To find ID of a Policy, run e.g.:

su - postgres -c "psql foreman -c \"COPY (SELECT id FROM foreman_openscap_policies WHERE name = 'POLICYNAME') TO STDOUT\""

• As a generic remedy workaround, remove the orphaned references of deleted hostgroups

echo "ForemanOpenscap::Asset.where(:assetable_type => 'Hostgroup').where.not(:assetable_id => Hostgroup.all.pluck(:id)).destroy_all" | foreman-rake console

The above will remove the key orphaned records while some others will remain in DB. These remaining ones don't impact work with SCAP functionality. Just in case one wishes to remove also these "safe! orphans, let 1) backup the Satellite before direct psql deletion operations, and then execute inside su - postgres -c "psql foreman"`:

• Cleanup invalid asset(hostgroup) from foreman_openscap_asset_policies:

  foreman=# DELETE FROM foreman_openscap_asset_policies WHERE asset_id IN (SELECT foa.id FROM foreman_openscap_assets AS foa LEFT JOIN hostgroups AS h ON foa.assetable_id = h.id WHERE h.id IS NULL AND assetable_type = 'Hostgroup');
  

• Cleanup invalid hostgroup entry from foreman_openscap_assets (should be already done by the rake command):

  foreman=# DELETE FROM foreman_openscap_assets WHERE id IN (SELECT foa.id FROM foreman_openscap_assets AS foa LEFT JOIN hostgroups AS h ON foa.assetable_id = h.id WHERE h.id IS NULL AND assetable_type = 'Hostgroup');
  

• Clean foreman_openscap_asset_policies when asset_id does not exist in foreman_openscap_assets:

  foreman=# DELETE FROM foreman_openscap_asset_policies WHERE asset_id IN (SELECT foap.asset_id FROM foreman_openscap_asset_policies AS foap LEFT JOIN foreman_openscap_assets AS foa ON foap.asset_id = foa.id WHERE foa.id IS NULL);
  

For more KB articles/solutions related to Red Hat Satellite 6.x OpenSCAP Issues, please refer to the Red Hat Satellite Consolidated Troubleshooting Article for Red Hat Satellite 6.x OpenSCAP Issues

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.