Using X-Hasura-User-Id on a JSONB Permission filter

Question

I'm setting up a Hasura server on top of an existing Postgres database. The schema uses JSONB a lot, and all entities are modelled like this:

EntityName
-----------
id: String
resource: JSONB

In my current case, I have the entity Appointment. I want to create a Permission that one user only has select access to Appointments where it is a participant.

Appointment.resource is modelled like this:

{
  // ... some other fields ...
  "participant": [
    {
      "actor": {
        "reference": "a826ade6bcbf" // this is X-Hasura-User-Id
      },
    }
  ]
}

Which lead me to the following "Row select permissions" (writing it on the web console, if it makes a difference):

{
  "resource": {
    "_contains": {
      "participant": [
        {
          "actor": { 
            "reference": "X-Hasura-User-Id" 
          }
        }
      ]
    }
  }
}

The exported metadata for the table looks like this:

{
  "version": 2,
  "tables": [
    {
      "table": {
        "schema": "public",
        "name": "appointment"
      },
      "select_permissions": [
        {
          "role": "patient",
          "permission": {
            "columns": [
              "id",
              "resource",
            ],
            "filter": {
              "resource": {
                "_contains": {
                  "participant": [
                    {
                      "actor": {
                        "reference": "X-Hasura-User-Id",
                      }
                    }
                  ]
                }
              }
            }
          }
        }
      ]
    },
  ],
}

Thing is, if I try said rule on GraphiQL with the proper headers, I get an empty response. But if I change the rule to "reference": "a826ade6bcbf" (a String literal), I get the expected response (only Appointments matching the filter).

It seems like X-Hasura-User-Id is not being substituted on the Permission.

Am I doing something wrong? Is this feature supported?

Are there other options for authorization? Changing the current database schema is not a viable solution.

Answer 1

As of 1.2.1, don't think it's possible.

For query:

query MyQuery {
  test {
    id
    test_jsonb
    test_text
  }
}

w/ x-hasura-user-id: "a826ade6bcbf", and x-hasura-role: "user" in headers, and the following "row select permissions" (clicking X-Hasura-User-Id from the Console UI):

{"test_text":{"_eq":"X-Hasura-User-Id"}}

we get the following logs:

2020-05-19T21:35:12.879476+00:00 app[web.1]: {"type":"query-log","timestamp":"2020-05-19T21:35:12.877+0000","level":"info","detail":{"request_id":"c1d865c0-be91-4295-b8a0-92ddf5702d70","generated_sql":{"test":{"prepared_arguments":["{\"x-hasura-role\":\"user\",\"x-hasura-user-id\":\"a826ade6bcbf\"}"],"query":"SELECT  coalesce(json_agg(\"root\" ), '[]' ) AS \"root\" FROM  (SELECT  row_to_json((SELECT  \"_1_e\"  FROM  (SELECT  \"_0_root.base\".\"id\" AS \"id\", \"_0_root.base\".\"test_jsonb\" AS \"test_jsonb\", \"_0_root.base\".\"test_text\" AS \"test_text\"       ) AS \"_1_e\"      ) ) AS \"root\" FROM  (SELECT  *  FROM \"public\".\"test\"  WHERE (((\"public\".\"test\".\"test_text\") = ((($1->>'x-hasura-user-id'))::text)) OR (((\"public\".\"test\".\"test_text\") IS NULL) AND (((($1->>'x-hasura-user-id'))::text) IS NULL)))     ) AS \"_0_root.base\"      ) AS \"_2_root\"      "}},"query":{"operationName":"MyQuery","query":"query MyQuery {\n  test {\n    id\n    test_jsonb\n    test_text\n  }\n}\n\nquery MyQuery2 {\n  test(where: {test_jsonb: {_contains: {participant: [{actor: {reference: \"a826ade6bcbf\"}}]}}}) {\n    id\n    test_jsonb\n  }\n}\n"}}}

and expected result (there are other rows in test table that don't have the correct test_text value:

{
  "data": {
    "test": [
      {
        "id": 4,
        "test_jsonb": {
          "participant": [
            {
              "actor": {
                "reference": "a826ade6bcbf"
              }
            }
          ]
        },
        "test_text": "a826ade6bcbf"
      }
    ]
  }
}

Notice the "prepared_arguments" and how that's used in the query: $1->>'x-hasura-user-id'))::text.

When we do the same with the following row select permission (using a JSONB based permission):

{"test_jsonb":{"_contains":"{participant:[ { actor: {reference: X-Hasura-User-Id }}]}"}}

2020-05-19T21:36:45.580889+00:00 app[web.1]: {"type":"query-log","timestamp":"2020-05-19T21:36:45.580+0000","level":"info","detail":{"request_id":"7dc56852-7501-48fe-8205-3d75a7964c58","generated_sql":{"test":{"prepared_arguments":["{\"x-hasura-role\":\"user\",\"x-hasura-user-id\":\"a826ade6bcbf\"}"],"query":"SELECT  coalesce(json_agg(\"root\" ), '[]' ) AS \"root\" FROM  (SELECT  row_to_json((SELECT  \"_1_e\"  FROM  (SELECT  \"_0_root.base\".\"id\" AS \"id\", \"_0_root.base\".\"test_jsonb\" AS \"test_jsonb\"       ) AS \"_1_e\"      ) ) AS \"root\" FROM  (SELECT  *  FROM \"public\".\"test\"  WHERE ((\"public\".\"test\".\"test_jsonb\") @> (('\"{participant:[ { actor: {reference: X-Hasura-User-Id }}]}\"')::jsonb))     ) AS \"_0_root.base\"      ) AS \"_2_root\"      "}},"query":{"operationName":"MyQuery","query":"query MyQuery {\n  test {\n    id\n    test_jsonb\n    \n  }\n}\n\nquery MyQuery2 {\n  test(where: {test_jsonb: {_contains: {participant: [{actor: {reference: \"a826ade6bcbf\"}}]}}}) {\n    id\n    test_jsonb\n  }\n}\n"}}}

the prepared arguments are not used appropriately (like above) - ((\"public\".\"test\".\"test_jsonb\") @> (('\"{participant:[ { actor: {reference: X-Hasura-User-Id }}]}\"')::jsonb))

Doesn't matter if X-Hasura-User-Id is formatted in any way. Would file as a feature request here - https://github.com/hasura/graphql-engine.

The workaround here would be to extract this value into a column and query through a view or computed/generated column.