Project

General

Profile

Actions

Bug #8817

closed

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

Added by Jeffrey Miller over 9 years ago. Updated almost 6 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Users, Roles and Permissions
Target version:
Difficulty:
Triaged:
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 5 (1 open4 closed)

Related to Foreman - Bug #5841: Foreman dashboard slow in 1.5 for non admin usersClosedDominic Cleal05/20/2014Actions
Related to Foreman - Bug #10627: Monitor -> Reports will show reports for hosts not in the Organization contextClosedDominic Cleal05/27/2015Actions
Related to Foreman - Bug #10819: PGError: ERROR: missing FROM-clause entry for table "hosts_reports" LINE 1New06/15/2015Actions
Has duplicate Foreman - Bug #9398: Invalid MySQL query after login on 1.7.xDuplicate02/16/2015Actions
Has duplicate Foreman - Bug #9409: Update from 1.6 to 1.7 user role problem in view Report and DashboardDuplicate02/17/2015Actions
Actions #1

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.

Actions #2

Updated by Dominic Cleal over 9 years ago

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

Updated by Dominic Cleal over 9 years ago

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

Actions #4

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. :)

Actions #5

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?

Actions #6

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.

Actions #7

Updated by Jeffrey Miller about 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.

Actions #8

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).

Actions #9

Updated by Dominic Cleal about 9 years ago

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

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

Actions #11

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
Actions #12

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.

Actions #13

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

Actions #14

Updated by Dominic Cleal about 9 years ago

  • translation missing: en.field_release set to 28
Actions #15

Updated by Dominic Cleal about 9 years ago

  • Status changed from New to Assigned
  • Assignee set to Dominic Cleal
Actions #16

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 ()
Actions #17

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.

Actions #18

Updated by Dominic Cleal almost 9 years ago

  • translation missing: en.field_release deleted (28)
Actions #19

Updated by Dominic Cleal almost 9 years ago

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

Updated by Dominic Cleal almost 9 years ago

  • translation missing: en.field_release set to 50
Actions #21

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
Actions #22

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
Actions

Also available in: Atom PDF