Project

General

Profile

Bug #8817

PGError: ERROR: missing FROM-clause entry for table "hostgroups"

Added by Jeffrey Miller over 4 years ago. Updated 9 months ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Authorization
Target version:
Difficulty:
Triaged:
Bugzilla link:
Team Backlog:
Fixed in Releases:
Found in Releases:

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

Related to Foreman - Bug #5841: Foreman dashboard slow in 1.5 for non admin usersClosed2014-05-20
Related to Foreman - Bug #10627: Monitor -> Reports will show reports for hosts not in the Organization contextClosed2015-05-27
Related to Foreman - Bug #10819: PGError: ERROR: missing FROM-clause entry for table "hosts_reports" LINE 1New2015-06-15
Has duplicate Foreman - Bug #9398: Invalid MySQL query after login on 1.7.xDuplicate2015-02-16
Has duplicate Foreman - Bug #9409: Update from 1.6 to 1.7 user role problem in view Report and DashboardDuplicate2015-02-17

Associated revisions

Revision 1e611cee (diff)
Added by Dominic Cleal about 4 years ago

refs #8817 - Revert "fixes #5841 - use inner query when retrieving reports for subset of authorised hosts"

This reverts commit ecdb85a84915954f312889ba51a9b35de86cd38c.

Revision 4e685156 (diff)
Added by Dominic Cleal about 4 years ago

refs #8817 - Revert "fixes #5841 - use inner query when retrieving reports for subset of authorised hosts"

This reverts commit ecdb85a84915954f312889ba51a9b35de86cd38c.

Revision 54592c2f (diff)
Added by Dominic Cleal almost 4 years ago

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.

Revision 825e3990 (diff)
Added by Dominic Cleal almost 4 years ago

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 4 years ago

  • Category set to Authorization

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 4 years ago

  • Related to Bug #5841: Foreman dashboard slow in 1.5 for non admin users added

#3 Updated by Dominic Cleal over 4 years ago

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

#4 Updated by Jeffrey Miller over 4 years ago

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

#5 Updated by Dominic Cleal over 4 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 4 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 4 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 about 4 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).

#9 Updated by Dominic Cleal about 4 years ago

  • Has duplicate Bug #9398: Invalid MySQL query after login on 1.7.x added

#10 Updated by Martin Matuška about 4 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

#11 Updated by Dominic Cleal about 4 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 about 4 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 about 4 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 about 4 years ago

  • Legacy Backlogs Release (now unused) set to 28

#15 Updated by Dominic Cleal about 4 years ago

  • Status changed from New to Assigned
  • Assignee set to Dominic Cleal

#16 Updated by The Foreman Bot about 4 years ago

  • 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 4 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 almost 4 years ago

  • Legacy Backlogs Release (now unused) deleted (28)

#19 Updated by Dominic Cleal almost 4 years ago

  • Status changed from Ready For Testing to Closed
  • % Done changed from 0 to 100

#20 Updated by Dominic Cleal almost 4 years ago

  • Legacy Backlogs Release (now unused) set to 50

#21 Updated by Dominic Cleal almost 4 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 4 years ago

  • Related to Bug #10819: PGError: ERROR: missing FROM-clause entry for table "hosts_reports" LINE 1 added

Also available in: Atom PDF