Bug #8817
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'
Related issues
Associated revisions
fixes #8817 - look up reports with all joins from host scoped_search
This changes the optimisation in d50c799 which caused errors for users with
host filters referencing tables other than hosts.
When retrieving all reports joined with authorised hosts, the nested joins need
to be passed into AR via .joins on the main scope (reports) rather than what
happened with scoped_search, which only specifies the joins on the inner scope.
In that case, they're ignored and not included in the table list.
Retrieving the conditionals and tables from scoped_search directly allows us to
build up a more correct authorisation AR query with joins.
fixes #8817 - look up reports with all joins from host scoped_search
This changes the optimisation in d50c799 which caused errors for users with
host filters referencing tables other than hosts.
When retrieving all reports joined with authorised hosts, the nested joins need
to be passed into AR via .joins on the main scope (reports) rather than what
happened with scoped_search, which only specifies the joins on the inner scope.
In that case, they're ignored and not included in the table list.
Retrieving the conditionals and tables from scoped_search directly allows us to
build up a more correct authorisation AR query with joins.
(cherry picked from commit 54592c2f126680db8980f3909ad085124c5983d1)
Conflicts:
app/models/report.rb
History
#1
Updated by Dominic Cleal over 8 years ago
Updated by - 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.
#2
Updated by Dominic Cleal over 8 years ago
- Related to Bug #5841: Foreman dashboard slow in 1.5 for non admin users added
#3
Updated by Dominic Cleal over 8 years ago
Looks like it's probably my "fix" in #5841 that's triggering this too.
#4
Updated by Jeffrey Miller over 8 years ago
Updated by Possibly, but I am uncertain as to what you are asking for and where to get it. :)
#5
Updated by Dominic Cleal over 8 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?
#6
Updated by Jeffrey Miller over 8 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.
#7
Updated by Jeffrey Miller over 8 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.
#8
Updated by Chris Edester over 8 years ago
Updated by 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).
#9
Updated by Dominic Cleal over 8 years ago
- Has duplicate Bug #9398: Invalid MySQL query after login on 1.7.x added
#10
Updated by Martin Matuška over 8 years ago
Updated by 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
#11
Updated by Dominic Cleal over 8 years ago
- Has duplicate Bug #9409: Update from 1.6 to 1.7 user role problem in view Report and Dashboard added
#12
Updated by Jeffrey Miller over 8 years ago
I just finished the upgrade to 1.7.2 and reverting #5841 has fixed the issue for me as well.
#13
Updated by Dominic Cleal over 8 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
#14
Updated by Dominic Cleal over 8 years ago
- Legacy Backlogs Release (now unused) set to 28
#15
Updated by Dominic Cleal about 8 years ago
- Status changed from New to Assigned
- Assignee set to Dominic Cleal
#16
Updated by The Foreman Bot about 8 years ago
Updated by - Status changed from Assigned to Ready For Testing
- Pull request https://github.com/theforeman/foreman/pull/2239 added
- Pull request deleted (
)
#17
Updated by Dominic Cleal about 8 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.
#18
Updated by Dominic Cleal about 8 years ago
- Legacy Backlogs Release (now unused) deleted (
28)
#19
Updated by Dominic Cleal about 8 years ago
- Status changed from Ready For Testing to Closed
- % Done changed from 0 to 100
Applied in changeset 54592c2f126680db8980f3909ad085124c5983d1.
#20
Updated by Dominic Cleal about 8 years ago
- Legacy Backlogs Release (now unused) set to 50
#21
Updated by Dominic Cleal about 8 years ago
- Related to Bug #10627: Monitor -> Reports will show reports for hosts not in the Organization context added
#22
Updated by Kal McFate almost 8 years ago
Updated by - Related to Bug #10819: PGError: ERROR: missing FROM-clause entry for table "hosts_reports" LINE 1 added
refs #8817 - Revert "fixes #5841 - use inner query when retrieving reports for subset of authorised hosts"
This reverts commit ecdb85a84915954f312889ba51a9b35de86cd38c.