Bug 1370407

Created attachment 1194225 [details]
Set of patches for workaround this issue

Description of problem:
  When customer use custom roles as it is described in this document: https://access.redhat.com/documentation/en-US/Red_Hat_Satellite/6.1/html/User_Guide/sect-Red_Hat_Satellite-User_Guide-Users_and_Roles-Granular_Permission_Filtering.html, specifically the ^ (begins with) and !^ (not begins with) operators, the satellite WebUI will start showing many SQL exceptions because the operators are wrongly translated to the SQL language.

  In more details the ^ is translated as IN, mostly without brackets which are required for this operator, but this IN operator should not be used anyway and should be replaced by LIKE / ILIKE. The same applies for !^ where it is being replaced by NOT IN.

Version-Release number of selected component (if applicable):
I have hit this error message only with satellite 6.2:

~~~
PG::Error: ERROR: missing FROM-clause entry for table "taxonomies" LINE 1: ...) AND "hosts"."location_id" IN (5, 8, 2, 6) AND (("taxonomie... ^ : SELECT COUNT(DISTINCT count_column) FROM (SELECT DISTINCT "katello_errata"."id" AS count_column FROM "katello_errata" INNER JOIN "katello_content_facet_errata" ON "katello_content_facet_errata"."erratum_id" = "katello_errata"."id" INNER JOIN "katello_content_facets" ON "katello_content_facets"."id" = "katello_content_facet_errata"."content_facet_id" WHERE "katello_content_facets"."host_id" IN (SELECT "hosts"."id" FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" IN (3) AND "hosts"."location_id" IN (5, 8, 2, 6) AND (("taxonomies"."title" = 'test'))) LIMIT 6) subquery_for_count
~~~

And this one in 6.2 but also in 6.1:

~~~
PG::Error: ERROR: syntax error at or near "'test-'" LINE 9: ...locks_owner.resource_id = 8) OR (users."login" IN 'test-') O... ^ : SELECT DISTINCT foreman_tasks_tasks.*, "foreman_tasks_tasks"."id" AS t0_r0, "foreman_tasks_tasks"."type" AS t0_r1, "foreman_tasks_tasks"."label" AS t0_r2, "foreman_tasks_tasks"."started_at" AS t0_r3, "foreman_tasks_tasks"."ended_at" AS t0_r4, "foreman_tasks_tasks"."state" AS t0_r5, "foreman_tasks_tasks"."result" AS t0_r6, "foreman_tasks_tasks"."external_id" AS t0_r7, "foreman_tasks_tasks"."parent_task_id" AS t0_r8, "foreman_tasks_tasks"."start_at" AS t0_r9, "foreman_tasks_tasks"."start_before" AS t0_r10 FROM "foreman_tasks_tasks" INNER JOIN foreman_tasks_locks AS foreman_tasks_locks_owner ON (foreman_tasks_locks_owner.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_owner.resource_type = 'User' AND foreman_tasks_locks_owner.name = 'task_owner') INNER JOIN foreman_tasks_locks AS foreman_tasks_locks_owner ON (foreman_tasks_locks_owner.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_owner.resource_type = 'User' AND foreman_tasks_locks_owner.name = 'task_owner') INNER JOIN users ON (users.id = foreman_tasks_locks_owner.resource_id) WHERE (((foreman_tasks_locks_owner.resource_id = 8) OR (users."login" IN 'test-') OR (users."login" = 'foreman_admin'))) ORDER BY "foreman_tasks_tasks"."started_at" DESC NULLS LAST LIMIT 20 OFFSET 0
~~~


How reproducible:
Apply the attached set of rules and create a user with the role, you have created. Then login as the newly created user.


Steps to Reproduce:
1. Download the org-perms-default.sh
2. Edit it and set the unit and unit_org_id (unit = organization, unit_org_id = organization id)
3. Run the org-perms-default.sh
4. Create an user with org-admin-${unit} role
5. Login as the newly created user and try access these pages:
    https://<SAT62_FQDN>/dashboard
    https://<SAT62_FQDN>/foreman_tasks/tasks

Actual results:
  Hitting the errors described in "Depending on the version" part of this bug.

Expected results:
  All the pages will be displayed correctly respecting the role search rules.

Additional info:
  I wrote and attached interim patches which should workaround the issues.