Bug: Orgs and Locs + Job Templates are broken in 3.18 (probably 3.17 as well)

Support
1

Problem:

The changes to Organizations and Locations has completely broken jobs.

We currently have around 160 Organizations and 1,800 Locations. We assign various Orgs and Locs to users by creating their own user role, limiting what they can do and see.

We have since found a major issue in 3.18 (likely also exists in 3.17)

We have been testing this for the last 2 days, and there are some major issues breaking jobs.
For example, we have a user that has 1 Org and 1 Loc assigned to them. They are NOT an Admin. When this user navigates to run a job, the job categories and templates never load. When they navigate to the Host → Job Template page - it also never loads and times out.
This makes no sense as the user themselves only has 1 Org and 1 Loc assigned to them.

We have tested both with job templates have all Orgs and Locs assigned to them, as well as with them all unassigned. Same outcome.

The error is below. You can see a timeout of 60 seconds against the PostGres database. But watching the database at the same time, there is little to no activity, and nothing showing any sort of high processing time.

If you make the user an Admin, by clicking the Administrator checkbox, the job templates page, job categories and job templates all load almost instantly.

According to Claude helping us with this, it stated:

Foreman Role Filter “Override” Checkbox — Removed in 3.17

What it did

The Override checkbox let you decouple a filter’s org/location scope from its parent role. With it enabled, you could grant or restrict orgs/locations on a per-filter basis instead of inheriting from the role.

Why it was removed

PR #10370 (https://github.com/theforeman/foreman/pull/10370) (merged Oct 2025, shipped in Foreman 3.17) removed it to fix Redmine #37987(https://projects.theforeman.org/issues/37987) — filters with override=false weren’t properly generating their taxonomy_search field, so org/location restrictions on the role weren’t being enforced correctly. Rather than fix the complex override machinery, the developers removed it entirely and made filters unconditionally inherit taxonomies from the role.

The change deleted ~656 lines across 28 files — the column, controller action, API params, and UI elements are all gone. No deprecation warning was issued beforehand.

This being said, we have a major issue on our hands now because we only have two possible routes.

  1. Make all users admins, to allow them to run jobs. But now they can see and manipulate hosts that they shouldnt otherwise be able to.
  2. Do not allow users to run jobs.

Neither of these options are viable.

Follow up question: Now that the Override checkbox has been removed, what happens if no Organizations or Locations are explicitly assigned to a role - does it default to all orgs and locs, or none?

$ foreman-rake errors:fetch_log request_id=a6e0c865
Foreman version: 3.18.1
Plugins:

foreman-tasks 11.1.1

foreman_kubevirt 0.6.0



foreman_ovirt 2.0.3

foreman_puppet 9.1.0

foreman_remote_execution 16.5.3

foreman_salt 17.0.2

foreman_statistics 2.1.0

foreman_templates 11.0.1

foreman_vault 3.0.0

foreman_webhooks 5.0.2

2026-05-08T14:50:14 [I|app|a6e0c865] Started GET “/job_templates” for 10.222.218.3 at 2026-05-08 14:50:14 +0000
2026-05-08T14:50:14 [I|app|a6e0c865] Processing by JobTemplatesController#index as HTML
2026-05-08T14:51:14 [I|app|a6e0c865]   Rendered /usr/share/gems/gems/foreman_remote_execution-16.5.3/app/views/job_templates/index.html.erb within layouts/application (Duration: 60008.3ms | Allocations: 3681)
2026-05-08T14:51:14 [I|app|a6e0c865]   Rendered layout layouts/application.html.erb (Duration: 60008.6ms | Allocations: 3739)
2026-05-08T14:51:14 [W|app|a6e0c865] PG::QueryCanceled: ERROR:  canceling statement due to statement timeout
a6e0c865 |
2026-05-08T14:51:14 [I|app|a6e0c865] Backtrace for ‘PG::QueryCanceled: ERROR:  canceling statement due to statement timeout
a6e0c865 | ’ error (ActionView::Template::Error): PG::QueryCanceled: ERROR:  canceling statement due to statement timeout
a6e0c865 |
a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in exec_params'  a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in block (2 levels) in exec_no_cache’
a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/concurrency/share_lock.rb:187:in yield_shares'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/dependencies/interlock.rb:41:in permit_concurrent_loads’
a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/connection_adapters/postgresql_adapter.rb:767:in block in exec_no_cache'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in handle_interrupt’
a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in block in synchronize'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in handle_interrupt’
a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in synchronize'  a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/connection_adapters/abstract_adapter.rb:752:in block in log’
a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/notifications/instrumenter.rb:24:in instrument'  a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/connection_adapters/abstract_adapter.rb:743:in log’
a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/connection_adapters/postgresql_adapter.rb:766:in exec_no_cache'  a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/connection_adapters/postgresql_adapter.rb:745:in execute_and_clear’
a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/connection_adapters/postgresql/database_statements.rb:54:in exec_query'  a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/connection_adapters/abstract/database_statements.rb:560:in select’
a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/connection_adapters/abstract/database_statements.rb:66:in select_all'  a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/connection_adapters/abstract/query_cache.rb:107:in block in select_all’
a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/connection_adapters/abstract/query_cache.rb:137:in block in cache_sql'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in handle_interrupt’
a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in block in synchronize'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in handle_interrupt’
a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in synchronize'  a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/connection_adapters/abstract/query_cache.rb:128:in cache_sql’
a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/connection_adapters/abstract/query_cache.rb:107:in select_all'  a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/connection_adapters/abstract/database_statements.rb:91:in select_rows’
a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/connection_adapters/abstract/schema_statements.rb:1298:in distinct_relation_for_primary_key'  a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/relation/finder_methods.rb:427:in block in apply_join_dependency’
a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/relation.rb:962:in skip_query_cache_if_necessary'  a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/relation/finder_methods.rb:426:in apply_join_dependency’
a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/relation.rb:932:in block in exec_main_query'  a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/relation.rb:962:in skip_query_cache_if_necessary’
a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/relation.rb:928:in exec_main_query'  a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/relation.rb:914:in block in exec_queries’
a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/relation.rb:962:in skip_query_cache_if_necessary'  a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/relation.rb:908:in exec_queries’
a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/relation.rb:695:in load'  a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/relation.rb:250:in records’
a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/relation/delegation.rb:88:in each'  a6e0c865 | /usr/share/gems/gems/foreman_remote_execution-16.5.3/app/views/job_templates/index.html.erb:19:in __usr_share_gems_gems_foreman_remote_execution________app_views_job_templates_index_html_erb___2837764437716347345_28909880’
a6e0c865 | /usr/share/gems/gems/actionview-7.0.10/lib/action_view/base.rb:244:in public_send'  a6e0c865 | /usr/share/gems/gems/actionview-7.0.10/lib/action_view/base.rb:244:in _run’
a6e0c865 | /usr/share/gems/gems/actionview-7.0.10/lib/action_view/template.rb:157:in block in render'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/notifications.rb:208:in instrument’
a6e0c865 | /usr/share/gems/gems/actionview-7.0.10/lib/action_view/template.rb:361:in instrument_render_template'  a6e0c865 | /usr/share/gems/gems/actionview-7.0.10/lib/action_view/template.rb:155:in render’
a6e0c865 | /usr/share/gems/gems/actionview-7.0.10/lib/action_view/renderer/template_renderer.rb:65:in block (2 levels) in render_template'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/notifications.rb:206:in block in instrument’
a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/notifications/instrumenter.rb:24:in instrument'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/notifications.rb:206:in instrument’
a6e0c865 | /usr/share/gems/gems/actionview-7.0.10/lib/action_view/renderer/template_renderer.rb:60:in block in render_template'  a6e0c865 | /usr/share/gems/gems/actionview-7.0.10/lib/action_view/renderer/template_renderer.rb:75:in block in render_with_layout’
a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/notifications.rb:206:in block in instrument'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/notifications/instrumenter.rb:24:in instrument’
a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/notifications.rb:206:in instrument'  a6e0c865 | /usr/share/gems/gems/actionview-7.0.10/lib/action_view/renderer/template_renderer.rb:74:in render_with_layout’
a6e0c865 | /usr/share/gems/gems/actionview-7.0.10/lib/action_view/renderer/template_renderer.rb:59:in render_template'  a6e0c865 | /usr/share/gems/gems/actionview-7.0.10/lib/action_view/renderer/template_renderer.rb:11:in render’
a6e0c865 | /usr/share/gems/gems/actionview-7.0.10/lib/action_view/renderer/renderer.rb:61:in render_template_to_object'  a6e0c865 | /usr/share/gems/gems/actionview-7.0.10/lib/action_view/renderer/renderer.rb:29:in render_to_object’
a6e0c865 | /usr/share/gems/gems/actionview-7.0.10/lib/action_view/rendering.rb:117:in block in _render_template'  a6e0c865 | /usr/share/gems/gems/actionview-7.0.10/lib/action_view/base.rb:270:in in_rendering_context’
a6e0c865 | /usr/share/gems/gems/actionview-7.0.10/lib/action_view/rendering.rb:116:in _render_template'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_controller/metal/streaming.rb:216:in _render_template’
a6e0c865 | /usr/share/gems/gems/actionview-7.0.10/lib/action_view/rendering.rb:103:in render_to_body'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_controller/metal/rendering.rb:158:in render_to_body’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_controller/metal/renderers.rb:141:in render_to_body'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/abstract_controller/rendering.rb:27:in render’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_controller/metal/rendering.rb:139:in render'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_controller/metal/instrumentation.rb:22:in block (2 levels) in render’
a6e0c865 | /usr/share/ruby/benchmark.rb:308:in realtime'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/core_ext/benchmark.rb:14:in ms’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_controller/metal/instrumentation.rb:22:in block in render'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_controller/metal/instrumentation.rb:91:in cleanup_view_runtime’
a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/railties/controller_runtime.rb:34:in cleanup_view_runtime'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_controller/metal/instrumentation.rb:21:in render’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_controller/metal/implicit_render.rb:35:in default_render'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_controller/metal/basic_implicit_render.rb:7:in send_action’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/abstract_controller/base.rb:215:in process_action'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_controller/metal/rendering.rb:165:in process_action’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/abstract_controller/callbacks.rb:234:in block in process_action'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/callbacks.rb:118:in block in run_callbacks’
a6e0c865 | /usr/share/foreman/app/controllers/concerns/foreman/controller/timezone.rb:10:in set_timezone'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/callbacks.rb:127:in block in run_callbacks’
a6e0c865 | /usr/share/foreman/app/models/concerns/foreman/thread_session.rb:32:in clear_thread'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/callbacks.rb:127:in block in run_callbacks’
a6e0c865 | /usr/share/foreman/app/controllers/concerns/foreman/controller/topbar_sweeper.rb:12:in set_topbar_sweeper_controller'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/callbacks.rb:127:in block in run_callbacks’
a6e0c865 | /usr/share/gems/gems/audited-5.8.0/lib/audited/sweeper.rb:16:in around'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/callbacks.rb:127:in block in run_callbacks’
a6e0c865 | /usr/share/gems/gems/audited-5.8.0/lib/audited/sweeper.rb:16:in around'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/callbacks.rb:127:in block in run_callbacks’
a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/callbacks.rb:138:in run_callbacks'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/abstract_controller/callbacks.rb:233:in process_action’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_controller/metal/rescue.rb:23:in process_action'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_controller/metal/instrumentation.rb:67:in block in process_action’
a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/notifications.rb:206:in block in instrument'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/notifications/instrumenter.rb:24:in instrument’
a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/notifications.rb:206:in instrument'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_controller/metal/instrumentation.rb:66:in process_action’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_controller/metal/params_wrapper.rb:259:in process_action'  a6e0c865 | /usr/share/gems/gems/activerecord-7.0.10/lib/active_record/railties/controller_runtime.rb:27:in process_action’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/abstract_controller/base.rb:151:in process'  a6e0c865 | /usr/share/gems/gems/actionview-7.0.10/lib/action_view/rendering.rb:39:in process’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_controller/metal.rb:188:in dispatch'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_controller/metal.rb:251:in dispatch’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/routing/route_set.rb:49:in dispatch'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/routing/route_set.rb:32:in serve’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/journey/router.rb:50:in block in serve'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/journey/router.rb:32:in each’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/journey/router.rb:32:in serve'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/routing/route_set.rb:852:in call’
a6e0c865 | /usr/share/gems/gems/apipie-dsl-2.6.2/lib/apipie_dsl/static_dispatcher.rb:67:in call'  a6e0c865 | /usr/share/gems/gems/apipie-rails-1.5.0/lib/apipie/static_dispatcher.rb:72:in call’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/static.rb:23:in call'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/static.rb:23:in call’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/static.rb:23:in call'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/static.rb:23:in call’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/static.rb:23:in call'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/static.rb:23:in call’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/static.rb:23:in call'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/static.rb:23:in call’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/static.rb:23:in call'  a6e0c865 | /usr/share/foreman/lib/foreman/middleware/libvirt_connection_cleaner.rb:9:in call’
a6e0c865 | /usr/share/foreman/lib/foreman/middleware/telemetry.rb:10:in call'  a6e0c865 | /usr/share/gems/gems/apipie-rails-1.5.0/lib/apipie/middleware/checksum_in_headers.rb:27:in call’
a6e0c865 | /usr/share/gems/gems/rack-2.2.4/lib/rack/tempfile_reaper.rb:15:in call'  a6e0c865 | /usr/share/gems/gems/rack-2.2.4/lib/rack/etag.rb:27:in call’
a6e0c865 | /usr/share/gems/gems/rack-2.2.4/lib/rack/conditional_get.rb:27:in call'  a6e0c865 | /usr/share/gems/gems/rack-2.2.4/lib/rack/head.rb:12:in call’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/http/permissions_policy.rb:38:in call'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/http/content_security_policy.rb:39:in call’
a6e0c865 | /usr/share/foreman/lib/foreman/middleware/logging_context_session.rb:22:in call'  a6e0c865 | /usr/share/gems/gems/rack-2.2.4/lib/rack/session/abstract/id.rb:266:in context’
a6e0c865 | /usr/share/gems/gems/rack-2.2.4/lib/rack/session/abstract/id.rb:260:in call'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/cookies.rb:704:in call’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/callbacks.rb:27:in block in call'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/callbacks.rb:99:in run_callbacks’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/callbacks.rb:26:in call'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/debug_exceptions.rb:28:in call’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/show_exceptions.rb:29:in call'  a6e0c865 | /usr/share/gems/gems/railties-7.0.10/lib/rails/rack/logger.rb:40:in call_app’
a6e0c865 | /usr/share/gems/gems/railties-7.0.10/lib/rails/rack/logger.rb:27:in call'  a6e0c865 | /usr/share/gems/gems/sprockets-rails-3.5.2/lib/sprockets/rails/quiet_assets.rb:17:in call’
a6e0c865 | /usr/share/foreman/lib/foreman/middleware/logging_context_request.rb:11:in call'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/remote_ip.rb:93:in call’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/request_id.rb:26:in call'  a6e0c865 | /usr/share/gems/gems/rack-2.2.4/lib/rack/method_override.rb:24:in call’
a6e0c865 | /usr/share/gems/gems/rack-2.2.4/lib/rack/runtime.rb:22:in call'  a6e0c865 | /usr/share/gems/gems/activesupport-7.0.10/lib/active_support/cache/strategy/local_cache_middleware.rb:29:in call’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/executor.rb:14:in call'  a6e0c865 | /usr/share/gems/gems/rack-2.2.4/lib/rack/sendfile.rb:110:in call’
a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/ssl.rb:77:in call'  a6e0c865 | /usr/share/gems/gems/actionpack-7.0.10/lib/action_dispatch/middleware/host_authorization.rb:131:in call’
a6e0c865 | /usr/share/gems/gems/secure_headers-7.1.0/lib/secure_headers/middleware.rb:11:in call'  a6e0c865 | /usr/share/gems/gems/railties-7.0.10/lib/rails/engine.rb:531:in call’
a6e0c865 | /usr/share/gems/gems/railties-7.0.10/lib/rails/railtie.rb:226:in public_send'  a6e0c865 | /usr/share/gems/gems/railties-7.0.10/lib/rails/railtie.rb:226:in method_missing’
a6e0c865 | /usr/share/gems/gems/rack-2.2.4/lib/rack/urlmap.rb:74:in block in call'  a6e0c865 | /usr/share/gems/gems/rack-2.2.4/lib/rack/urlmap.rb:58:in each’
a6e0c865 | /usr/share/gems/gems/rack-2.2.4/lib/rack/urlmap.rb:58:in call'  a6e0c865 | /usr/share/gems/gems/puma-6.6.1/lib/puma/configuration.rb:279:in call’
a6e0c865 | /usr/share/gems/gems/puma-6.6.1/lib/puma/request.rb:99:in block in handle_request'  a6e0c865 | /usr/share/gems/gems/puma-6.6.1/lib/puma/thread_pool.rb:390:in with_force_shutdown’
a6e0c865 | /usr/share/gems/gems/puma-6.6.1/lib/puma/request.rb:98:in handle_request'  a6e0c865 | /usr/share/gems/gems/puma-6.6.1/lib/puma/server.rb:472:in process_client’
a6e0c865 | /usr/share/gems/gems/puma-6.6.1/lib/puma/server.rb:254:in block in run'  a6e0c865 | /usr/share/gems/gems/puma-6.6.1/lib/puma/thread_pool.rb:167:in block in spawn_thread’
a6e0c865 | /usr/share/gems/gems/logging-2.4.0/lib/logging/diagnostic_context.rb:474:in `block in create_with_logging_context’
2026-05-08T14:51:14 [I|app|a6e0c865]   Rendered common/500.html.erb within layouts/application (Duration: 0.9ms | Allocations: 521)
2026-05-08T14:51:14 [I|app|a6e0c865]   Rendered layouts/base.html.erb (Duration: 3.9ms | Allocations: 2200)
2026-05-08T14:51:14 [I|app|a6e0c865]   Rendered layout layouts/application.html.erb (Duration: 5.3ms | Allocations: 2983)
2026-05-08T14:51:14 [I|app|a6e0c865] Completed 500 Internal Server Error in 60083ms (Views: 6.0ms | ActiveRecord: 60033.3ms | Allocations: 27207)
1 Like
2

Urgent bug filed: Bug #39304: Orgs and Locs + Job Templates are broken in 3.18 (probably 3.17 as well) - Foreman

1 Like
3

Thanks for the detailed report @singularity001 .

@MariaAga @aruzicka @ofedoren do you need more information here to understand what’s going on? Is the use case described here something that came up when PR 10370 was out? Is there a different workaround that you see other than the 2 presented, or perhaps a better permissions layout to suggest?

1 Like
4

What is the exact list of roles and permissions granted through those roles that the users have?

5

Sorry for the output, its the only one I could find that wouldn’t truncate the output.

root@10-222-206-152:foreman-ui-01:[~]:
$ hammer user info --id 153
Id:                    153
Login:                 bglascoc
Name:                  Redacted
Email:                 redacted@redacted.com
Admin:                 no
Disabled:              no
Last login:            2026/04/14 19:18:16
Authorized by:         redacted
Email enabled:         no
Effective admin:       no
Locale:                default
Timezone:              default
Description:
Default organization:
Default location:
Roles:
    bglascoc
User groups:

Inherited User groups:

Locations:
    ES - Enterprise Data Platform (10900) (533517b9)
    ES - Kafka (12360) (595fae72)
Organizations:
    ES - Managed Services (676de612)
    Technology Infrastructure (676de612)
Created at:            2026/01/14 15:49:28
Updated at:            2026/01/14 17:39:22

$ hammer --output csv filter list | grep bglascoc
13329,Host,facts.ssnc_cloud_subproject_id=subproject-abc57f78-ed95-4a8a-8404-b39acfe779fd OR facts.ssnc_cloud_subproject_id=subproject-684ebae7-2e68-481c-85a4-5e6b60419059 OR facts.ssnc_cloud_subproject_id=subproject-648ec935-ff97-49bf-aa80-6dea5feb7109 OR facts.ssnc_cloud_subproject_id=subproject-3ca346cf-0e66-46b7-b5c0-7e49e90cdb78 OR facts.ssnc_cloud_subproject_id=subproject-28d6aa66-1f5e-4ef2-91fa-08d78e73aed9 OR facts.ssnc_cloud_subproject_id=subproject-8e108dae-e68f-422a-a085-3dd8f5ffe200 OR facts.ssnc_cloud_subproject_id=subproject-fb027f76-0a32-46f8-aa34-268d8e9a5b35 OR facts.ssnc_cloud_subproject_id=subproject-01b2702e-2a12-4394-a0b1-fe5339084c89 OR facts.ssnc_cloud_subproject_id=subproject-733ff590-6833-4cd0-804f-a078d344d55c OR facts.ssnc_cloud_subproject_id=subproject-766c64cb-577a-4e92-83c6-4287468446cf OR facts.ssnc_cloud_subproject_id=subproject-2a81b06c-580c-49d9-98bc-02fd0b7306da OR facts.ssnc_cloud_subproject_id=subproject-8ffc0fd6-2e2d-4cd8-84cc-cb28a7b8328b OR facts.ssnc_cloud_subproject_id=subproject-08973480-5bc2-48b3-b18b-97e815637c60 OR facts.ssnc_cloud_subproject_id=subproject-85784d99-0dac-4bc8-a403-23fdf1334121 OR facts.ssnc_cloud_subproject_id=subproject-7ba9f756-0ed3-4308-9790-73067f1e81f6 OR facts.ssnc_cloud_subproject_id=subproject-a11b3e46-d7f3-4f0a-a846-f8d2db6f108e OR facts.ssnc_cloud_subproject_id=subproject-e69edbf0-19d0-4208-ab97-3fd25b2f2384 OR facts.ssnc_cloud_subproject_id=subproject-5f843144-0968-4d57-a4ad-37759c50ce85 OR facts.ssnc_cloud_subproject_id=subproject-e132939a-6b85-429e-a134-bb0b326c78e1 OR facts.ssnc_cloud_subproject_id=subproject-3616bf0a-a5d4-42de-9ca9-3ce9f7105e77,bglascoc,"edit_hosts, view_hosts, create_hosts"
13326,Audit,none,bglascoc,view_audit_logs
13327,Bookmark,none,bglascoc,"create_bookmarks, edit_bookmarks"
13328,(Miscellaneous),none,bglascoc,"view_statistics, access_dashboard, view_statuses"
13330,JobTemplate,job_category  !=  SaltReleaseTestBed,bglascoc,"view_job_templates, create_job_templates, edit_job_templates, destroy_job_templates, lock_job_templates"
13331,RemoteExecutionFeature,none,bglascoc,"view_remote_execution_features, edit_remote_execution_features"
13332,JobInvocation,job_category  !=  SaltReleaseTestBed,bglascoc,"create_job_invocations, view_job_invocations, cancel_job_invocations"
13333,TemplateInvocation,none,bglascoc,"view_template_invocations, create_template_invocations, filter_autocompletion_for_template_invocation"
13334,SmartProxy,none,bglascoc,"view_smart_proxies_salt_autosign, view_smart_proxies_salt_keys, view_smart_proxies"
13335,ReportTemplate,none,bglascoc,"view_report_templates, create_report_templates, edit_report_templates, generate_report_templates"
13336,ForemanSalt::SaltEnvironment,none,bglascoc,view_salt_environments
13337,ForemanSalt::SaltVariable,none,bglascoc,view_salt_variables
13338,ForemanSalt::SaltModule,none,bglascoc,view_salt_modules
13339,Hostgroup,none,bglascoc,view_hostgroups
13340,ForemanTasks::Task,none,bglascoc,view_foreman_tasks
13341,Architecture,none,bglascoc,view_architectures
13342,AuthSource,none,bglascoc,view_authenticators
13343,ConfigReport,none,bglascoc,view_config_reports
13344,Domain,none,bglascoc,view_domains
13345,FactValue,none,bglascoc,view_facts
13346,Filter,none,bglascoc,view_filters
13347,HttpProxy,none,bglascoc,view_http_proxies
13348,KeyPair,none,bglascoc,view_keypairs
13349,Location,"name  !=   ""Location Not Set""",bglascoc,view_locations
13350,LookupValue,none,bglascoc,view_lookup_values
13351,MailNotification,none,bglascoc,view_mail_notifications
13352,Model,none,bglascoc,view_models
13353,Operatingsystem,none,bglascoc,view_operatingsystems
13354,Parameter,none,bglascoc,view_params
13355,PersonalAccessToken,none,bglascoc,view_personal_access_tokens
13356,Subnet,none,bglascoc,view_subnets
13357,Usergroup,none,bglascoc,view_usergroups
13358,User,admin != true,bglascoc,view_users
13359,ForemanTasks::RecurringLogic,none,bglascoc,"create_recurring_logics, view_recurring_logics, edit_recurring_logics"
13360,ForemanPuppet::PuppetclassLookupKey,none,bglascoc,view_external_parameters
6

And what roles is the bglascoc role assigned to?

7

Do you mean this? Each user gets their own role.

root@10-222-206-152:foreman-ui-01:[~]:
$ hammer role info --id 428
Id:            428
Name:          bglascoc
Builtin:       no
Description:   bglascoc
Locations:
    ES - Enterprise Data Platform (10900) (533517b9)
    ES - Kafka (12360) (595fae72)
Organizations:
    ES - Managed Services (676de612)
    Technology Infrastructure (676de612)
8

With https://github.com/theforeman/foreman/pull/10370 (and Fixes #38731 - User taxonomy checks during permission checks by adamruzicka · Pull Request #10701 · theforeman/foreman · GitHub ) a role without any organizations or locations should grant the permissions universally, within the scope of the user’s organizations and locations. So if user belongs to organization X and is given a role with view_hosts permission without any organizations assigned, that user will only be able to see hosts in organization X.

Iirc if the query was aborted postgres should log it. What was the query being processed at the time?

Well, yes, permission checks are skipped completely for admin

Now that the org/loc scoping enforcement works a bit differently than it did before, would it be possible for you to move to a model with fewer roles shared among users?

Just having that many organizations and locations shouldn’t be a problem on its own, but combining that with over 400 roles and 13k filters could be problematic.

1 Like
9

Give me a few days to get this for you, I will need to setup a meeting with the DB team.

Now that the org/loc scoping enforcement works a bit differently than it did before, would it be possible for you to move to a model with fewer roles shared among users?

Generally, no. But I also don’t quite see how having a single role per person is any more or less responsive/faster than multiple people sharing a single role. Either way those roles need to be looked up. We are a private cloud company, with each person having specific access to certain servers in certain Orgs/Locs - we also have expanded the login scripts to account for people that also have to enable BreakGlass approval on specific locations. That is the real reason behind an individual role for each person. Because we need to be able to control each individual that may or may not open BreakGlass specifically for themselves.

Just having that many organizations and locations shouldn’t be a problem on its own, but combining that with over 400 roles and 13k filters could be problematic.

You would certainly know better than me. But I dont quite understand how it can matter. If each person has their own role, and within that role they have say 20-200 filters, isn’t the taxonomy just looking up what that single user can do? This was all working fine in 3.16 - so it seems to be whatever recent changes were made, have broken it.

All this being said, Im guessing you dont see anything quite yet thats sticking out at you? I can get the database queries, but beyond that, anything else?

1 Like
10

We aren’t seeing anything crazy in the postgres logs. They are trying to pull more data, but because we have 150,000 facts being added hourly, 1000s of servers being added and removed hourly, and 1000s of api calls and users logging in, its very hard to decipher our specific calls. Mind you none of the aforementioned cause problems outside of what we’re seeing here.

We also spent some time thinking and talking about this. However, we realized that for some of the users we are testing that have this problem, they have our Role of “Power Users” - they have 1 single filter and thats it. They are essentially admins, just without the admin checkbox (yes I understand there is a major difference). But in this case, 1 user filter. They have the same problem. Jobs page takes 45+ seconds to load. So it does appear that even without host/org/loc filters the issue exist.

root@10-222-206-152:foreman-ui-01:[~]:
$ hammer role info --id 536
Id:          536
Name:        Power User Role
Builtin:     no
Description: DO NOT DELETE - User role template


root@10-222-206-152:foreman-ui-01:[~]:
$ hammer --output csv filter list | grep Power
13855,ForemanTasks::Task,none,Power User Role,view_foreman_tasks
13857,AuthSource,none,Power User Role,view_authenticators
13859,Domain,none,Power User Role,view_domains
13860,FactValue,none,Power User Role,view_facts
13862,HttpProxy,none,Power User Role,view_http_proxies
13871,Subnet,none,Power User Role,view_subnets
13873,User,admin != true,Power User Role,view_users
13841,Audit,none,Power User Role,view_audit_logs
13842,Bookmark,none,Power User Role,"create_bookmarks, edit_bookmarks"
13843,(Miscellaneous),none,Power User Role,"view_statistics, access_dashboard, view_statuses"
13847,JobInvocation,none,Power User Role,"create_job_invocations, view_job_invocations, execute_jobs_on_infrastructure_hosts, cancel_job_invocations"
13846,RemoteExecutionFeature,none,Power User Role,"view_remote_execution_features, edit_remote_execution_features"
13848,TemplateInvocation,none,Power User Role,"view_template_invocations, create_template_invocations, filter_autocompletion_for_template_invocation"
13851,ForemanSalt::SaltEnvironment,none,Power User Role,view_salt_environments
13852,ForemanSalt::SaltVariable,none,Power User Role,view_salt_variables
13853,ForemanSalt::SaltModule,none,Power User Role,view_salt_modules
13856,Architecture,none,Power User Role,view_architectures
13858,ConfigReport,none,Power User Role,view_config_reports
13861,Filter,none,Power User Role,view_filters
13863,KeyPair,none,Power User Role,view_keypairs
13845,JobTemplate,none,Power User Role,"view_job_templates, create_job_templates, edit_job_templates, destroy_job_templates, lock_job_templates"
13865,LookupValue,none,Power User Role,view_lookup_values
13866,MailNotification,none,Power User Role,view_mail_notifications
13867,Model,none,Power User Role,view_models
13868,Operatingsystem,none,Power User Role,view_operatingsystems
13869,Parameter,none,Power User Role,view_params
13870,PersonalAccessToken,none,Power User Role,view_personal_access_tokens
13849,SmartProxy,none,Power User Role,"view_smart_proxies_salt_autosign, view_smart_proxies_salt_keys, view_smart_proxies"
13872,Usergroup,none,Power User Role,view_usergroups
13874,ForemanTasks::RecurringLogic,none,Power User Role,"create_recurring_logics, view_recurring_logics, edit_recurring_logics"
13850,ReportTemplate,none,Power User Role,"view_report_templates, create_report_templates, edit_report_templates, generate_report_templates"
13854,Hostgroup,none,Power User Role,view_hostgroups
13875,ForemanPuppet::PuppetclassLookupKey,none,Power User Role,view_external_parameters
13864,Location,none,Power User Role,view_locations
13844,Host,none,Power User Role,"edit_hosts, view_hosts, create_hosts"

Only actual filter:

13873,User,admin != true,Power User Role,view_users

Still working to get SQL stuffs, but that route is going to be hard with the amount of data coming and going, and many queries arent helpful, they just show the variables in the query, eg; $1, $2 etc.

11

I don’t want to get your hopes up just yet, but I might have a winner - Fixes #39209 - Drop superfluous includes by adamruzicka · Pull Request #10898 · theforeman/foreman · GitHub . Would you be willing to take it for a spin? If all goes well, I’d propose it to be picked for next 3.18.z.

By this small change, on my much less loaded instance, it went from almost 3 seconds to 0.25 seconds.

Before

2026-05-12T14:23:36 [I|app|f7f94160] Completed 200 OK in 2903ms (Views: 2298.5ms | ActiveRecord: 421.2ms | Allocations: 1253415)

After

2026-05-12T14:21:47 [I|app|ef3321b1] Completed 200 OK in 251ms (Views: 20.7ms | ActiveRecord: 50.0ms | Allocations: 78326)

The time needed grows with template_count * organization_count * location_count, which I expect nets you a pretty large number on your instance.

12

:raising_hands::folded_hands:

I’ll give it a try right now.
I’ve spent all day on this. I thought I really had it when I saw the audits table was at 57,063,554 entries. I thought “Oh this is it for sure. I bet for every Org/Loc and for every host in their Org/Loc that person has access to, it needs to load all possible audit records.” But I mass purged them all, and the same issue occurs with even 3000 audit records.

Trying the above now. Will report back, if not today, tomorrow am (US).

Thanks @aruzicka - even if this doesn’t fix it, thank you so much for helping us with this.

13

I want to say it is both exceptionally faster, but also marginally :slight_smile:

It has gone from an avg page load of 45+ (to timing out) down to about 12-25 seconds. So an exceptional improvement, but waiting for an avg of 15-20 seconds for the job categories and templates drop down is annoying our users.

I am working on this full time now, so Ill see where I get. Im looking into the 13,000 filters as well, because there is just no way we have that many, so we are confused why the high count.

14

Its probably just the pure number of taxonomies the users have:

     type     | count
--------------+-------
 Location     |  1814
 Organization |   161
(2 rows)

     type     | ancestry | count
--------------+----------+-------
 Location     |          |  1814
 Organization |          |   161
(2 rows)

foreman=# -- which roles does ajackman have and how many taxonomies each
SELECT r.id, r.name,
       (SELECT count(*) FROM taxable_taxonomies tt
         WHERE tt.taxable_type='Role' AND tt.taxable_id=r.id) AS role_taxonomies
FROM user_roles ur
JOIN roles r ON r.id = ur.role_id
WHERE ur.owner_type='User' AND ur.owner_id=28;
 id  |      name       | role_taxonomies
-----+-----------------+-----------------
   1 | Default role    |               0
 466 | ajackman        |              36
 536 | Power User Role |               0
(3 rows)

foreman=# SELECT count(*) FROM taxable_taxonomies WHERE taxable_type='User' AND taxable_id=28;
 count
-------
  1975

So what we’re seeing is the 3.17/18 Override removal playing out. Users who used to have unlimited: true now need an explicit list of every org/loc. 1,975 of them. The IN list is massive. Postgres handles IN lists OK up to a few hundred, but 1,975 pushes it into sequential-scan territory on the join plan unless stats are perfect.

Eek.

15

6 hours in debugging with Claude. I think we’ve proven its not a SQL issue. It did end up patching a taxonomy initializer which signifincally reduced SQL query time. However, still no change in browser loading. About 15-25 seconds to load the job_templates page, even with this patch. Sharing for visibility:

Root cause

Taxonomix::ClassMethods#get_taxonomy_ids called by Authorizer#used_taxonomy_ids_for:

def get_taxonomy_ids(taxonomy, method)
  Array(taxonomy).map { |t| t.send(method) + t.ancestor_ids }.flatten.uniq
end

One subtree_ids + one ancestor_ids DB call per taxonomy per authorization check. With the current test user having 1,975 taxonomies × 2 types (orgs + locs) = ~3,960 identical ancestry-lookup queries per .authorized(...) call.

Patch

Foreman initializer /usr/share/foreman/config/initializers/taxonomy_child_ids_batch.rb that replaces get_taxonomy_ids with a single batched SQL query using ancestry LIKE predicates joined by OR. Cost is O(1) DB round-trips regardless of taxonomy count.

Full source:

$ cat /usr/share/foreman/config/initializers/taxonomy_child_ids_batch.rb
# /usr/share/foreman/config/initializers/taxonomy_child_ids_batch.rb
#
# Replaces Taxonomix::ClassMethods#get_taxonomy_ids with a batched version
# that resolves all taxonomy descendants in one SQL query instead of N.
#
# Original: loops over each taxonomy calling `t.subtree_ids + t.ancestor_ids`,
# producing 2N DB queries. With N=1,975 user-level taxonomies, that's ~3,950
# queries per .authorized(...) call -> 7-8 second page loads.
#
# Batched: one SELECT with an OR-chain of ancestry predicates, so cost is
# O(1) DB round-trips regardless of N.

Rails.application.config.to_prepare do
  Taxonomix::ClassMethods.module_eval do
    unless method_defined?(:get_taxonomy_ids_without_batch)
      alias_method :get_taxonomy_ids_without_batch, :get_taxonomy_ids
      def get_taxonomy_ids(taxonomy, method)
        arr = Array(taxonomy)
        return [] if arr.empty?
        ids = arr.map(&:id)
        where_clause = arr.map { "ancestry LIKE ? OR ancestry = ?" }.join(' OR ')
        bindings = arr.flat_map { |t| ["#{t.id}/%", t.id.to_s] }
        descendants = Taxonomy.where(where_clause, *bindings).pluck(:id)
        ancestors = arr.flat_map { |t| t.ancestry.to_s.split('/').map(&:to_i).reject(&:zero?) }
        (ids + descendants + ancestors).uniq
      end
    end
  end
end

Results (console, JobTemplate.authorized(:view_job_templates).to_a)

Unpatched Patched
Elapsed 7.99s 0.40s
Queries against taxonomies ~3,960 ~20
Returned template count 24 24 ✓

Results (production.log, GET /job_templates)

Metric Before After
Total 12,590 ms 1,477 ms
Views 4,929 ms 615 ms
ActiveRecord 3,782 ms 146 ms
Allocations 7,469,799 1,200,081

Now, to figure out still what is taking so long.

16

Im an idiot. Someone enabled our second server in our load balanced pair. I disabled the services on it so requests were only hitting the server with the above patch, and I can say “it works”.

Now seeing avg page load time around 1.5-3.5 seconds.

1 Like
17

Foreman Job Templates Slowness — Investigation Summary

Problem

Non-admin users with many org/location taxonomies experienced 15-60s page loads (and 500 timeouts) on /job_templates, /hosts, and /job_invocations/new. Admin loads were 1-2s.

Example user ajackman: 1,975 direct user-level taxonomies (1,814 Locations + 161 Organizations).

Root cause

1. N+1 in Taxonomix::ClassMethods#get_taxonomy_ids

def get_taxonomy_ids(taxonomy, method)
  Array(taxonomy).map { |t| t.send(method) + t.ancestor_ids }.flatten.uniq
end

Called by Authorizer on every .authorized(...). Each taxonomy in the user’s scope ran two separate DB queries (subtree_ids + ancestor_ids). For ajackman: 1,975 taxonomies × 2 methods × 2 taxonomy types = ~3,960 identical SQL queries per page load.

No open upstream Foreman issue tracks this specifically. PR #7451 added a 2-min Rails.cache wrapper but the default MemoryStore is per-Puma-worker, so most requests still pay the full cost.

Fixes applied

1. Monkey-patch initializer

/usr/share/foreman/config/initializers/taxonomy_child_ids_batch.rb replaces Taxonomix::ClassMethods#get_taxonomy_ids with a single batched SQL query using ancestry LIKE predicates joined by OR. Cost is O(1) DB round-trips regardless of taxonomy count.

Deployed to foreman-ui-01 and foreman-ui-02.

Results

Console benchmark (JobTemplate.authorized(:view_job_templates).to_a for ajackman):

Before After
Elapsed 7.99s 0.40s
Taxonomies queries ~3,960 ~20
Template count 24 24 ✓

Production (GET /job_templates for ajackman):

Metric Before After
Total 12,590 ms ~1,500 ms
Views 4,929 ms 615 ms
ActiveRecord 3,782 ms 146 ms
Allocations 7,469,799 1,200,081

~8x faster overall, 26x faster AR, 6x fewer allocations.

@aruzicka fixed, see above. Now to see if I can get the Hosts page to load faster.

1 Like
18

Yeah, I was looking at that general area, glad to hear even such a relatively simple approach would help and that we wouldn’t need to go overboard with it.

Filed Bug #39323: User taxonomy lookup is inefficient, number of queries grows with the number of user's assigned taxonomies - Foreman to have your fix brought in.

1 Like
19

Hi @singularity001. I dug into this further and put together a proposed fix here: pablomh/foreman#10

The remaining non-admin slowdown on /api/hosts and /api/job_templates turned out not to be the taxonomy expansion itself anymore, but the work Foreman was still doing per matching RBAC filter before building the final authorization condition.

In this case, many matching filters collapse down to the same effective taxonomy scope, but the grouped authorization path was still repeatedly checking per-filter granularity/resource metadata across thousands of Filter records. That Ruby-side overhead was dominating the request time.

The patch in the PR fixes that by:

  • grouping granular filters by effective taxonomy scope

  • normalizing equivalent taxonomy predicates so they can be grouped together

  • memoizing the normalized grouping key

  • deriving granularity once from the target resource_class instead of re-evaluating it on every Filter

On our test system, this was enough by itself to restore the problematic endpoints:

  • /api/hosts: ~6.7s → ~0.23s

  • /api/hosts?per_page: ~6.6s → ~0.22s

  • /api/job_templates: ~5.2s → ~0.36s

  • /api/job_templates?search: ~5.1s → ~0.25s

There is also a separate /api/filters bottleneck, but that appears to be independent of the /api/hosts and /api/job_templates issue and needs its own fix.

If you have an environment that reproduces the original issue, it would be very helpful if you could test pablomh/foreman#10 and report back with results. If it behaves well outside our setup too, that should give us enough confidence to start working on upstreaming it.

20

For anyone who wants immediate relief without applying the full patch, one smaller workaround that helps a lot is to preload the filterings -> permission association in app/services/authorizer.rb:

# Before
base = user.filters.joins(:permissions).where(permissions: { resource_type: resource_name(resource_class) })

# After
base = user.filters.joins(:permissions).preload(filterings: :permission).where(permissions: { resource_type: resource_name(resource_class) })

That avoids the per-filter lazy loading behind Filter#granular? / resource_type, so Foreman no longer issues one association query per matching filter.

That said, it is only a workaround: it does not group duplicate taxonomy predicates, so it is not a replacement for the full fix in the PR.