Absolute column reference filter in query with query clause does not behave as expected #9283
Description
Version Information
Server Version: 2.16.0
CLI Version (for CLI related issue):
Environment
Docker
What is the current behaviour?
Absolute column reference ($._ceq, $._cne etc.) in permissions filters are evaluated incorrectly for queries with where clauses that refer to related entities.
For our access management we use absolute column references ($._ceq) in our permission filters. With these we check via views (via _exists operator) if a user has access to a resource.
This works fine for queries that don't have a where clause. As only one permission filter is evaluated.
But if a where clause is present, that refers to a related entity, the permission filter of both the root entity and the related entity are evaluated and the absolute column reference of the related entity is evaluated against the root entity.
This leads to wrong results.
What is the expected behaviour?
The absolute column reference of the related entity should be evaluated against the related entity.
How to reproduce the issue?
Repo for reproduction: https://github.com/mathiasjakob/hasura-ceq-bug
Alternatively you can use the following steps:
- Create two table that have a relationship between them.
- Create select permission for both tables that use absolute column reference (
$._ceq) in the filter. - Query one of the tables with a where clause that refers to the related table.
Screenshots or Screencast
Please provide any traces or logs that could help here.
SQL generated by hasura for the following data structure:
Database schema:
- table: article (id, title, author_id)
- table: author (id, name)
- table: user (id)
- view: user_article_access (user_id, article_id)
- view: user_author_access (user_id, author_id)
- FK article.author_id -> author.id
Article and author table have a select permission that uses absolute column reference ($._ceq) in the filter, to check if the user has access to the resource via the views user_article_access / user_author_access.
Generated sql
Defective part: ("public"."user_author_access"."author_id") = ("public"."article"."id")
SELECT
coalesce(json_agg("root"), '[]') AS "root"
FROM
(
SELECT
row_to_json(
(
SELECT
"_e"
FROM
(
SELECT
"_root.base"."id" AS "id",
"_root.or.author"."author" AS "author"
) AS "_e"
)
) AS "root"
FROM
(
SELECT
*
FROM
"public"."article"
WHERE
(
(
EXISTS (
SELECT
1
FROM
"public"."user_article_access"
WHERE
(
(
(
(
(
(
("public"."user_article_access"."user_id") = ((($ 1 ->> 'x-hasura-user-id')) :: integer)
)
OR (
(
("public"."user_article_access"."user_id") IS NULL
)
AND (((($ 1 ->> 'x-hasura-user-id')) :: integer) IS NULL)
)
)
AND ('true')
)
AND ('true')
)
AND (
(
(
(
("public"."user_article_access"."article_id") = ("public"."article"."id")
)
AND ('true')
)
AND ('true')
)
AND ('true')
)
)
AND ('true')
)
)
)
AND (
EXISTS (
SELECT
1
FROM
"public"."author" AS "__be_0_author"
WHERE
(
(
(
("__be_0_author"."id") = ("public"."article"."author_id")
)
AND ('true')
)
AND (
(
(
EXISTS (
SELECT
1
FROM
"public"."user_author_access"
WHERE
(
(
(
(
(
(
("public"."user_author_access"."user_id") = ((($ 1 ->> 'x-hasura-user-id')) :: integer)
)
OR (
(
("public"."user_author_access"."user_id") IS NULL
)
AND (((($ 1 ->> 'x-hasura-user-id')) :: integer) IS NULL)
)
)
AND ('true')
)
AND ('true')
)
AND (
(
(
(
("public"."user_author_access"."author_id") = ("public"."article"."id")
)
AND ('true')
)
AND ('true')
)
AND ('true')
)
)
AND ('true')
)
)
)
AND ('true')
)
AND (
(
(
(("__be_0_author"."id") IS NOT NULL)
AND ('true')
)
AND ('true')
)
AND ('true')
)
)
)
)
)
)
) AS "_root.base"
LEFT OUTER JOIN LATERAL (
SELECT
row_to_json(
(
SELECT
"_e"
FROM
(
SELECT
"_root.or.author.base"."id" AS "id",
"_root.or.author.base"."name" AS "name"
) AS "_e"
)
) AS "author"
FROM
(
SELECT
*
FROM
"public"."author"
WHERE
(
(("_root.base"."author_id") = ("id"))
AND (
EXISTS (
SELECT
1
FROM
"public"."user_author_access"
WHERE
(
(
(
(
(
(
("public"."user_author_access"."user_id") = ((($ 1 ->> 'x-hasura-user-id')) :: integer)
)
OR (
(
("public"."user_author_access"."user_id") IS NULL
)
AND (((($ 1 ->> 'x-hasura-user-id')) :: integer) IS NULL)
)
)
AND ('true')
)
AND ('true')
)
AND (
(
(
(
("public"."user_author_access"."author_id") = ("public"."author"."id")
)
AND ('true')
)
AND ('true')
)
AND ('true')
)
)
AND ('true')
)
)
)
)
LIMIT
1
) AS "_root.or.author.base"
) AS "_root.or.author" ON ('true')
) AS "_root"Any possible solutions/workarounds you're aware of?
Keywords
Permissions, absolute column reference, ceq, cne, cgt, clt, cgte, clte