hasura / hasura/graphql-engine
Using exists column comparison in `where` query for permissions
- Dominant language
- TypeScript
- Stars
- 32.1k
- Forks
- 3k
- PR merge metrics
- PR metrics pending
Description
### Version Information
Server Version: 2.21.0
CLI Version (for CLI related issue):
### Environment
OSS
### What is the current behaviour?
Trying to use the column comparison for permission with the `_exists` clause on another table. This seem to be working properly when directly querying the table with the permissions.
In this example I have a table `channel_owners` with field `channelId`

The problem arises when trying to use this table in a `where` query for another table with a relationship to `channel_owners`. For example
```graphql
query {
getChannels(where: {owners: {ownerId: {_eq: "..."}}}) {
id
...
}
}
```
which results in the following error
```
"hint": "Perhaps you meant to reference the column \"channel_entity.chainId\".",
"message": "column channel_entity.channelId does not exist",
"statement": "SELECT coalesce(json_agg(\"root\" ), '[]' ) AS \"root\" FROM (SELECT row_to_json((SELECT \"_e\" FROM (SELECT \"_root.base\".\"id\" AS \"id\", \"_root.base\".\"threshold\" AS \"threshold\", \"_root.base\".\"proposedNonce\" AS \"proposedNonce\", \"_root.base\".\"chainId\" AS \"chainId\", \"_root.ar.root.owners\".\"owners\" AS \"owners\" ) AS \"_e\" ) ) AS \"root\" FROM (SELECT * FROM \"public\".\"channel_entity\" WHERE (EXISTS (SELECT 1 FROM \"public\".\"channel_owner_entity\" AS \"__be_0_channel_owner_entity\" WHERE ((((\"__be_0_channel_owner_entity\".\"channelId\") = (\"public\".\"channel_entity\".\"id\")) AND ('true')) AND (((EXISTS (SELECT 1 FROM \"public\".\"identity_role_entity\" WHERE (((((((\"public\".\"identity_role_entity\".\"identityId\") = ((($1->>'x-hasura-user-id'))::text)) OR (((\"public\".\"identity_role_entity\".\"identityId\") IS NULL) AND (((($1->>'x-hasura-user-id'))::text) IS NULL))) AND ('true')) AND ('true')) AND (((((\"public\".\"identity_role_entity\".\"scope\") = (\"public\".\"channel_entity\".\"channelId\")) AND ('true')) AND ('true')) AND ('true'))) AND ('true')) )) AND ('true')) AND (((((\"__be_0_channel_owner_entity\".\"ownerId\") = (($2)::text)) AND ('true')) AND ('true')) AND ('true')))) )) ) AS \"_root.base\" LEFT OUTER JOIN LATERAL (SELECT coalesce(json_agg(\"owners\" ), '[]' ) AS \"owners\" FROM (SELECT row_to_json((SELECT \"_e\" FROM (SELECT \"_root.ar.root.owners.base\".\"id\" AS \"id\" ) AS \"_e\" ) ) AS \"owners\" FROM (SELECT * FROM \"public\".\"channel_owner_entity\" WHERE (((\"_root.base\".\"id\") = (\"channelId\")) AND (EXISTS (SELECT 1 FROM \"public\".\"identity_role_entity\" WHERE (((((((\"public\".\"identity_role_entity\".\"identityId\") = ((($1->>'x-hasura-user-id'))::text)) OR (((\"public\".\"identity_role_entity\".\"identityId\") IS NULL) AND (((($1->>'x-hasura-user-id'))::text) IS NULL))) AND ('true')) AND ('true')) AND (((((\"public\".\"identity_role_entity\".\"scope\") = (\"public\".\"channel_owner_entity\".\"channelId\")) AND ('true')) AND ('true')) AND ('true'))) AND ('true')) ))) ) AS \"_root.ar.root.owners.base\" ) AS \"_root.ar.root.owners\" ) AS \"_root.ar.root.owners\" ON ('true') ) AS \"_root\" "
```
which seems like its trying to apply the permissions to the table at the root of query and not the `channel_owners` table
Possibly related #9457
### Keywords
Permissions, column comparison, _exists
Contributor guide
Research direction
Reproduce the v2.21.0 GraphQL `getChannels(where: {owners: {ownerId: {_eq: ...}}})` query with the shown permission and `_exists` relationship, then inspect the generated PostgreSQL statement. Trace why the permission condition references the root `channel_entity` instead of the related `channel_owner_entity`; done means the query applies each permission to the intended table and returns the expected channels.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- graphql, postgres, typescript
- Domain
- api, authorization, database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 35/100