Description of problem: We have a role that is limited to actions on a certain hostgroup. Since 6.2 we had to add the "Config report" view_config_reports permission so that the users can see the reports of their hosts. However this only works if the permission is added without a filter. When adding a filter (like: "hostgroup_title ~ ourgroup"), the user still gets the "reports" button on the host overview page, but when clicking it (and thus accessing e.g. https://sat6.example.com/hosts/client.example.com/config_reports) a nice PostgreSQL error is shown: Oops, we're sorry but something went wrong PG::Error: ERROR: invalid reference to FROM-clause entry for table "hostgroups" LINE 1: SELECT hostgroups.id FROM "hostgroups" "hostgroups_reports" ^ HINT: Perhaps you meant to reference the table alias "hostgroups_reports". : SELECT hostgroups.id FROM "hostgroups" "hostgroups_reports" Looking at the PostgreSQL logs, the log is not much more than the error message itself: 2017-02-21 09:46:56 CET ERROR: invalid reference to FROM-clause entry for table "hostgroups" at character 8 2017-02-21 09:46:56 CET HINT: Perhaps you meant to reference the table alias "hostgroups_reports". 2017-02-21 09:46:56 CET STATEMENT: SELECT hostgroups.id FROM "hostgroups" "hostgroups_reports" However, I see another error, which was generated with the same action, but for another user, which has slightly different rights: 2017-02-21 09:14:20 CET ERROR: missing FROM-clause entry for table "hosts_reports" at character 1381 2017-02-21 09:14:20 CET STATEMENT: SELECT "reports"."id" AS t0_r0, "reports"."host_id" AS t0_r1, "reports"."reported_at" AS t0_r2, "reports"."created_at" AS t0_r3, "reports"."updated_at" AS t0_r4, "reports"."status" AS t0_r5, "reports"."metrics" AS t0_r6, "reports"."type" AS t0_r7, "reports"."openscap_proxy_id" AS t0_r8, "hostgroups"."id" AS t1_r0, "hostgroups"."name" AS t1_r1, "hostgroups"."created_at" AS t1_r2, "hostgroups"."updated_at" AS t1_r3, "hostgroups"."environment_id" AS t1_r4, "hostgroups"."operatingsystem_id" AS t1_r5, "hostgroups"."architecture_id" AS t1_r6, "hostgroups"."medium_id" AS t1_r7, "hostgroups"."ptable_id" AS t1_r8, "hostgroups"."root_pass" AS t1_r9, "hostgroups"."puppet_ca_proxy_id" AS t1_r10, "hostgroups"."use_image" AS t1_r11, "hostgroups"."image_file" AS t1_r12, "hostgroups"."ancestry" AS t1_r13, "hostgroups"."vm_defaults" AS t1_r14, "hostgroups"."subnet_id" AS t1_r15, "hostgroups"."domain_id" AS t1_r16, "hostgroups"."puppet_proxy_id" AS t1_r17, "hostgroups"."title" AS t1_r18, "hostgroups"."realm_id" AS t1_r19, "hostgroups"."compute_profile_id" AS t1_r20, "hostgroups"."content_source_id" AS t1_r21, "hostgroups"."grub_pass" AS t1_r22, "hostgroups"."content_view_id" AS t1_r23, "hostgroups"."lifecycle_environment_id" AS t1_r24, "hostgroups"."lookup_value_matcher" AS t1_r25, "hostgroups"."kickstart_repository_id" AS t1_r26, "hostgroups"."openscap_proxy_id" AS t1_r27, "hosts_reports"."id" AS t2_r0, "hosts_reports"."name" AS t2_r1, "hosts_reports"."last_compile" AS t2_r2, "hosts_reports"."last_report" AS t2_r3, "hosts_reports"."updated_at" AS t2_r4, "hosts_reports"."created_at" AS t2_r5, "hosts_reports"."root_pass" AS t2_r6, "hosts_reports"."architecture_id" AS t2_r7, "hosts_reports"."operatingsystem_id" AS t2_r8, "hosts_reports"."environment_id" AS t2_r9, "hosts_reports"."ptable_id" AS t2_r10, "hosts_reports"."medium_id" AS t2_r11, "hosts_reports"."build" AS t2_r12, "hosts_reports"."comment" AS t2_r13, "hosts_reports"."disk" AS t2_r14, "hosts_reports"."installed_at" AS t2_r15, "hosts_reports"."model_id" AS t2_r16, "hosts_reports"."hostgroup_id" AS t2_r17, "hosts_reports"."owner_id" AS t2_r18, "hosts_reports"."owner_type" AS t2_r19, "hosts_reports"."enabled" AS t2_r20, "hosts_reports"."puppet_ca_proxy_id" AS t2_r21, "hosts_reports"."managed" AS t2_r22, "hosts_reports"."use_image" AS t2_r23, "hosts_reports"."image_file" AS t2_r24, "hosts_reports"."uuid" AS t2_r25, "hosts_reports"."compute_resource_id" AS t2_r26, "hosts_reports"."puppet_proxy_id" AS t2_r27, "hosts_reports"."certname" AS t2_r28, "hosts_reports"."image_id" AS t2_r29, "hosts_reports"."organization_id" AS t2_r30, "hosts_reports"."location_id" AS t2_r31, "hosts_reports"."type" AS t2_r32, "hosts_reports"."otp" AS t2_r33, "hosts_reports"."realm_id" AS t2_r34, "hosts_reports"."compute_profile_id" AS t2_r35, "hosts_reports"."provision_method" AS t2_r36, "hosts_reports"."content_source_id" AS t2_r37, "hosts_reports"."grub_pass" AS t2_r38, "hosts_reports"."discovery_rule_id" AS t2_r39, "hosts_reports"."content_view_id" AS t2_r40, "hosts_reports"."lifecycle_environment_id" AS t2_r41, "hosts_reports"."global_status" AS t2_r42, "hosts_reports"."lookup_value_matcher" AS t2_r43, "hosts_reports"."openscap_proxy_id" AS t2_r44 FROM "reports" INNER JOIN "hosts" ON "hosts"."id" = "reports"."host_id" AND "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" IN (1) AND "hosts"."location_id" IN (8, 27, 21, 19, 9, 3, 4, 20) LEFT OUTER JOIN "hosts" "hosts_reports_join" ON "hosts_reports_join"."id" = "reports"."host_id" AND "hosts_reports_join"."type" IN ('Host::Managed') AND "hosts_reports_join"."organization_id" IN (1) AND "hosts_reports_join"."location_id" IN (8, 27, 21, 19, 9, 3, 4, 20) LEFT OUTER JOIN "hostgroups" ON "hostgroups"."id" = "hosts_reports_join"."hostgroup_id" AND "hostgroups"."id" IN (34, 43, 25, 32, 12, 1, 42, 11, 18, 59, 78, 53, 54, 62, 19, 2, 3, 47, 72, 23, 41, 35, 61, 65, 75, 20, 69, 44, 22, 63, 37, 70, 14, 27, 48, 55, 17, 68, 36, 15, 66, 38, 60, 50, 74, 33, 73, 56) WHERE "reports"."type" IN ('ConfigReport') AND (("hostgroups"."title" ILIKE '%ourgroup%')) AND (("hosts"."name" = 'client.example.com')) ORDER BY "reports"."reported_at" DESC LIMIT 25 OFFSET 0 Version-Release number of selected component (if applicable): 6.2.7 How reproducible: always Steps to Reproduce: 1. create a user that is allowed to see config reports based on a hostgroup substring (like: "hostgroup_title ~ ourgroup") 2. open https://sat6.example.com/hosts/client.example.com/config_reports for a host in such a group 3. Actual results: PostgreSQL error Expected results: list of reports Additional info:
Similar error when accessing https://sat62.egolov.example.com/config_reports?search=eventful+%3D+true 2017-02-22 11:20:25 CET ERROR: missing FROM-clause entry for table "hosts_reports" at character 1381 2017-02-22 11:20:25 CET STATEMENT: SELECT "reports"."id" AS t0_r0, "reports"."host_id" AS t0_r1, "reports"."reported_at" AS t0_r2, "reports"."created_at" AS t0_r3, "reports"."updated_at" AS t0_r4, "reports"."status" AS t0_r5, "reports"."metrics" AS t0_r6, "reports"."type" AS t0_r7, "reports"."openscap_proxy_id" AS t0_r8, "hostgroups"."id" AS t1_r0, "hostgroups"."name" AS t1_r1, "hostgroups"."created_at" AS t1_r2, "hostgroups"."updated_at" AS t1_r3, "hostgroups"."environment_id" AS t1_r4, "hostgroups"."operatingsystem_id" AS t1_r5, "hostgroups"."architecture_id" AS t1_r6, "hostgroups"."medium_id" AS t1_r7, "hostgroups"."ptable_id" AS t1_r8, "hostgroups"."root_pass" AS t1_r9, "hostgroups"."puppet_ca_proxy_id" AS t1_r10, "hostgroups"."use_image" AS t1_r11, "hostgroups"."image_file" AS t1_r12, "hostgroups"."ancestry" AS t1_r13, "hostgroups"."vm_defaults" AS t1_r14, "hostgroups"."subnet_id" AS t1_r15, "hostgroups"."domain_id" AS t1_r16, "hostgroups"."puppet_proxy_id" AS t1_r17, "hostgroups"."title" AS t1_r18, "hostgroups"."realm_id" AS t1_r19, "hostgroups"."compute_profile_id" AS t1_r20, "hostgroups"."content_source_id" AS t1_r21, "hostgroups"."grub_pass" AS t1_r22, "hostgroups"."content_view_id" AS t1_r23, "hostgroups"."lifecycle_environment_id" AS t1_r24, "hostgroups"."lookup_value_matcher" AS t1_r25, "hostgroups"."kickstart_repository_id" AS t1_r26, "hostgroups"."openscap_proxy_id" AS t1_r27, "hosts_reports"."id" AS t2_r0, "hosts_reports"."name" AS t2_r1, "hosts_reports"."last_compile" AS t2_r2, "hosts_reports"."last_report" AS t2_r3, "hosts_reports"."updated_at" AS t2_r4, "hosts_reports"."created_at" AS t2_r5, "hosts_reports"."root_pass" AS t2_r6, "hosts_reports"."architecture_id" AS t2_r7, "hosts_reports"."operatingsystem_id" AS t2_r8, "hosts_reports"."environment_id" AS t2_r9, "hosts_reports"."ptable_id" AS t2_r10, "hosts_reports"."medium_id" AS t2_r11, "hosts_reports"."build" AS t2_r12, "hosts_reports"."comment" AS t2_r13, "hosts_reports"."disk" AS t2_r14, "hosts_reports"."installed_at" AS t2_r15, "hosts_reports"."model_id" AS t2_r16, "hosts_reports"."hostgroup_id" AS t2_r17, "hosts_reports"."owner_id" AS t2_r18, "hosts_reports"."owner_type" AS t2_r19, "hosts_reports"."enabled" AS t2_r20, "hosts_reports"."puppet_ca_proxy_id" AS t2_r21, "hosts_reports"."managed" AS t2_r22, "hosts_reports"."use_image" AS t2_r23, "hosts_reports"."image_file" AS t2_r24, "hosts_reports"."uuid" AS t2_r25, "hosts_reports"."compute_resource_id" AS t2_r26, "hosts_reports"."puppet_proxy_id" AS t2_r27, "hosts_reports"."certname" AS t2_r28, "hosts_reports"."image_id" AS t2_r29, "hosts_reports"."organization_id" AS t2_r30, "hosts_reports"."location_id" AS t2_r31, "hosts_reports"."type" AS t2_r32, "hosts_reports"."otp" AS t2_r33, "hosts_reports"."realm_id" AS t2_r34, "hosts_reports"."compute_profile_id" AS t2_r35, "hosts_reports"."provision_method" AS t2_r36, "hosts_reports"."content_source_id" AS t2_r37, "hosts_reports"."grub_pass" AS t2_r38, "hosts_reports"."content_view_id" AS t2_r39, "hosts_reports"."lifecycle_environment_id" AS t2_r40, "hosts_reports"."global_status" AS t2_r41, "hosts_reports"."lookup_value_matcher" AS t2_r42, "hosts_reports"."discovery_rule_id" AS t2_r43, "hosts_reports"."openscap_proxy_id" AS t2_r44 FROM "reports" INNER JOIN "hosts" ON "hosts"."id" = "reports"."host_id" AND "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" IN (1) AND "hosts"."location_id" IN (2) LEFT OUTER JOIN "hosts" "hosts_reports_join" ON "hosts_reports_join"."id" = "reports"."host_id" AND "hosts_reports_join"."type" IN ('Host::Managed') AND "hosts_reports_join"."organization_id" IN (1) AND "hosts_reports_join"."location_id" IN (2) LEFT OUTER JOIN "hostgroups" ON "hostgroups"."id" = "hosts_reports_join"."hostgroup_id" AND "hostgroups"."id" IN (1) WHERE "reports"."type" IN ('ConfigReport') AND (("hostgroups"."title" ILIKE '%default%')) AND (1=0) AND (((("reports"."status" >> 0 & 16777215) > 0))) ORDER BY "reports"."reported_at" DESC LIMIT 20 OFFSET 0
This does not happen when filtering for "host", but for "hostgroup", "hostgroup_fullname", "hostgroup_title" or "environment"
Confirm this is a separate bug then similar ones, and also exists in upstream. Seems to be related to the permission filtering, updating component.
Created redmine issue http://projects.theforeman.org/issues/18622 from this bug
Thank you for your interest in Satellite 6. We have evaluated this request, and we do not expect this to be implemented in the product in the foreseeable future. We are therefore closing this out as WONTFIX. If you have any concerns about this, please feel free to contact Rich Jerrido or Bryan Kearney. Thank you.