Project

General

Profile

Bug #11996

fact_values API with non-admin user and search on host.hostgroup throws SQL error

Added by Dominic Cleal almost 4 years ago. Updated about 1 year ago.

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

Description

1. Create a non-admin user, e.g. with the Viewer role
2. Ensure a host is present with facts and a host group set
3. GET /api/v2/fact_values?search=host.hostgroup=Example%20and%20name=ipaddress

It throws an error due to an invalid SQL statement:

2015-09-29T14:00:59 [app] [I] Started GET "/api/v2/fact_values?search=host.hostgroup=Test3%20and%20name=ipaddress" for 127.0.0.1 at 2015-09-29 14:00:59 +0100                                                                                                       [166/92422]
2015-09-29T14:01:00 [app] [I] Processing by Api::V2::FactValuesController#index as JSON
2015-09-29T14:01:00 [app] [I]   Parameters: {"search"=>"host.hostgroup=Test3 and name=ipaddress", "apiv"=>"v2", "fact_value"=>{}}
2015-09-29T14:01:00 [sql] [D]   User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."lower_login" = 'viewer' LIMIT 1
2015-09-29T14:01:00 [sql] [D]   AuthSource Load (0.2ms)  SELECT "auth_sources".* FROM "auth_sources" WHERE "auth_sources"."id" = 1 LIMIT 1
2015-09-29T14:01:00 [sql] [D]   CACHE (0.0ms)  SELECT "users".* FROM "users" WHERE "users"."lower_login" = 'viewer' LIMIT 1
2015-09-29T14:01:00 [sql] [D] Authenticated user viewer against INTERNAL authentication source
2015-09-29T14:01:00 [sql] [D]   User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."lower_login" = 'foreman_admin' LIMIT 1
2015-09-29T14:01:00 [app] [D] Setting current user thread-local variable to foreman_admin
2015-09-29T14:01:00 [app] [D] Setting current user thread-local variable to nil
2015-09-29T14:01:00 [sql] [D] Post-login processing for viewer
2015-09-29T14:01:00 [sql] [D]   CACHE (0.0ms)  SELECT "users".* FROM "users" WHERE "users"."lower_login" = 'foreman_admin' LIMIT 1
2015-09-29T14:01:00 [app] [D] Setting current user thread-local variable to foreman_admin
2015-09-29T14:01:00 [sql] [D]    (0.1ms)  begin transaction
2015-09-29T14:01:00 [sql] [D]    (0.3ms)  UPDATE "users" SET "last_login_on" = '2015-09-29 13:01:00.142449', "updated_at" = '2015-09-29 13:01:00.143557' WHERE "users"."id" = 30
2015-09-29T14:01:00 [sql] [D]   Role Load (0.1ms)  SELECT "roles".* FROM "roles" WHERE "roles"."name" = 'Anonymous' LIMIT 1
2015-09-29T14:01:00 [sql] [D]    (0.1ms)  SELECT "roles".id FROM "roles" INNER JOIN "user_roles" ON "roles"."id" = "user_roles"."role_id" WHERE "user_roles"."owner_id" = 30 AND "user_roles"."owner_type" = 'User'
2015-09-29T14:01:00 [sql] [D]    (24.1ms)  commit transaction
2015-09-29T14:01:00 [sql] [D]   CACHE (0.0ms)  SELECT "roles".* FROM "roles" WHERE "roles"."name" = 'Anonymous' LIMIT 1
2015-09-29T14:01:00 [sql] [D]   Role Exists (0.2ms)  SELECT 1 AS one FROM "roles" INNER JOIN "user_roles" ON "roles"."id" = "user_roles"."role_id" WHERE "user_roles"."owner_id" = 30 AND "user_roles"."owner_type" = 'User' AND "roles"."id" = 8 LIMIT 1
2015-09-29T14:01:00 [app] [D] Setting current user thread-local variable to nil
2015-09-29T14:01:00 [app] [D] Setting current user thread-local variable to viewer
2015-09-29T14:01:00 [sql] [D]    (0.1ms)  SELECT auth_sources.id FROM "auth_sources" WHERE "auth_sources"."type" IN ('AuthSourceHidden')
2015-09-29T14:01:00 [sql] [D]   User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."lower_login" = 'viewer' AND ("users"."auth_source_id" NOT IN (7)) ORDER BY firstname LIMIT 1
2015-09-29T14:01:00 [app] [I] Authorized user viewer(Viewer Viewer)
2015-09-29T14:01:00 [app] [D] Setting current user thread-local variable to viewer
2015-09-29T14:01:00 [sql] [D]   Usergroup Load (0.2ms)  SELECT "usergroups".* FROM "usergroups" INNER JOIN "cached_usergroup_members" ON "usergroups"."id" = "cached_usergroup_members"."usergroup_id" WHERE "cached_usergroup_members"."user_id" = 30 ORDER BY usergroups.name
2015-09-29T14:01:00 [sql] [D]   Role Load (0.2ms)  SELECT DISTINCT "roles".* FROM "roles" INNER JOIN "cached_user_roles" ON "roles"."id" = "cached_user_roles"."role_id" WHERE "cached_user_roles"."user_id" = 30
2015-09-29T14:01:00 [sql] [D]    (1.1ms)  SELECT permissions.name FROM "permissions" INNER JOIN "filterings" ON "permissions"."id" = "filterings"."permission_id" INNER JOIN "filters" ON "filterings"."filter_id" = "filters"."id" WHERE "filters"."role_id" = 20 ORDER BY rol
e_id, filters.id
2015-09-29T14:01:00 [permissions] [D] checking permission view_facts
2015-09-29T14:01:00 [sql] [D]   Filter Load (0.3ms)  SELECT "filters".* FROM "filters" INNER JOIN "filterings" ON "filterings"."filter_id" = "filters"."id" INNER JOIN "permissions" ON "permissions"."id" = "filterings"."permission_id" INNER JOIN "roles" ON "filters"."role
_id" = "roles"."id" INNER JOIN "cached_user_roles" ON "roles"."id" = "cached_user_roles"."role_id" WHERE "cached_user_roles"."user_id" = 30 AND (permissions.resource_type = 'FactValue') AND (permissions.name = 'view_facts') ORDER BY role_id, filters.id
2015-09-29T14:01:00 [permissions] [D] filter with role_id: 20 limited: false search:  taxonomy_search: 
2015-09-29T14:01:00 [permissions] [D] checking permission view_hosts
2015-09-29T14:01:00 [sql] [D]   Filter Load (0.4ms)  SELECT "filters".* FROM "filters" INNER JOIN "filterings" ON "filterings"."filter_id" = "filters"."id" INNER JOIN "permissions" ON "permissions"."id" = "filterings"."permission_id" INNER JOIN "roles" ON "filters"."role
_id" = "roles"."id" INNER JOIN "cached_user_roles" ON "roles"."id" = "cached_user_roles"."role_id" WHERE "cached_user_roles"."user_id" = 30 AND (permissions.resource_type = 'Host') AND (permissions.name = 'view_hosts') ORDER BY role_id, filters.id
2015-09-29T14:01:00 [permissions] [D] filter with role_id: 20 limited: false search:  taxonomy_search: 
2015-09-29T14:01:00 [sql] [D]   SQL (1.4ms)  SELECT DISTINCT "fact_values".id FROM "fact_values" INNER JOIN "hosts" ON "hosts"."id" = "fact_values"."host_id" AND "hosts"."type" IN ('Host::Managed') LEFT OUTER JOIN "fact_names" ON "fact_names"."id" = "fact_values"."fact_n
ame_id" LEFT OUTER JOIN "hosts" "hosts_fact_values_join" ON "hosts_fact_values_join"."id" = "fact_values"."host_id" AND "hosts_fact_values_join"."type" IN ('Host::Managed') LEFT OUTER JOIN "hostgroups" ON "hostgroups"."id" = "hosts_fact_values_join"."hostgroup_id" WHERE 
(fact_names.name <> '_timestamp') AND ((("fact_values"."id" IN (SELECT "fact_values"."id" FROM "fact_values" 
 |  INNER JOIN "hosts" 
 |  ON "fact_values"."host_id" = "hosts"."id" 
 |  INNER JOIN "hostgroups" 
 |  ON "hosts"."hostgroup_id" = "hostgroups"."id" 
 |  WHERE "hostgroups"."name" = 'Test3' )) AND ("fact_names"."name" = 'ipaddress'))) ORDER BY "fact_values"."value" ASC LIMIT 40 OFFSET 0
2015-09-29T14:01:00 [sql] [D]   SQL (0.4ms)  SELECT "fact_values"."id" AS t0_r0, "fact_values"."value" AS t0_r1, "fact_values"."fact_name_id" AS t0_r2, "fact_values"."host_id" AS t0_r3, "fact_values"."updated_at" AS t0_r4, "fact_values"."created_at" AS t0_r5, "fact_names
"."id" AS t1_r0, "fact_names"."name" AS t1_r1, "fact_names"."updated_at" AS t1_r2, "fact_names"."created_at" AS t1_r3, "fact_names"."compose" AS t1_r4, "fact_names"."short_name" AS t1_r5, "fact_names"."type" AS t1_r6, "fact_names"."ancestry" AS t1_r7, "hostgroups"."id" A
S t2_r0, "hostgroups"."name" AS t2_r1, "hostgroups"."created_at" AS t2_r2, "hostgroups"."updated_at" AS t2_r3, "hostgroups"."environment_id" AS t2_r4, "hostgroups"."operatingsystem_id" AS t2_r5, "hostgroups"."architecture_id" AS t2_r6, "hostgroups"."medium_id" AS t2_r7, 
"hostgroups"."ptable_id" AS t2_r8, "hostgroups"."root_pass" AS t2_r9, "hostgroups"."puppet_ca_proxy_id" AS t2_r10, "hostgroups"."use_image" AS t2_r11, "hostgroups"."image_file" AS t2_r12, "hostgroups"."ancestry" AS t2_r13, "hostgroups"."vm_defaults" AS t2_r14, "hostgroup
s"."subnet_id" AS t2_r15, "hostgroups"."domain_id" AS t2_r16, "hostgroups"."puppet_proxy_id" AS t2_r17, "hostgroups"."title" AS t2_r18, "hostgroups"."compute_profile_id" AS t2_r19, "hostgroups"."realm_id" AS t2_r20, "hostgroups"."grub_pass" AS t2_r21, "hostgroups"."looku
p_value_matcher" AS t2_r22, "hosts_fact_values"."id" AS t3_r0, "hosts_fact_values"."name" AS t3_r1, "hosts_fact_values"."last_compile" AS t3_r2, "hosts_fact_values"."last_report" AS t3_r3, "hosts_fact_values"."updated_at" AS t3_r4, "hosts_fact_values"."created_at" AS t3_
r5, "hosts_fact_values"."root_pass" AS t3_r6, "hosts_fact_values"."architecture_id" AS t3_r7, "hosts_fact_values"."operatingsystem_id" AS t3_r8, "hosts_fact_values"."environment_id" AS t3_r9, "hosts_fact_values"."ptable_id" AS t3_r10, "hosts_fact_values"."medium_id" AS t
3_r11, "hosts_fact_values"."build" AS t3_r12, "hosts_fact_values"."comment" AS t3_r13, "hosts_fact_values"."disk" AS t3_r14, "hosts_fact_values"."installed_at" AS t3_r15, "hosts_fact_values"."model_id" AS t3_r16, "hosts_fact_values"."hostgroup_id" AS t3_r17, "hosts_fact_
values"."owner_id" AS t3_r18, "hosts_fact_values"."owner_type" AS t3_r19, "hosts_fact_values"."enabled" AS t3_r20, "hosts_fact_values"."puppet_ca_proxy_id" AS t3_r21, "hosts_fact_values"."managed" AS t3_r22, "hosts_fact_values"."use_image" AS t3_r23, "hosts_fact_values".
"image_file" AS t3_r24, "hosts_fact_values"."uuid" AS t3_r25, "hosts_fact_values"."compute_resource_id" AS t3_r26, "hosts_fact_values"."puppet_proxy_id" AS t3_r27, "hosts_fact_values"."certname" AS t3_r28, "hosts_fact_values"."image_id" AS t3_r29, "hosts_fact_values"."or
ganization_id" AS t3_r30, "hosts_fact_values"."location_id" AS t3_r31, "hosts_fact_values"."type" AS t3_r32, "hosts_fact_values"."compute_profile_id" AS t3_r33, "hosts_fact_values"."provision_method" AS t3_r34, "hosts_fact_values"."otp" AS t3_r35, "hosts_fact_values"."re
alm_id" AS t3_r36, "hosts_fact_values"."grub_pass" AS t3_r37, "hosts_fact_values"."global_status" AS t3_r38, "hosts_fact_values"."lookup_value_matcher" AS t3_r39 FROM "fact_values" INNER JOIN "hosts" ON "hosts"."id" = "fact_values"."host_id" AND "hosts"."type" IN ('Host:
:Managed') LEFT OUTER JOIN "fact_names" ON "fact_names"."id" = "fact_values"."fact_name_id" LEFT OUTER JOIN "hosts" "hosts_fact_values_join" ON "hosts_fact_values_join"."id" = "fact_values"."host_id" AND "hosts_fact_values_join"."type" IN ('Host::Managed') LEFT OUTER JOI
N "hostgroups" ON "hostgroups"."id" = "hosts_fact_values_join"."hostgroup_id" WHERE "fact_values"."id" IN (1119) AND (fact_names.name <> '_timestamp') AND ((("fact_values"."id" IN (SELECT "fact_values"."id" FROM "fact_values" 
 |  INNER JOIN "hosts" 
 |  ON "fact_values"."host_id" = "hosts"."id" 
 |  INNER JOIN "hostgroups" 
 |  ON "hosts"."hostgroup_id" = "hostgroups"."id" 
 |  WHERE "hostgroups"."name" = 'Test3' )) AND ("fact_names"."name" = 'ipaddress'))) ORDER BY "fact_values"."value" ASC
2015-09-29T14:01:00 [sql] [D] SQLite3::SQLException: no such column: hosts_fact_values.id: SELECT "fact_values"."id" AS t0_r0, "fact_values"."value" AS t0_r1, "fact_values"."fact_name_id" AS t0_r2, "fact_values"."host_id" AS t0_r3, "fact_values"."updated_at" A[101/92422]
act_values"."created_at" AS t0_r5, "fact_names"."id" AS t1_r0, "fact_names"."name" AS t1_r1, "fact_names"."updated_at" AS t1_r2, "fact_names"."created_at" AS t1_r3, "fact_names"."compose" AS t1_r4, "fact_names"."short_name" AS t1_r5, "fact_names"."type" AS t1_r6, "fact_n
ames"."ancestry" AS t1_r7, "hostgroups"."id" AS t2_r0, "hostgroups"."name" AS t2_r1, "hostgroups"."created_at" AS t2_r2, "hostgroups"."updated_at" AS t2_r3, "hostgroups"."environment_id" AS t2_r4, "hostgroups"."operatingsystem_id" AS t2_r5, "hostgroups"."architecture_id" 
 AS t2_r6, "hostgroups"."medium_id" AS t2_r7, "hostgroups"."ptable_id" AS t2_r8, "hostgroups"."root_pass" AS t2_r9, "hostgroups"."puppet_ca_proxy_id" AS t2_r10, "hostgroups"."use_image" AS t2_r11, "hostgroups"."image_file" AS t2_r12, "hostgroups"."ancestry" AS t2_r13, "h
ostgroups"."vm_defaults" AS t2_r14, "hostgroups"."subnet_id" AS t2_r15, "hostgroups"."domain_id" AS t2_r16, "hostgroups"."puppet_proxy_id" AS t2_r17, "hostgroups"."title" AS t2_r18, "hostgroups"."compute_profile_id" AS t2_r19, "hostgroups"."realm_id" AS t2_r20, "hostgrou
ps"."grub_pass" AS t2_r21, "hostgroups"."lookup_value_matcher" AS t2_r22, "hosts_fact_values"."id" AS t3_r0, "hosts_fact_values"."name" AS t3_r1, "hosts_fact_values"."last_compile" AS t3_r2, "hosts_fact_values"."last_report" AS t3_r3, "hosts_fact_values"."updated_at" AS 
t3_r4, "hosts_fact_values"."created_at" AS t3_r5, "hosts_fact_values"."root_pass" AS t3_r6, "hosts_fact_values"."architecture_id" AS t3_r7, "hosts_fact_values"."operatingsystem_id" AS t3_r8, "hosts_fact_values"."environment_id" AS t3_r9, "hosts_fact_values"."ptable_id" A
S t3_r10, "hosts_fact_values"."medium_id" AS t3_r11, "hosts_fact_values"."build" AS t3_r12, "hosts_fact_values"."comment" AS t3_r13, "hosts_fact_values"."disk" AS t3_r14, "hosts_fact_values"."installed_at" AS t3_r15, "hosts_fact_values"."model_id" AS t3_r16, "hosts_fact_
values"."hostgroup_id" AS t3_r17, "hosts_fact_values"."owner_id" AS t3_r18, "hosts_fact_values"."owner_type" AS t3_r19, "hosts_fact_values"."enabled" AS t3_r20, "hosts_fact_values"."puppet_ca_proxy_id" AS t3_r21, "hosts_fact_values"."managed" AS t3_r22, "hosts_fact_value
s"."use_image" AS t3_r23, "hosts_fact_values"."image_file" AS t3_r24, "hosts_fact_values"."uuid" AS t3_r25, "hosts_fact_values"."compute_resource_id" AS t3_r26, "hosts_fact_values"."puppet_proxy_id" AS t3_r27, "hosts_fact_values"."certname" AS t3_r28, "hosts_fact_values" 
."image_id" AS t3_r29, "hosts_fact_values"."organization_id" AS t3_r30, "hosts_fact_values"."location_id" AS t3_r31, "hosts_fact_values"."type" AS t3_r32, "hosts_fact_values"."compute_profile_id" AS t3_r33, "hosts_fact_values"."provision_method" AS t3_r34, "hosts_fact_va
lues"."otp" AS t3_r35, "hosts_fact_values"."realm_id" AS t3_r36, "hosts_fact_values"."grub_pass" AS t3_r37, "hosts_fact_values"."global_status" AS t3_r38, "hosts_fact_values"."lookup_value_matcher" AS t3_r39 FROM "fact_values" INNER JOIN "hosts" ON "hosts"."id" = "fact_v
alues"."host_id" AND "hosts"."type" IN ('Host::Managed') LEFT OUTER JOIN "fact_names" ON "fact_names"."id" = "fact_values"."fact_name_id" LEFT OUTER JOIN "hosts" "hosts_fact_values_join" ON "hosts_fact_values_join"."id" = "fact_values"."host_id" AND "hosts_fact_values_jo
in"."type" IN ('Host::Managed') LEFT OUTER JOIN "hostgroups" ON "hostgroups"."id" = "hosts_fact_values_join"."hostgroup_id" WHERE "fact_values"."id" IN (1119) AND (fact_names.name <> '_timestamp') AND ((("fact_values"."id" IN (SELECT "fact_values"."id" FROM         "fact
_values" 
 |         INNER JOIN "hosts" 
 |         ON "fact_values"."host_id" = "hosts"."id" 
 |         INNER JOIN "hostgroups" 
 |         ON "hosts"."hostgroup_id" = "hostgroups"."id" 
 |  WHERE "hostgroups"."name" = 'Test3' )) AND ("fact_names"."name" = 'ipaddress'))) ORDER BY "fact_values"."value" ASC
2015-09-29T14:01:00 [app] [W] Action failed
 | ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: hosts_fact_values.id: SELECT "fact_values"."id" AS t0_r0, "fact_values"."value" AS t0_r1, "fact_values"."fact_name_id" AS t0_r2, "fact_values"."host_id" AS t0_r3, "fact_values"."updated_at" AS t0_r
4, "fact_values"."created_at" AS t0_r5, "fact_names"."id" AS t1_r0, "fact_names"."name" AS t1_r1, "fact_names"."updated_at" AS t1_r2, "fact_names"."created_at" AS t1_r3, "fact_names"."compose" AS t1_r4, "fact_names"."short_name" AS t1_r5, "fact_names"."type" AS t1_r6, "f
act_names"."ancestry" AS t1_r7, "hostgroups"."id" AS t2_r0, "hostgroups"."name" AS t2_r1, "hostgroups"."created_at" AS t2_r2, "hostgroups"."updated_at" AS t2_r3, "hostgroups"."environment_id" AS t2_r4, "hostgroups"."operatingsystem_id" AS t2_r5, "hostgroups"."architectur
e_id" AS t2_r6, "hostgroups"."medium_id" AS t2_r7, "hostgroups"."ptable_id" AS t2_r8, "hostgroups"."root_pass" AS t2_r9, "hostgroups"."puppet_ca_proxy_id" AS t2_r10, "hostgroups"."use_image" AS t2_r11, "hostgroups"."image_file" AS t2_r12, "hostgroups"."ancestry" AS t2_r1
3, "hostgroups"."vm_defaults" AS t2_r14, "hostgroups"."subnet_id" AS t2_r15, "hostgroups"."domain_id" AS t2_r16, "hostgroups"."puppet_proxy_id" AS t2_r17, "hostgroups"."title" AS t2_r18, "hostgroups"."compute_profile_id" AS t2_r19, "hostgroups"."realm_id" AS t2_r20, "hos
tgroups"."grub_pass" AS t2_r21, "hostgroups"."lookup_value_matcher" AS t2_r22, "hosts_fact_values"."id" AS t3_r0, "hosts_fact_values"."name" AS t3_r1, "hosts_fact_values"."last_compile" AS t3_r2, "hosts_fact_values"."last_report" AS t3_r3, "hosts_fact_values"."updated_at
" AS t3_r4, "hosts_fact_values"."created_at" AS t3_r5, "hosts_fact_values"."root_pass" AS t3_r6, "hosts_fact_values"."architecture_id" AS t3_r7, "hosts_fact_values"."operatingsystem_id" AS t3_r8, "hosts_fact_values"."environment_id" AS t3_r9, "hosts_fact_values"."ptable_
id" AS t3_r10, "hosts_fact_values"."medium_id" AS t3_r11, "hosts_fact_values"."build" AS t3_r12, "hosts_fact_values"."comment" AS t3_r13, "hosts_fact_values"."disk" AS t3_r14, "hosts_fact_values"."installed_at" AS t3_r15, "hosts_fact_values"."model_id" AS t3_r16, "hosts_
fact_values"."hostgroup_id" AS t3_r17, "hosts_fact_values"."owner_id" AS t3_r18, "hosts_fact_values"."owner_type" AS t3_r19, "hosts_fact_values"."enabled" AS t3_r20, "hosts_fact_values"."puppet_ca_proxy_id" AS t3_r21, "hosts_fact_values"."managed" AS t3_r22, "hosts_fact_
values"."use_image" AS t3_r23, "hosts_fact_values"."image_file" AS t3_r24, "hosts_fact_values"."uuid" AS t3_r25, "hosts_fact_values"."compute_resource_id" AS t3_r26, "hosts_fact_values"."puppet_proxy_id" AS t3_r27, "hosts_fact_values"."certname" AS t3_r28, "hosts_fact_va
lues"."image_id" AS t3_r29, "hosts_fact_values"."organization_id" AS t3_r30, "hosts_fact_values"."location_id" AS t3_r31, "hosts_fact_values"."type" AS t3_r32, "hosts_fact_values"."compute_profile_id" AS t3_r33, "hosts_fact_values"."provision_method" AS t3_r34, "hosts_fa
ct_values"."otp" AS t3_r35, "hosts_fact_values"."realm_id" AS t3_r36, "hosts_fact_values"."grub_pass" AS t3_r37, "hosts_fact_values"."global_status" AS t3_r38, "hosts_fact_values"."lookup_value_matcher" AS t3_r39 FROM "fact_values" INNER JOIN "hosts" ON "hosts"."id" = "f
act_values"."host_id" AND "hosts"."type" IN ('Host::Managed') LEFT OUTER JOIN "fact_names" ON "fact_names"."id" = "fact_values"."fact_name_id" LEFT OUTER JOIN "hosts" "hosts_fact_values_join" ON "hosts_fact_values_join"."id" = "fact_values"."host_id" AND "hosts_fact_valu
es_join"."type" IN ('Host::Managed') LEFT OUTER JOIN "hostgroups" ON "hostgroups"."id" = "hosts_fact_values_join"."hostgroup_id" WHERE "fact_values"."id" IN (1119) AND (fact_names.name <> '_timestamp') AND ((("fact_values"."id" IN (SELECT "fact_values"."id" FROM         
"fact_values" 
 |         INNER JOIN "hosts" 
 |         ON "fact_values"."host_id" = "hosts"."id" 
 |         INNER JOIN "hostgroups" 
 |         ON "hosts"."hostgroup_id" = "hostgroups"."id" 
 |  WHERE "hostgroups"."name" = 'Test3' )) AND ("fact_names"."name" = 'ipaddress'))) ORDER BY "fact_values"."value" ASC
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/sqlite3-1.3.10/lib/sqlite3/database.rb:91:in `initialize'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/sqlite3-1.3.10/lib/sqlite3/database.rb:91:in `new'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/sqlite3-1.3.10/lib/sqlite3/database.rb:91:in `prepare'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/activerecord-3.2.21/lib/active_record/connection_adapters/sqlite_adapter.rb:246:in `block in exec_query'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/activerecord-3.2.21/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/activesupport-3.2.21/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/activerecord-3.2.21/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/activerecord-3.2.21/lib/active_record/connection_adapters/sqlite_adapter.rb:242:in `exec_query'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/activerecord-3.2.21/lib/active_record/connection_adapters/sqlite_adapter.rb:467:in `select'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/activerecord-3.2.21/lib/active_record/connection_adapters/abstract/database_statements.rb:18:in `select_all'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/activerecord-3.2.21/lib/active_record/connection_adapters/abstract/query_cache.rb:61:in `block in select_all'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/activerecord-3.2.21/lib/active_record/connection_adapters/abstract/query_cache.rb:75:in `cache_sql'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/activerecord-3.2.21/lib/active_record/connection_adapters/abstract/query_cache.rb:61:in `select_all'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/activerecord-3.2.21/lib/active_record/relation/finder_methods.rb:212:in `find_with_associations'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/bullet-4.14.7/lib/bullet/active_record3x.rb:45:in `find_with_associations'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/activerecord-3.2.21/lib/active_record/relation.rb:174:in `block in exec_queries'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/activerecord-3.2.21/lib/active_record/identity_map.rb:72:in `without'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/activerecord-3.2.21/lib/active_record/relation.rb:173:in `exec_queries'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/activerecord-3.2.21/lib/active_record/relation.rb:160:in `block in to_a'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/activerecord-3.2.21/lib/active_record/explain.rb:34:in `logging_query_plan'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/activerecord-3.2.21/lib/active_record/relation.rb:159:in `to_a'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/bullet-4.14.7/lib/bullet/active_record3x.rb:10:in `to_a'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/will_paginate-3.0.7/lib/will_paginate/active_record.rb:134:in `block in to_a'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/will_paginate-3.0.7/lib/will_paginate/collection.rb:96:in `create'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/will_paginate-3.0.7/lib/will_paginate/active_record.rb:133:in `to_a'
 | /home/dcleal/.rvm/gems/ruby-2.0.0-p353@foreman/gems/activerecord-3.2.21/lib/active_record/relation/finder_methods.rb:159:in `all'
 | /home/dcleal/code/foreman/foreman/app/controllers/api/v2/fact_values_controller.rb:17:in `index'

It seems to be some nasty interaction between non-admin users in the authz system (perhaps its join support in #8817), searching fact_values with host.hostgroup through host, and the .includes in the controller. Removing any of these things makes it work.


Related issues

Related to Foreman - Bug #19742: API call for fact_values is slow when user is non-admin , Foreman 1.15.0Closed2017-06-01

Associated revisions

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

fixes #11996 - use preload to avoid multiple fact/host table joins

When retrieving fact_values as a non-admin user, the my_facts scope
performs a join to the hosts table. The .includes(:host) also performs
a join via eager loading, and when both are combined with a scoped
search on host.hostgroup (joined via hosts to hostgroups), the three
joins with table renaming cause errors.

History

#1 Updated by Dominic Cleal almost 4 years ago

Equivalent queries from an admin user:

2015-09-29T14:07:17 [app] [D] Setting current user thread-local variable to admin
2015-09-29T14:07:17 [sql] [D]   SQL (0.9ms)  SELECT DISTINCT "fact_values".id FROM "fact_values" LEFT OUTER JOIN "fact_names" ON "fact_names"."id" = "fact_values"."fact_name_id" LEFT OUTER JOIN "hosts" ON "hosts"."id" = "fact_values"."host_id" AND "hosts"."type" IN ('Host::Managed') LEFT OUTER JOIN "hostgroups" ON "hostgroups"."id" = "hosts"."hostgroup_id" LEFT OUTER JOIN "hosts" "hosts_fact_values" ON "hosts_fact_values"."id" = "fact_values"."host_id" AND "hosts_fact_values"."type" IN ('Host::Managed') WHERE (fact_names.name <> '_timestamp') AND ((("fact_values"."id" IN (SELECT "fact_values"."id" FROM "fact_values" 
 |  INNER JOIN "hosts" 
 |  ON "fact_values"."host_id" = "hosts"."id" 
 |  INNER JOIN "hostgroups" 
 |  ON "hosts"."hostgroup_id" = "hostgroups"."id" 
 |  WHERE "hostgroups"."name" = 'Test3' )) AND ("fact_names"."name" = 'ipaddress'))) ORDER BY "fact_values"."value" ASC LIMIT 40 OFFSET 0
2015-09-29T14:07:17 [sql] [D]   SQL (0.7ms)  SELECT "fact_values"."id" AS t0_r0, "fact_values"."value" AS t0_r1, "fact_values"."fact_name_id" AS t0_r2, "fact_values"."host_id" AS t0_r3, "fact_values"."updated_at" AS t0_r4, "fact_values"."created_at" AS t0_r5, "fact_names"."id" AS t1_r0, "fact_names"."name" AS t1_r1, "fact_names"."updated_at" AS t1_r2, "fact_names"."created_at" AS t1_r3, "fact_names"."compose" AS t1_r4, "fact_names"."short_name" AS t1_r5, "fact_names"."type" AS t1_r6, "fact_names"."ancestry" AS t1_r7, "hostgroups"."id" AS t2_r0, "hostgroups"."name" AS t2_r1, "hostgroups"."created_at" AS t2_r2, "hostgroups"."updated_at" AS t2_r3, "hostgroups"."environment_id" AS t2_r4, "hostgroups"."operatingsystem_id" AS t2_r5, "hostgroups"."architecture_id" AS t2_r6, "hostgroups"."medium_id" AS t2_r7, "hostgroups"."ptable_id" AS t2_r8, "hostgroups"."root_pass" AS t2_r9, "hostgroups"."puppet_ca_proxy_id" AS t2_r10, "hostgroups"."use_image" AS t2_r11, "hostgroups"."image_file" AS t2_r12, "hostgroups"."ancestry" AS t2_r13, "hostgroups"."vm_defaults" AS t2_r14, "hostgroups"."subnet_id" AS t2_r15, "hostgroups"."domain_id" AS t2_r16, "hostgroups"."puppet_proxy_id" AS t2_r17, "hostgroups"."title" AS t2_r18, "hostgroups"."compute_profile_id" AS t2_r19, "hostgroups"."realm_id" AS t2_r20, "hostgroups"."grub_pass" AS t2_r21, "hostgroups"."lookup_value_matcher" AS t2_r22, "hosts_fact_values"."id" AS t3_r0, "hosts_fact_values"."name" AS t3_r1, "hosts_fact_values"."last_compile" AS t3_r2, "hosts_fact_values"."last_report" AS t3_r3, "hosts_fact_values"."updated_at" AS t3_r4, "hosts_fact_values"."created_at" AS t3_r5, "hosts_fact_values"."root_pass" AS t3_r6, "hosts_fact_values"."architecture_id" AS t3_r7, "hosts_fact_values"."operatingsystem_id" AS t3_r8, "hosts_fact_values"."environment_id" AS t3_r9, "hosts_fact_values"."ptable_id" AS t3_r10, "hosts_fact_values"."medium_id" AS t3_r11, "hosts_fact_values"."build" AS t3_r12, "hosts_fact_values"."comment" AS t3_r13, "hosts_fact_values"."disk" AS t3_r14, "hosts_fact_values"."installed_at" AS t3_r15, "hosts_fact_values"."model_id" AS t3_r16, "hosts_fact_values"."hostgroup_id" AS t3_r17, "hosts_fact_values"."owner_id" AS t3_r18, "hosts_fact_values"."owner_type" AS t3_r19, "hosts_fact_values"."enabled" AS t3_r20, "hosts_fact_values"."puppet_ca_proxy_id" AS t3_r21, "hosts_fact_values"."managed" AS t3_r22, "hosts_fact_values"."use_image" AS t3_r23, "hosts_fact_values"."image_file" AS t3_r24, "hosts_fact_values"."uuid" AS t3_r25, "hosts_fact_values"."compute_resource_id" AS t3_r26, "hosts_fact_values"."puppet_proxy_id" AS t3_r27, "hosts_fact_values"."certname" AS t3_r28, "hosts_fact_values"."image_id" AS t3_r29, "hosts_fact_values"."organization_id" AS t3_r30, "hosts_fact_values"."location_id" AS t3_r31, "hosts_fact_values"."type" AS t3_r32, "hosts_fact_values"."compute_profile_id" AS t3_r33, "hosts_fact_values"."provision_method" AS t3_r34, "hosts_fact_values"."otp" AS t3_r35, "hosts_fact_values"."realm_id" AS t3_r36, "hosts_fact_values"."grub_pass" AS t3_r37, "hosts_fact_values"."global_status" AS t3_r38, "hosts_fact_values"."lookup_value_matcher" AS t3_r39 FROM "fact_values" LEFT OUTER JOIN "fact_names" ON "fact_names"."id" = "fact_values"."fact_name_id" LEFT OUTER JOIN "hosts" ON "hosts"."id" = "fact_values"."host_id" AND "hosts"."type" IN ('Host::Managed') LEFT OUTER JOIN "hostgroups" ON "hostgroups"."id" = "hosts"."hostgroup_id" LEFT OUTER JOIN "hosts" "hosts_fact_values" ON "hosts_fact_values"."id" = "fact_values"."host_id" AND "hosts_fact_values"."type" IN ('Host::Managed') WHERE "fact_values"."id" IN (1119) AND (fact_names.name <> '_timestamp') AND ((("fact_values"."id" IN (SELECT "fact_values"."id" FROM "fact_values" 
 |  INNER JOIN "hosts" 
 |  ON "fact_values"."host_id" = "hosts"."id" 
 |  INNER JOIN "hostgroups" 
 |  ON "hosts"."hostgroup_id" = "hostgroups"."id" 
 |  WHERE "hostgroups"."name" = 'Test3' )) AND ("fact_names"."name" = 'ipaddress'))) ORDER BY "fact_values"."value" ASC
2015-09-29T14:07:17 [app] [I]   Rendered api/v2/fact_values/index.json.rabl within api/v2/layouts/index_layout (0.6ms)

#2 Updated by Dominic Cleal almost 4 years ago

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

#3 Updated by The Foreman Bot almost 4 years ago

  • Status changed from Assigned to Ready For Testing
  • Pull request https://github.com/theforeman/foreman/pull/2766 added
  • Pull request deleted ()

#4 Updated by Dominic Cleal almost 4 years ago

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

#5 Updated by Dominic Cleal almost 4 years ago

  • Legacy Backlogs Release (now unused) set to 63

#6 Updated by Marek Hulán almost 2 years ago

  • Related to Bug #19742: API call for fact_values is slow when user is non-admin , Foreman 1.15.0 added

Also available in: Atom PDF