Bug #8817
closed
PGError: ERROR: missing FROM-clause entry for table "hostgroups"
Description
After migrating from Foreman 1.6.3 to 1.7.1, users that are not an administrator receive the above error line upon login (full error is listed at the end). There appears to be no other loss in functionality after the migration except for users that are not administrators. The users are successfully logged in and authenticated by the system.
The following observations have been found thus far:
URLs that result in the PGError:
/dashboard
/reports
Some URLs that do not result in the PGError:
/reports/6146670
/hosts
/hosts/F.Q.D.N/reports/last
/hostgroups
/fact_values
/statistics
/trends
/operatingsystems
/architectures
When selecting an individual host from the /hosts URL and upon clicking the reports button, the PGError occurs.
Navigating to the following URLs works:
/hosts?search=last_report+>"35+minutes+ago"
/hosts?search=last_report>"35+minutes+ago"+and(status.failed+>0+or+status.failed_restarts>0)+and+status.enabled%3D+true
When the user goes to a specific report from that page, they can view the report.
The full trace one of the instances of the error (generated upon going to the dashboard after login) is:
ActiveRecord::StatementInvalid
PGError: ERROR: missing FROM-clause entry for table "hostgroups" LINE 1: ... WHERE "hosts"."type" IN ('Host::Managed') AND ((("hostgroup... ^ : SELECT COUNT FROM (SELECT "reports"."id" AS count_column FROM "reports" LEFT OUTER JOIN "hosts" ON "hosts"."id" = "reports"."host_id" AND "hosts"."type" IN ('Host::Managed') WHERE "reports"."host_id" IN (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 ) AND (status <> 0) AND (("reports"."reported_at" >= '2014-12-25')) LIMIT 6) subquery_for_count
app/views/dashboard/_reports_widget.html.erb:3:in `_app_views_dashboard__reports_widget_html_erb___1377828463669829617_74013300'
app/views/dashboard/index.html.erb:9:in `block in app_views_dashboard_index_html_erb_2431672577634662470_72666120'
app/views/dashboard/index.html.erb:8:in `map'
app/views/dashboard/index.html.erb:8:in `_app_views_dashboard_index_html_erb__2431672577634662470_72666120'
app/controllers/dashboard_controller.rb:6:in `index'
app/models/concerns/foreman/thread_session.rb:33:in `clear_thread'
lib/middleware/catch_json_parse_errors.rb:9:in `call'
Updated by Dominic Cleal over 9 years ago
- Category set to Users, Roles and Permissions
Could you share what search filters you have set up for those users please? I'm guessing you have some host group related ones for viewing hosts which is probably generating the SQL that's causing the problem.
Updated by Dominic Cleal over 9 years ago
- Related to Bug #5841: Foreman dashboard slow in 1.5 for non admin users added
Updated by Dominic Cleal over 9 years ago
Looks like it's probably my "fix" in #5841 that's triggering this too.
Updated by Jeffrey Miller over 9 years ago
Possibly, but I am uncertain as to what you are asking for and where to get it. :)
Updated by Dominic Cleal over 9 years ago
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?
Updated by Jeffrey Miller over 9 years ago
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.
Updated by Jeffrey Miller over 9 years ago
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.
Updated by Chris Edester about 9 years ago
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).
Updated by Dominic Cleal about 9 years ago
- Has duplicate Bug #9398: Invalid MySQL query after login on 1.7.x added
Updated by Martin Matuška about 9 years ago
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
Updated by Dominic Cleal about 9 years ago
- Has duplicate Bug #9409: Update from 1.6 to 1.7 user role problem in view Report and Dashboard added
Updated by Jeffrey Miller about 9 years ago
I just finished the upgrade to 1.7.2 and reverting #5841 has fixed the issue for me as well.
Updated by Dominic Cleal about 9 years ago
#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
Updated by Dominic Cleal about 9 years ago
- translation missing: en.field_release set to 28
Updated by Dominic Cleal about 9 years ago
- Status changed from New to Assigned
- Assignee set to Dominic Cleal
Updated by The Foreman Bot about 9 years ago
- Status changed from Assigned to Ready For Testing
- Pull request https://github.com/theforeman/foreman/pull/2239 added
- Pull request deleted (
)
Updated by Dominic Cleal about 9 years ago
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.
Updated by Dominic Cleal about 9 years ago
- translation missing: en.field_release deleted (
28)
Updated by Dominic Cleal almost 9 years ago
- Status changed from Ready For Testing to Closed
- % Done changed from 0 to 100
Applied in changeset 54592c2f126680db8980f3909ad085124c5983d1.
Updated by Dominic Cleal almost 9 years ago
- translation missing: en.field_release set to 50
Updated by Dominic Cleal almost 9 years ago
- Related to Bug #10627: Monitor -> Reports will show reports for hosts not in the Organization context added
Updated by Kal McFate almost 9 years ago
- Related to Bug #10819: PGError: ERROR: missing FROM-clause entry for table "hosts_reports" LINE 1 added