Bug #33763
open
Authorization generates inefficient SQL query for taxonomy-overriden permissions
Description
Cloned from https://bugzilla.redhat.com/show_bug.cgi?id=1985101
Description of problem:
Job execution lookups prior to firing off a new job was suddenly very slow, taking upwards of 5 minutes to instantiate a single job execution.
After creating a new index on the foreman database, job execution instantiation went back to the usual duration of 3 seconds.
The index was created on the taxable_taxonomies table with the command below:
create unique index taxable_id_id on taxable_taxonomies (taxable_id, id);Version-Release number of selected component (if applicable):
satellite-6.8.5-1.el7sat.noarch
How reproducible:
100% of times when invoking a new job.
Steps to Reproduce:
1. Invoke new job on the web UI or through hammer.
Actual results:
Takes upwards of 5 minutes to create the job.
Expected results:
Few seconds to create the job.
Additional info:
The new index was created after examining the SQL queries being run by foreman while invoking the job. The new DB index changed the job template lookup significantly: the sql query now avoids nested loops and does merge joins and hashed joins instead.
Updated by Tomer Brisker over 2 years ago
- Category set to Users, Roles and Permissions
Updated by Tomer Brisker over 2 years ago
I dug a little deeper into this, from the SQL it appears this query comes from https://github.com/theforeman/foreman_remote_execution/blob/master/app/models/job_invocation_composer.rb#L465 which in turn calls https://github.com/theforeman/foreman_remote_execution/blob/master/app/models/job_invocation_composer.rb#L457.
Now it would appear that the problematic section of the query is generated by the authorizer, probably somewhere inside https://github.com/theforeman/foreman/blob/ea27aadfdf33bf12103c178a2dec7a6e8c48b3ad/app/services/authorizer.rb#L27.
I managed to reproduce on develop by granting the user a role with view_templates permission that has an override to the taxonomies set. Looks like one of the join clauses isn't generated properly when there is a polymorphic relation involved. Will continue investigating.
The SQL that is generated looks like:
SELECT "templates"."job_category" FROM "templates" LEFT OUTER JOIN "taxable_taxonomies" ON "taxable_taxonomies"."taxable_type" = 'JobTemplate' AND "taxable_taxonomies"."taxable_id" = "templates"."id" LEFT OUTER JOIN "taxonomies" ON "taxonomies"."type" = 'Organization' AND "taxonomies"."id" = "taxable_taxonomies"."taxonomy_id" LEFT OUTER JOIN "taxable_taxonomies" "taxable_taxonomies_templates_join" ON "taxable_taxonomies_templates_join"."taxable_type" = 'JobTemplate' AND "taxable_taxonomies_templates_join"."taxable_id" = "templates"."id" LEFT OUTER JOIN "taxonomies" "locations_templates" ON "locations_templates"."type" = 'Location' AND "locations_templates"."id" = "taxable_taxonomies_templates_join"."taxonomy_id" WHERE "templates"."type" = 'JobTemplate' AND (templates.id IN (103,....,639)) AND ((("templates"."name" ILIKE '%aaa%') AND ("templates"."id" IN (SELECT "templates"."id" FROM "templates" INNER JOIN "taxable_taxonomies"
ON "templates"."id" = "taxable_taxonomies"."taxable_id"
INNER JOIN "taxonomies"
ON "taxable_taxonomies"."taxonomy_id" = "taxonomies"."id"
WHERE "taxonomies"."id" IN ('1','112','111') )) AND ("templates"."id" IN (SELECT "templates"."id" FROM "templates" INNER JOIN "taxable_taxonomies"
ON "templates"."id" = "taxable_taxonomies"."taxable_id"
INNER JOIN "taxonomies"
ON "taxable_taxonomies"."taxonomy_id" = "taxonomies"."id"
WHERE "taxonomies"."id" IN ('3','2',...,'64') )))) AND "templates"."snippet" = 'false' GROUP BY "templates"."job_category" ORDER BY "templates"."job_category" ASC ;
Specifically, the part of the query that shouldn't occur are these inner joins:
INNER JOIN "taxable_taxonomies" ON "templates"."id" = "taxable_taxonomies"."taxable_id"
which should instead be:
INNER JOIN "taxable_taxonomies" ON "templates"."id" = "taxable_taxonomies"."taxable_id" AND "taxable_taxonomies"."taxable_type" = 'JobTemplate'
(similar to how the left joins in the start of the query look)
Updated by Tomer Brisker over 2 years ago
Looks like an issue with scoped search incorrectly handling the polymorphic STI for templates. At
https://github.com/wvanbergen/scoped_search/blob/master/lib/scoped_search/query_builder.rb#L290 we get the chain of tables which is
["templates", "taxable_taxonomies", "taxonomies"], but the scoped search definition is on the child classes, causing https://github.com/wvanbergen/scoped_search/blob/master/lib/scoped_search/query_builder.rb#L298 to incorrectly miss the polymorphic type field.
A trivial reproducer to generate the same sql:
ReportTemplate.search_for('organization_id = 1')