Project

General

Profile

Bug #32641

PG::AmbiguousColumn when listing domains

Added by Nacho Barrientos 3 months ago. Updated 2 months ago.

Status:
Closed
Priority:
Normal
Assignee:
-
Category:
Database
Target version:
Difficulty:
Triaged:
No
Bugzilla link:
Fixed in Releases:
Found in Releases:

Description

Hi,

When a user who is not admin or does not have any role allowing to see all hosts, browsing /domains leads to a PG::AmbiguousColumn exception as follows:

Oops, we're sorry but something went wrong PG::AmbiguousColumn: ERROR: column reference "domain_id" is ambiguous LINE 1: SELECT COUNT(DISTINCT "hosts"."id") AS count_id, domain_id A... ^

Digging a bit deeper, I managed to reproduce it using the Rails console by playing a bit with what counted_hosts does when the template calls hosts_count[domain]

> User.current = User.find_by_login('myuser')
> hosts_scope = Host::Managed.reorder('') 
> hosts_scope = hosts_scope.joins(:primary_interface)
irb(main):004:0> hosts_scope.authorized(:view_hosts).group("domain_id").count
Traceback (most recent call last):
        2: from lib/tasks/console.rake:5:in `block in <top (required)>'
        1: from (irb):4
ActiveRecord::StatementInvalid (PG::AmbiguousColumn: ERROR:  column reference "domain_id" is ambiguous)
LINE 1: SELECT COUNT(DISTINCT "hosts"."id") AS count_id, domain_id A...

However if I manually specify that I want to group by nics's domain_id it works as expected:

> hosts_scope.authorized(:view_hosts).group("nics.domain_id").count
=> {1=>510}

As I mentioned before, admin users or users with a role with a filter "view_hosts" unlimited do not experience this problem.


Related issues

Has duplicate Foreman - Bug #32629: Subnets and Domains page throw sql error for non-admin userClosed

Associated revisions

Revision 3f088d6b (diff)
Added by Nacho Barrientos 2 months ago

Fixes #32641 - Disambiguate column name

History

#1 Updated by Nacho Barrientos 3 months ago

rubygem-rails-6.0.3.4-1.el8.noarch
rubygem-activesupport-6.0.3.4-1.el8.noarch

#2 Updated by Nacho Barrientos 3 months ago

Generated SQL:

SELECT COUNT(DISTINCT "hosts"."id") AS count_id, domain_id AS domain_id FROM "hosts" INNER JOIN "nics" ON "nics"."primary" = $1 AND "nics"."host_id" = "hosts"."id" LEFT OUTER JOIN "hostgroups" ON "hostgroups"."id" = "hosts"."hostgroup_id" AND (hostgroups.id IN (13,[......],27679,193)) WHERE "hosts"."type" = $2 AND "hosts"."organization_id" = $3 AND "hosts"."location_id" = $4 AND ((("hostgroups"."title" ILIKE 'foo/%') OR ("hostgroups"."title" = 'foo') OR ("hostgroups"."title" ILIKE 'bar/%') OR ("hostgroups"."title" = 'bar'))) GROUP BY domain_id  [["primary", true], ["type", "Host::Managed"], ["organization_id", 2], ["location_id", 1]]

The ambiguous domain_id comes from the fact that it's provided by two tables in the join, nics and hostgroups.

The hostgroups table is involved in this case I believe because User.current has some roles that via filters only allow to see hosts of some hostgroups.

If I repeat the experiment with a user with a role allowing to see all hosts (or an admin) indeed the query does not involve the hostgroups table at all.

#3 Updated by The Foreman Bot 3 months ago

  • Status changed from New to Ready For Testing
  • Pull request https://github.com/theforeman/foreman/pull/8544 added

#4 Updated by Ewoud Kohl van Wijngaarden 3 months ago

  • Has duplicate Bug #32629: Subnets and Domains page throw sql error for non-admin user added

#5 Updated by The Foreman Bot 2 months ago

  • Fixed in Releases 3.0.0 added

#6 Updated by Nacho Barrientos 2 months ago

  • Status changed from Ready For Testing to Closed

#7 Updated by Tomer Brisker 2 months ago

  • Target version set to 2.4.1
  • Category set to Database

#8 Updated by Tomer Brisker 2 months ago

  • Fixed in Releases 2.4.1, 2.5.1 added

Also available in: Atom PDF