```yaml
---
slug: complex-hasura-permissions-via-views
published: 2021-04-15T00:00:00.000Z
description: How you can solve complex Hasura permissions using postgres views
changelog: ~
---
```

As we continue to explore the space of mentorship & career success at [Aibex](https://aibex.com), one of our prototypes tackled the idea of focused group messaging; think Slack, but without the signing in to seventeen spaces just to get something done. To make a messaging app that focused on the needs of mentors and mentees required unique permissions. We wanted users to have access to any messages they were present for, even if they later ended up leaving the group. To use our Slack analogy, leaving a private channel shouldn’t cut you off from the information you were originally invited to see.

The prototype is currently running on [Hasura](https://hasura.io), generating our GraphQL API from an existing Postgres database. We chose Hasura to both reduce the amount of API code we needed to write and to take advantage of the systems’ in-built webhook actions. When it came time to implementing our permissions via Hasura’s JSON language, we ran afoul of Hasura’s undocumented Rules for Rules (my term, not theirs).

- Every Hasura user in the same "role" must also use the same rules
- You cannot use a computed column or (most) function calls in a rule
- The only dynamic variables you have at permission time is the Hasura session variables (such as `X-Hasura-User-Id`)

In a messaging app, any message could have one or more recipients. In the Aibex prototype specifically, we also wanted someone who joined a group chat to gain access to all prior messages in the chat. After a user left a chat, they could continue to see all the messages during their time in the group.

There is no amount of `or`, `and`, nor `_exists` that solves this by default. If the title didn't give it away, the solution lies in offloading all computational work to a [postgres view](https://hasura.io/docs/latest/graphql/core/databases/postgres/schema/views.html), simplifying the Hasura permission JSON.

## The Computed Conditional Problem

We're going to begin with a set of tables, defining a relationship between users, groups, and the messages within:

```sql
CREATE TABLE users (id uuid PRIMARY KEY);

CREATE TABLE groups (id uuid primary key);

CREATE TABLE memberships (
  id uuid PRIMARY KEY,
  user_id uuid REFERENCES users(id),
  group_id uuid REFERENCES groups(id),
  joined_at timestamp NOT NULL DEFAULT now(),
  departed_at timestamp
);

CREATE TABLE messages (
  id uuid PRIMARY KEY,
  from_id uuid REFERENCES users(id),
  group_id uuid REFERENCES groups(id),
  created_at timestamp NOT NULL DEFAULT now(),
  content text
);
```

![An Entity Relationship diagram showing a user belongs to many groups and that a group has many messages](https://user-images.githubusercontent.com/1795/146840050-5fc68478-b223-44ad-8c1b-b27e1aa17835.png)
_A user has many groups and that group has many messages_

The problem arises when we want to add our permissions to the `messages` table. Hasura's permission system allows us to access `(messages).group.memberships` via its inbuilt relationship system.

```json
{
  "group": {
    "memberships": {
      "user_id": {
        "_eq": "X-Hasura-User-Id"
      }
    }
  }
} 
```

Setting up our permissions this way gets us halfway; a user who has a relationship to the message (via the groups table), would be allowed to see the message in question. However, if we want to use `joined_at` and `departed_at`:

```json
{
  "group": {
    "memberships": {
      "_and": {
        "user_id": {
          "_eq": "X-Hasura-User-Id"
        },
        "departed_at": {
          "_lte": "<< CANNOT ACCESS messages.created_at >>"
        }
      }
    }
  }
}
```

Accessing the group through an Object Relationship will only let us test that the user was in the group at some point. By the time we've traversed the relationship to check the `departed_at` column, we've lost the ability to check the original row's `created_at` value. Variations that use `_exists` suffer from the same problem; currently you cannot use a row's dynamic value in permissions.

## Enter the View

To make our permission problem with Hasura, we need some **precomputed** version of our messages table. One that doesn't need `(messages).group.memberships.departed_at / created_at` to work. Even better, if we have one row for every recipient of a message, we can then just ask *that* version of the data instead.

One option would be to denormalize the data, creating copies of the message for every recipient, but doing so will create integrity problems down the line. Enter the view:

```sql
CREATE VIEW message_recipients AS
  SELECT msg.id, msg.group_id, mbr.user_id as recipient
  FROM (
    (messages msg JOIN groups grp ON (grp.id = msg.group_id))
      JOIN memberships mbr ON (mbr.group_id = grp.id)
  )
  WHERE (
    mbr.departed_at IS NULL OR msg.created_at <= mbr.departed_at
  );
```

The end result will be a view with a row for every message recipient, containing a message id and a user id, filtered such that the member is either active in the group (`NULL`) or their `departed_at` happened after the message's `created_at` timestamp. Now our Hasura permissions will work as expected. When we tell Hasura there is a **manual relationship** between `messages` and our new `message_recipients` view, it will link both objects together. We can then use the new relationship in our permission JSON.  🎉

```json
{
  "_or": {
    "from_id": {
      "_eq": "X-Hasura-User-Id"
    },
    "message_recipients": {
      "recipient": {
        "_eq": "X-Hasura-User-Id"
      }
    }
  }
}
```

## Where Views Go Wrong?

In practice, large datasets will make conditional permissions via views expensive. This is because a view is just a prior known subquery under the hood. There are optimizations you can do with heavy read data such as a [materialized view](https://www.postgresql.org/docs/9.3/rules-materializedviews.html), but those situations imply you’ve got both a substantial amount of traffic and a substantial amount of data. Before you throw all of this away though, take advantage of Hasura’s “Analyze” button. Missing an important index can have a huge performance cost and for most applications, views are perfectly ok.

And if you do outgrow the view, then you should consider denormalizing the data. Hasura Actions can take care of the denormalization for you. In our final example, there’s nothing that stops you from writing to a recipient table in response to new messages and users joining the group. For now though, you probably won’t need those optimizations.

Ultimately, views give Hasura’s rule engine superpowers. If you can write a query that selects for your permission, you can make a view. And if you can make a view, Hasura can understand it.

## Additional Reading

- [Hasura Row + Column Permission Tuples (GitHub)](https://github.com/hasura/graphql-engine/issues/3442)
- [Multiple Column + Row Permissions for the Same Role (Hasura Docs)](https://hasura.io/docs/latest/graphql/core/auth/authorization/role-multiple-rules.html)
- [Create View (Postgres Docs)](https://www.postgresql.org/docs/current/sql-createview.html)
- [Object and Array Relationships (Hasura Docs)](https://hasura.io/docs/latest/graphql/core/databases/postgres/queries/nested-object-queries.html)


How you can solve complex Hasura permissions using postgres views

Link

code

CREATE TABLE users (id uuid PRIMARY KEY);

CREATE TABLE groups (id uuid primary key);

CREATE TABLE memberships (
  id uuid PRIMARY KEY,
  user_id uuid REFERENCES users(id),
  group_id uuid REFERENCES groups(id),
  joined_at timestamp NOT NULL DEFAULT now(),
  departed_at timestamp
);

CREATE TABLE messages (
  id uuid PRIMARY KEY,
  from_id uuid REFERENCES users(id),
  group_id uuid REFERENCES groups(id),
  created_at timestamp NOT NULL DEFAULT now(),
  content text
);


Fence

strong

CREATE VIEW message_recipients AS
  SELECT msg.id, msg.group_id, mbr.user_id as recipient
  FROM (
    (messages msg JOIN groups grp ON (grp.id = msg.group_id))
      JOIN memberships mbr ON (mbr.group_id = grp.id)
  )
  WHERE (
    mbr.departed_at IS NULL OR msg.created_at <= mbr.departed_at
  );


Hasura

2021

Complex Hasura Permissions Via Views