Referencing permissions

[afitzek]

I hope the format of my posting here is correct, if not please let me know and I can adopt it.

It would be really nice to be able to create "referencing permissions" (not sure if this a good term for this ;)) for resources in a database.

In many models one encounters resources, that share access permissions. For example let's say we have a database model, where we store users which will have access to objects and these objects can have tags.

So we would have the following tables (just pseudo code):

TABLE "user" (
 ID
)

TABLE "object" (
 ID
 USER_ID
)

TABLE "tag" (
 ID
 OBJECT_ID
)

In my permission model, a user should be able to read all tags, of objects that belong to him.

Currently I would create permissions for the user table:

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

For the object table (I know in this simple example I could reference the USER_ID directly, but that is not the point, imagine an orgs structure, with teams between users and object):

{"user":{"ID":{"_eq":"X-Hasura-User-Id"}}}

For the tags table:

{"object":{"user":{"ID":{"_eq":"X-Hasura-User-Id"}}}}

As one can see we are duplicating the rules.

For a very complex system, this becomes really hard to manage.

It would be really nice to be able to write something like this (there are probably much better ways to express this, but I hope the idea comes across):

The user permission:

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

The object permission:

{"user":{"can_select": true}}

The tag permission:

{"object":{"can_select": true}}

One could reference access to other tables, basically.

If this is possible, there is probably also a way to increase the performance of the generated SQL queries, because at that point, one does not have to duplicate permissions in the query?

Here is an example query that hasura 1.3.3 generated for the above model:

query {
  object(where:{
    ID:{
      _eq:1
    }
  }) {
    tags{
      key
      value
    }
  }
}

SELECT
  coalesce(json_agg("root"), '[]') AS "root"
FROM
  (
    SELECT
      row_to_json(
        (
          SELECT
            "_8_e"
          FROM
            (
              SELECT
                "_7_root.ar.root.tags"."tags" AS "tags"
            ) AS "_8_e"
        )
      ) AS "root"
    FROM
      (
        SELECT
          *
        FROM
          "public"."object"
        WHERE
          (
            (
              EXISTS (
                SELECT
                  1
                FROM
                  "public"."user" AS "_0__be_0_user"
                WHERE
                  (
                    (
                      (
                        ("_0__be_0_user"."ID") = ("public"."object"."user_id")
                      )
                      AND ('true')
                    )
                    AND (
                      (
                        (
                          (("_0__be_0_user"."ID") = (('1') :: integer))
                          OR (
                            (("_0__be_0_user"."ID") IS NULL)
                            AND ((('1') :: integer) IS NULL)
                          )
                        )
                        AND ('true')
                      )
                      AND ('true')
                    )
                  )
              )
            )
            AND (("public"."object"."ID") = (('1') :: integer))
          )
      ) AS "_1_root.base"
      LEFT OUTER JOIN LATERAL (
        SELECT
          coalesce(json_agg("tags"), '[]') AS "tags"
        FROM
          (
            SELECT
              row_to_json(
                (
                  SELECT
                    "_5_e"
                  FROM
                    (
                      SELECT
                        "_4_root.ar.root.tags.base"."key" AS "key",
                        "_4_root.ar.root.tags.base"."value" AS "value"
                    ) AS "_5_e"
                )
              ) AS "tags"
            FROM
              (
                SELECT
                  *
                FROM
                  "public"."tag"
                WHERE
                  (
                    (("_1_root.base"."ID") = ("object_id"))
                    AND (
                      EXISTS (
                        SELECT
                          1
                        FROM
                          "public"."object" AS "_2__be_0_object"
                        WHERE
                          (
                            (
                              (
                                ("_2__be_0_object"."ID") = ("public"."tag"."object_id")
                              )
                              AND ('true')
                            )
                            AND (
                              (
                                EXISTS (
                                  SELECT
                                    1
                                  FROM
                                    "public"."user" AS "_3__be_1_user"
                                  WHERE
                                    (
                                      (
                                        (
                                          ("_3__be_1_user"."ID") = ("_2__be_0_object"."user_id")
                                        )
                                        AND ('true')
                                      )
                                      AND (
                                        (
                                          (
                                            (("_3__be_1_user"."ID") = (('1') :: integer))
                                            OR (
                                              (("_3__be_1_user"."ID") IS NULL)
                                              AND ((('1') :: integer) IS NULL)
                                            )
                                          )
                                          AND ('true')
                                        )
                                        AND ('true')
                                      )
                                    )
                                )
                              )
                              AND ('true')
                            )
                          )
                      )
                    )
                  )
              ) AS "_4_root.ar.root.tags.base"
          ) AS "_6_root.ar.root.tags"
      ) AS "_7_root.ar.root.tags" ON ('true')
  ) AS "_9_root"

I have not fully thought it through, but I think this could be optimized to a simple outer join of the tags, if it is known, that the permission of the tags reference the objects permission.

Let me know if this makes any sense. :)

[afitzek]

In a simpler version a permission like {"user":{"can_select": true}} could use the select permission of the current role from the table that is behind the user reference.

It could just internally expand the permission to {"user":{"ID":{"_eq":"X-Hasura-User-Id"}}} if the select permission for the role for the table referenced by user is : {"ID":{"_eq":"X-Hasura-User-Id"}}.

[jrevels]

We're running into a similar issue at my company - there's lots of access transitivity in our schema (e.g. "if you have access to the parent table, you should have access to the child table" and vice versa) that results in a lot of heavily nested/redundant access control configuration on the Hasura side of things. It does seem to cause performance issues in our case - queries that are super simple/fast as admin slow to a crawl as a non-admin user because of the additional access control logic.

We were thinking about ways to refactor our schema quite a bit to mitigate some of these issues, but ultimately something like @afitzek's proposal would be incredibly useful for us IIUC.

[maxcan]

Came to this via #6591. I've also got a lot of transitive tables. A -> B -> C -> D etc and I'd like to go into D and create a rule like {c_table: {has_access: true}} and then the same for C and B so that they each depend on the parent table.

currently this is done with a lot of copy/pasting duplicated code. I'm on a solo developer project right now so this is easy to for me to model in my head but if i were running a team I'd be terrified of some dev missing something with all this duplication.