Looks like it's probably my "fix" in #5841 that's triggering this too.

Possibly, but I am uncertain as to what you are asking for and where to get it. :)

Jeffrey Miller wrote:

Possibly, but I am uncertain as to what you are asking for and where to get it. :)

You have some roles and filters set up for those users that give them access to view_hosts (under Adminster > Roles). When editing the filter, there's a search term (hostgroup = Foo perhaps), could you share that?

Sure thing. For the "Host/managed" resource, the permissions (view_hosts, create_hosts, edit_hosts, destroy_hosts, build_hosts, puppetrun_hosts) have the following filter:

(hostgroup_id = 10 or hostgroup_id = 3 or hostgroup_id = 4 or hostgroup_id = 16)

The "(" and ")" were shown as part of the search filter.

Note: I tried recreating the hostgroup filter so that instead of using the hostgroup_id, it was using the "hostgroup = XXX" instead with the same results. I also tried reducing the complexity of the filter to just one hostgroup or hostgroup_id instead of the multiple ones listed above. The results were the same as the filter previously listed in note #6.

I can confirm the same problem on my setup.
The dashboard and reports pages crash with a 500 for anyone but admins.
Reverting #5841 fixes the issue for me.

My Anonymous Role has this:
Host/managed -- view_hosts -- not has hostgroup

Even removing the view_hosts filter causes the issue.
Basically the only way it works is to have a role filter somewhere that has no search clause and allows all users to view all hosts (not what I want).

This subquery:

SELECT "hosts"."id" FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND ((("hostgroups"."id" = '5') OR ("hostgroups"."id" = '9') OR ("hostgroups"."id" = '1') OR ("hostgroups"."id" = '2') OR ("hostgroups"."id" = '8') OR ("hostgroups"."id" = '7') OR ("hostgroups"."id" = '17') OR ("hostgroups"."id" = '12') OR ("hostgroups"."id" = '16'))) ORDER BY "hosts"."name" ASC NULLS FIRST

requests data from a table (hostgroups) that is not joined

I just finished the upgrade to 1.7.2 and reverting #5841 has fixed the issue for me as well.

#5841 which introduced the problem in Foreman 1.7.1 will be reverted in Foreman 1.7.3 so as to keep the 1.7 series stable. However it is still in Foreman 1.8 at the moment, so I'm leaving this ticket open in the hope we can find a solution for both problems (performance and errors).

I raised this on foreman-dev in the hope we can get some more ideas about a proper solution: https://groups.google.com/forum/#!topic/foreman-dev/2x7KYYzWLAk

The associated PR should now achieve both performance and lack-of-error objectives by manually creating the AR queries with joins in the right places.

https://github.com/wvanbergen/scoped_search/issues/125 is filed to request better support in scoped_search for constructing these types of queries.