Allow column presets with relationship from session variables

[nirvdrum]

Currently, column presets can either be static values or lookups from session variables. What I'd like to do is set a relationship value that's queried by a session variable. Specifically, I've been following the Auth0 + Hasura integration steps, but I'd really rather not use the Auth0 ID as the users table PK. Since users may change their login mechanism over time, there's no guarantee that the Auth0 ID won't change. Additionally, I'd rather join on UUID values than strings.

Performing custom checks with this schema works fine. I just set the check to be {"user":{"auth0_id":{"_eq": {"X-Hasura-User-Id"}}}. I'd like to see a similar mechanism supported for column presets.

This may be a dupe of #2705, but I'm not sure how the suggested solution of using a trigger can work in this case because the discriminator value must come from the session.

[rikinsk]

@nirvdrum Hasura respects the permissions of the nested table while doing nested inserts. What this means is that once you have set the column preset for the auth0_id column in the user table, Hasura will try to use that preset whenever a user is being inserted, i.e. either directly or via relationships.

I am not sure if this solution works for you but you can head to the user table permissions and set the preset of auth0_id as X-Hasura-User-Id.

[afreix]

@rikinsk I believe that I would like to do the same thing as @nirvdrum if I'm following correctly.

Your described solution isn't quite what I'm after. Imagine a users table with a id column (UUID) and an auth0Id column (string). This keeps my code flexible in case I want to move off of Auth0 in the future.

Ideally, I'd like to be able to have a column preset for a userId column on another table that looks like:
"Given a session variable X-Hasura-User-Id, go to the users table and set the value of the userId equal to id where { auth0Id: {_eq: X-Hasura-User-Id }}

[rikinsk]

@afreix I sort of understand your requirement but am not sure how you plan to use this. Can you share a sample schema with tables, relationships and the mutation you would want to run. Would help understand the full expectations and ideate without making any assumptions.

[nirvdrum]

@afreix's explanation is indeed what I'm looking for.

The schema looks like:

CREATE TABLE public.todos (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    title text NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    is_completed boolean DEFAULT false NOT NULL
);

CREATE TABLE public.users (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    auth0_id text NOT NULL,
    name text NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    last_seen timestamp with time zone
);

ALTER TABLE ONLY public.todos
    ADD CONSTRAINT todos_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_auth0_id_key UNIQUE (auth0_id);

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.todos
    ADD CONSTRAINT todos_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) 
    ON UPDATE CASCADE 
    ON DELETE CASCADE;

The session variable X-Hasura-User-Id contains the users.auth0_id value. There's no need for a column preset on the users table because I'm using an Auth0 rule to create the user upon signup/login. I did have to adjust that rule to a different mutation that sets the users.auth0_id value upon INSERT, but that was straightforward.

With this schema now, there are two remaining matters from Hasura:

1. Ensuring the permissions checks work with this schema
2. Providing proper column presets for tables that JOIN the users table

The first item works fine. Hasura's UI makes it possible to set up custom checks across relationships. So, any table with a relationship with the users table can have a custom check that looks like {"user":{"auth0_id":{"_eq": {"X-Hasura-User-Id"}}}. I admit this looks a bit funny because it appears the equality check is against a static string, but something within Hasura knows to use that string as a lookup in the session.

The second item is what this issue is about. Say I'm going to INSERT a new row in the todos table. There's a todos.user_id column that NOT NULL. That column is a foreign key to users.id, not to users.auth0_id. So, I need a way of setting tables.user_id to the currently logged in user's user.id. I can certainly pass that value as part of the input to the insert_todos mutation. But, column presets exist in Hasura to simplify calls and help ensure a client doesn't try to insert the wrong users.id value.

Column presets, as they exist today, only allow two sorts of values: static values or values from the session. What I'd like is a computed relationship based on a session variable. I want the column preset for todos.user_id to be SELECT id FROM users WHERE auth0_id = ($1->>"x-hasura-user-id")::text. In a way, this presupposes some sort of "current user" concept, much like Relay's "viewer". But, since Hasura already has the JWT, I don't think knowing the current user is a foreign concept to Hasura either.

I don't have a strong opinion on what the column preset should look like. There could be a new preset type called "relationship" that would allow building the query off users.id. There could be an SQL fragment builder. Or Hasura could have an internal magic variable like current_user or viewer that would allow me to reference it in the column preset.

Hopefully that helps clarify what I'm after. If not, please let me know.

[rikinsk]

Thanks for the extremely detailed explanation.

I see how this is a very valid use case and would appear to be a natural fit for presets. It would be an interesting discussion to see how we could make that work.

Although, when thinking about auto-deriving of foreign-key values, we should probably think of using relationships. Hasura allows nested inserts which automatically figure out the foreign-key values involved.

In our case here, the natural way to think about this would be to "insert a todo for the user identified via the X-Hasura-User-Id" i.e. update a user with a new todo.

Currently there isn't a way to update related objects using the update mutation and this seems like a compelling use case for it.

Would love to hear your thoughts about this approach as well and if it seems neater than setting a preset that traverses relationships.

[nirvdrum]

Thanks for the suggestion. It's a little kludgy, but I got it working. For anyone else interested, this is what I had to do:

First, enable insert permissions on the users table from the user role. In my case, I only had inserts enabled for the admin role because I didn't want clients being able to create users on their own. I use an Auth0 rule to create the users. However, without the insert permission, there's no way to do the nested insert on the users type.

A few things to note:

• Enable an insert permissions check so someone in the user role can only insert against an existing row corresponding precisely to that user.
• Enable insert permissions on any non-NULL column without a default value. In my case, that would be the name column.
• Use a column preset to set the auth0_id value from the JWT.

Next up is the query itself:

mutation CreateTodo {
  __typename
  insert_users(objects: {todos: {data: {title: "Nested Todo Creation"}}, name: "DUMMY"}, on_conflict: {constraint: users_auth0_id_key, update_columns: last_seen}) {
    returning {
      auth0_id
      todos(limit: 1, order_by: {created_at: desc}) {
        id
        created_at
        title
      }
    }
  }
}

A few things to note here as well:

• Since my name column is non-NULL and doesn't have a default value, I need to use a dummy name here. It really doesn't matter what is used because the insert is going to conflict anway.
• Specify what to do on conflict. In my case, if a row already has the same auth0_id value, which is guaranteed to be true, I simply update the last_seen value instead. You can do whatever you want here, but the point is to ensure the INSERT statement doesn't outright fail, otherwise you won't create your todo item.
• As far as I can tell, there's no way to get back info on the todo you just created all by itself. Since it's nested, it looks like the resolver will simply query the full relationship. I simulated the same effect by returning the most recently created todo. You may not need this information and can safely use something simpler. You may not want to run a query after insert, so feel free to use something else. And please note that there's a race condition here should another insert occur before this one finishes returning data because I don't believe the insert & fetch are done in a single transaction. It's been clarified that the fetch for the return is run in a transaction.

There are trade-offs with this approach, for sure. Up until now, I've been doing an initial user query to get back the Hasura DB ID. I was also playing with updating the "app metadata" in Auth0 with the Hasura DB ID, so I could send over multiple headers: one with the Auth0 ID and one with the Hasura DB ID. In some ways, the nested insert simplifies things and lets permissions be handled mostly from the JWT. On the other hand, the mutations themselves are a bit more involved and require relaxing permissions a bit.

I think being able to insert a todo on the update_users mutation would alleviate the permissions concern. I'd be happy to hear if you have a better approach to returning data scoped to the nested items upon insertion.

[rikinsk]

@nirvdrum It's great to see you got it working. My initial suggestion was going to be this exact solution but I decided not to share it because of its kludgy nature as you mentioned.

I believe something along the lines of #2952 (comment) will make this much neater by avoiding all the effort going into making the dummy insert work.

We'll try to finalize the API structure and get this out soon. If you have any suggestions on the APi structure you would expect to see it would be great to know.

[rikinsk]

Also just wanted to add regarding:

And please note that there's a race condition here should another insert occur before this one finishes returning data because I don't believe the insert & fetch are done in a single transaction

The mutatiion and its returning fetch are executed as a part of a transaction so you will not have to worry about race conditions

[nirvdrum]

@rikinsk Thanks for clarifying the transaction part. I amended my comment. I hadn't gone spelunking through the code yet, but the hasura logged statement output didn't look like it was using a transaction. So, I wasn't really sure what to make of that.

[EionRobb]

I'm trying to do a similar thing here. I have my own unique IDs for users and I'm trying to use the X-Hasura-User-Id session ID as a secondary ID for users, in case I switch auth providers or if I want to allow users to not require logins

mutation MyMutation($title: String = "", $start_date: timestamptz!, $end_date: timestamptz!) {
 __typename
  insert_users(objects: {hosting_events: {data: {title: $title, start_date: $start_date, end_date: $end_date}}, name: "_DUMMY"}, on_conflict: {constraint: users_auth_user_token_key, update_columns: last_seen}) {
    returning {
      hosting_events(limit: 1, order_by: {created_at: desc}) {
        id
      }
    }
  }
}

although I have more not-null columns in my users table. I'd need to add each one into the query and would have to do that for every mutation too; adding/removing more columns to the users table would require me to update every mutation in my app, which IMO is clunky.

Is there a better way to achieve the detachment of X-Hasura-User-Id from the user?