hasura / hasura/graphql-engine

Using exists column comparison in `where` query for permissions

Open
#9,522 6 comments 0 reactions 0 assignees View on GitHub
k/bug
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`

![image](https://user-images.githubusercontent.com/54160127/227426501-aeb71af8-4891-4583-b35e-2de114da6ac2.png)

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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.