Help understanding multi-tenancy with foreign keys + Absinthe

:wave:Hey Elixir folks,

I’m newer to Elixir and building a GraphQL API with Absinthe. Overall it’s been wonderful and I’m really enjoying working with Elixir and Phoenix — this forum has been an especially wonderful resource, so thanks everyone. :slight_smile:

I’ve been able to work my way through the docs and different tutorials and get my API up and running. I’m at a point where I need to add tenancy to this app, and ended up following this guide to use foreign keys for this since this is generally the method I’ve used in the past with different applications and it generally seems easier for me to wrap my head around. I’ve updated all of my models and this seems to be scoping correctly and blocking my query if neglect to set my tenant ID, e.g.

import Myapp.Users
Myapp.Users
iex(2)> list_users
** (RuntimeError) expected account_id or skip_account_id to be set
    (foundation 0.1.0) lib/myapp/repo.ex:23: Myapp.Repo.prepare_query/3
    (ecto 3.5.6) lib/ecto/repo/queryable.ex:211: Ecto.Repo.Queryable.execute/4
    (ecto 3.5.6) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3
iex(2)> Myapp.Repo.put_account_id("account_1613590824385y45fvnpf")
nil
iex(3)> list_users
[debug] QUERY OK...

But I’m really struggling to understand the right way to use this put_org_id method. I read through
Bullet proof way to enforce tenants with a foreign key? - #27 by baldwindavid where the OP ran into a similar question, but didn’t find a clear answer here (unless I misunderstood) and I’m stuck banging my head.

I’m managing authentication in my app with:

  1. A set_current_user plug to set current user:
defmodule MyappWeb.Plugs.SetCurrentUser do
  @behaviour Plug

  import Plug.Conn

  def init(opts), do: opts

  def call(conn, _) do
    context = build_context(conn)
    Absinthe.Plug.put_options(conn, context: context)
  end

  defp build_context(conn) do
    with ["Bearer " <> token] <- get_req_header(conn, "authorization"),
         {:ok, %{id: id}} <- MyappWeb.AuthToken.verify(token),
         %{} = user <- Myapp.Users.get_user!(id) do
      %{current_user: user}
    else
      _ -> %{}
    end
  end
end

  1. Generic authentication middleware as outlined in the docs:
# Used to authenticate users
defmodule MyappWeb.Schema.Middleware.Authenticate do
  @behaviour Absinthe.Middleware

  def call(resolution, _) do
    case resolution.context do
      %{current_user: _} ->
        resolution
      _ ->
        resolution
        |> Absinthe.Resolution.put_result({:error, "You must be authenticated to create this request"})
    end
  end
end

The problem I’m running into is if I try to authenticate and make a request, I’m (expectedly) told that I haven’t yet set account_id. I’ve tried a bunch of different things, and I can make it work if I hardcode this in my build_context method like Myapp.Repo.put_account_id("account_1613590824385y45fvnpf") and also in my context like so:

def list_users do
    Myapp.Repo.put_account_id("account_1613590824385y45fvnpf")
    Repo.all(User)
  end

but obviously (1) I need to retrieve the account ID for the user, and (2) I’m certain I shouldn’t be using put_account_id in every single function. I just don’t really know where exactly I should do this or what’s idiomatic. So I have two questions I’m hoping to get some help with:

  1. I’m not clear on how to obtain and pass around the account_id. I realize this is quite newb, but what’s the best way for me to get the current user’s account_id (stored as a field on the User model) and where should I store it? In a session? A cookie?

  2. I would think within the set_current_user plug or in some other global way I could use the put_account_id function to set my user’s tenant ID, but this doesn’t work and I’m sure there’s an easier/better way to do this.

Would love any pointers here — thanks so much!

Hey @adamj, this is a great question! Perhaps I’m missing something, but if I am understanding this line correctly, I think you already have the account id:

but what’s the best way for me to get the current user’s account_id (stored as a field on the User model)

That is to say, in your context plug, you have the line %{} = user <- Myapp.Users.get_user!(id). If the account_id is a field on the user, then user.account_id is the account id right?

Or is the issue that get_user! only works if the account_id is already set? In that case, I would definitely consider making the account_id value part of your session token so that you can go ahead and pull it off of that.

Hey @benwilson512! Thanks for the reply and especially thanks for all your incredible work on Absinthe. :blush:

You’re right that I can access the account ID that way, and I’m probably just making this harder than it needs to be. Like I could do something where I just modify each resolver function to pass in the context and then read it and put the account (tenant) ID like so:

def list_users(_parent, _args, %{context: %{current_user: user}}) do
    Myapp.Repo.put_account_id(user.account_id)
    {:ok, Users.list_users()}
  end

But I’d be updating every one of my resolvers down the line like

def get_user(_parent, %{id: id}, %{context: %{current_user: user}}) do
    Myapp.Repo.put_account_id(user.account_id)
    {:ok, Users.get_user!(id)}
end

def get_user_by_email(_parent, %{email: email}, %{context: %{current_user: user}}) do
    Myapp.Repo.put_account_id(user.account_id)
    {:ok, Users.get_user_by_email(email)}
end

...

And so on. Is that the right way to do this? I was thinking that I’d be able to write a plug or something that retrieves the account ID and passes it in globally in a more DRY way, but would love your guidance on whether this is right. (And still newb to Elixir and Phoenix, so apologies for the remedial questions here!)

I think there is some sort of disconnect here. The Absinthe resolvers run in the same process as the Plugs. If you set the account_id in the plug, it should always be set for all of the resolvers, you don’t need to do so again.

Ah yeah I see that was unnecessary and this Just Works — thanks. In case it helps someone else (or anyone has feedback), all I needed to do was update my plug to skip checking for my tenant ID when making the request (so it’s not rejected) and then conditionally put my tenant ID in my call function:

defmodule MyappWeb.Plugs.SetCurrentUser do
  @behaviour Plug
  alias Myapp.Users.User

  import Plug.Conn

  def init(opts), do: opts

  def call(conn, _) do
    context = build_context(conn)
    if Map.has_key?(context, :current_user) do
      Myapp.Repo.put_account_id(context.current_user.account_id)
    end
    Absinthe.Plug.put_options(conn, context: context)
  end

  defp build_context(conn) do
    with ["Bearer " <> token] <- get_req_header(conn, "authorization"),
         {:ok, %{id: id}} <- MyappWeb.AuthToken.verify(token),
         %{} = user <- Myapp.Repo.get!(User, id, skip_account_id: true) do
      %{current_user: user}
    else
      _ -> %{}
    end
  end
end

1 Like

I looked through the linked blog post and I think you shouldn’t run into any more difficulties. I was concerned that the need for the process dictionary would cause issues with Dataloader because it fires off database queries in different processes (which won’t contain your pdict value) but the way that the blog post structures the associations to use composite primary keys I believe solves your issue.

I spoke too soon! When I try a nested query using dataloader it fails and tells me I’ve not properly set my tenant ID. :weary:

My schema has a users table and a customers table (amongst others). A user has many customers. So following the directions, I created a customers table with a unique index for the composite foreign key:

create unique_index(:users, [:id, :account_id])

create table(:customers, primary_key: false) do
  add :id, :string, primary_key: true
  ... # other fields
  add :user_id, references(:users, with: [account_id: :account_id], on_delete: :nothing, type: :string)
end
      

In my Customers and Users contexts I define datasource:

def datasource() do
  Dataloader.Ecto.new(Repo, query: &query/2)
end

And in my schema.ex I define a context function:

# Set up Dataloader
  def context(ctx) do
    loader =
      Dataloader.new
      |> Dataloader.add_source(Customers, Customers.datasource())
      |> Dataloader.add_source(Events, Events.datasource())
      |> Dataloader.add_source(Leads, Leads.datasource())
      |> Dataloader.add_source(Organizations, Organizations.datasource())
      |> Dataloader.add_source(Users, Users.datasource())
      |> Dataloader.add_source(Accounts, Accounts.datasource())

    Map.put(ctx, :loader, loader)
  end

  def plugins do
    [Absinthe.Middleware.Dataloader] ++ Absinthe.Plugin.defaults()
  end

But if I try

query listUsers {
  listUsers {
    id
    firstName
    email
    customers {
      firstName
    }
  }

This raises the exception:

Request: POST /graphiql
** (exit) an exception was raised:
    ** (Dataloader.GetError) {%RuntimeError{message: "expected account_id or skip_account_id to be set"}, [{Foundation.Repo, :prepare_query, 3, [file: 'lib/myapp/repo.ex', line: 23]}, {Ecto.Repo.Queryable, :execute, 4, [file: 'lib/ecto/repo/queryable.ex', line: 211]}, {Ecto.Repo.Queryable, :all, 3, [file: 'lib/ecto/repo/queryable.ex', line: 17]}, {Enum, :"-map/2-lists^map/1-0-", 2, [file: 'lib/enum.ex', line: 1411]}, {Dataloader.Source.Dataloader.Ecto, :run_batch, 2, [file: 'lib/dataloader/ecto.ex', line: 667]}, {Dataloader.Source.Dataloader.Ecto, :"-run_batches/1-fun-1-", 2, [file: 'lib/dataloader/ecto.ex', line: 601]}, {Task.Supervised, :invoke_mfa, 2, [file: 'lib/task/supervised.ex', line: 90]}, {Task.Supervised, :reply, 5, [file: 'lib/task/supervised.ex', line: 35]}, {:proc_lib, :init_p_do_apply, 3, [file: 'proc_lib.erl', line: 226]}]}
...

I’m pretty certain the associations themselves are working properly, because I can insert some data manually and test by hardcoding the tenant id in my repo.ex, replacing account_id = opts[:account_id] -> with account_id = "account_1613758796223ri3h283g" -> here:

def prepare_query(_operation, query, opts) do
    cond do
      opts[:skip_account_id] || opts[:schema_migration] ->
        {query, opts}

      account_id = "account_1613758796223ri3h283g" ->
        {Ecto.Query.where(query, account_id: ^account_id), opts}

      true ->
        raise "expected account_id or skip_account_id to be set"
    end
  end

Then my query works perfectly and returns the user and their nested customer.

I’ll keep working through this and will update if I find a solution. Thanks again for all the help!

Can you supply the full stack trace? And are you doing anything in your query/2 functions? Additionally, are you doing a source per context or a source per ecto schema?

Sure thing, here’s my whole stack trace:

---
[debug] QUERY OK source="users" db=5.5ms idle=989.0ms
SELECT u0."id", u0."active", u0."created", u0."email", u0."first_name", u0."image", u0."last_name", u0."object_type", u0."phone", u0."role", u0."team", u0."title", u0."updated", u0."password_hash", u0."account_id", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."account_id" = $1) ["account_1613758796223ri3h283g"]
[error] Task #PID<0.1636.0> started from #PID<0.1633.0> terminating
** (RuntimeError) expected account_id or skip_account_id to be set
    (myapp 0.1.0) lib/myapp/repo.ex:23: Myapp.Repo.prepare_query/3
    (ecto 3.5.6) lib/ecto/repo/queryable.ex:211: Ecto.Repo.Queryable.execute/4
    (ecto 3.5.6) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3
    (elixir 1.11.3) lib/enum.ex:1411: Enum."-map/2-lists^map/1-0-"/2
    (dataloader 1.0.8) lib/dataloader/ecto.ex:667: Dataloader.Source.Dataloader.Ecto.run_batch/2
    (dataloader 1.0.8) lib/dataloader/ecto.ex:601: anonymous fn/2 in Dataloader.Source.Dataloader.Ecto.run_batches/1
    (elixir 1.11.3) lib/task/supervised.ex:90: Task.Supervised.invoke_mfa/2
    (elixir 1.11.3) lib/task/supervised.ex:35: Task.Supervised.reply/5
    (stdlib 3.14) proc_lib.erl:226: :proc_lib.init_p_do_apply/3
Function: &:erlang.apply/2
    Args: [#Function<14.7762471/1 in Dataloader.Source.Dataloader.Ecto.run_batches/1>, [{{:assoc, Myapp.Users.User, #PID<0.1568.0>, :customers, Myapp.Customers.Customer, %{}}, #MapSet<[{["user_16137587962318zkqw3u4"], %Myapp.Users.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, account: #Ecto.Association.NotLoaded<association :account is not loaded>, account_id: "account_1613758796223ri3h283g", active: true, created: 1613758796, customers: #Ecto.Association.NotLoaded<association :customers is not loaded>, email: "someone@email.com", events: #Ecto.Association.NotLoaded<association :events is not loaded>, first_name: "Chris", id: "user_16137587962318zkqw3u4", image: nil, inserted_at: ~N[2021-02-19 18:19:56], last_name: "Jones", leads: #Ecto.Association.NotLoaded<association :leads is not loaded>, object_type: "user", password: nil, password_hash: "$pbkdf2-sha512$160000$W5d31LXHWtbO34aqzTz59Q$/PKrSWzEjEyW6Jx7Svq2HRlPv7f.8jtWk1pfnGUG0Z2EQKn1ym0DrygAxmnuENTcv6csTMxNFU/dvtdH1qYnFQ", phone: nil, role: nil, team: nil, title: nil, updated: nil, updated_at: ~N[2021-02-19 18:19:56]}}]>}]]
[info] Sent 500 in 131ms
[error] #PID<0.1568.0> running Myapp.Endpoint (connection #PID<0.1567.0>, stream id 1) terminated
Server: localhost:4000 (http)
Request: POST /graphiql
** (exit) an exception was raised:
    ** (Dataloader.GetError) {%RuntimeError{message: "expected account_id or skip_account_id to be set"}, [{Myapp.Repo, :prepare_query, 3, [file: 'lib/myapp/repo.ex', line: 23]}, {Ecto.Repo.Queryable, :execute, 4, [file: 'lib/ecto/repo/queryable.ex', line: 211]}, {Ecto.Repo.Queryable, :all, 3, [file: 'lib/ecto/repo/queryable.ex', line: 17]}, {Enum, :"-map/2-lists^map/1-0-", 2, [file: 'lib/enum.ex', line: 1411]}, {Dataloader.Source.Dataloader.Ecto, :run_batch, 2, [file: 'lib/dataloader/ecto.ex', line: 667]}, {Dataloader.Source.Dataloader.Ecto, :"-run_batches/1-fun-1-", 2, [file: 'lib/dataloader/ecto.ex', line: 601]}, {Task.Supervised, :invoke_mfa, 2, [file: 'lib/task/supervised.ex', line: 90]}, {Task.Supervised, :reply, 5, [file: 'lib/task/supervised.ex', line: 35]}, {:proc_lib, :init_p_do_apply, 3, [file: 'proc_lib.erl', line: 226]}]}
        (dataloader 1.0.8) lib/dataloader.ex:223: Dataloader.do_get/2
        (absinthe 1.5.5) lib/absinthe/resolution/helpers.ex:361: anonymous fn/6 in Absinthe.Resolution.Helpers.do_dataloader/5
        (absinthe 1.5.5) lib/absinthe/middleware/dataloader.ex:37: Absinthe.Middleware.Dataloader.get_result/2
        (absinthe 1.5.5) lib/absinthe/phase/document/execution/resolution.ex:230: Absinthe.Phase.Document.Execution.Resolution.reduce_resolution/1
        (absinthe 1.5.5) lib/absinthe/phase/document/execution/resolution.ex:185: Absinthe.Phase.Document.Execution.Resolution.do_resolve_field/3
        (absinthe 1.5.5) lib/absinthe/phase/document/execution/resolution.ex:114: Absinthe.Phase.Document.Execution.Resolution.walk_results/6
        (absinthe 1.5.5) lib/absinthe/phase/document/execution/resolution.ex:93: Absinthe.Phase.Document.Execution.Resolution.walk_result/5
        (absinthe 1.5.5) lib/absinthe/phase/document/execution/resolution.ex:114: Absinthe.Phase.Document.Execution.Resolution.walk_results/6
        (absinthe 1.5.5) lib/absinthe/phase/document/execution/resolution.ex:103: Absinthe.Phase.Document.Execution.Resolution.walk_result/5
        (absinthe 1.5.5) lib/absinthe/phase/document/execution/resolution.ex:114: Absinthe.Phase.Document.Execution.Resolution.walk_results/6
        (absinthe 1.5.5) lib/absinthe/phase/document/execution/resolution.ex:93: Absinthe.Phase.Document.Execution.Resolution.walk_result/5
        (absinthe 1.5.5) lib/absinthe/phase/document/execution/resolution.ex:67: Absinthe.Phase.Document.Execution.Resolution.perform_resolution/3
        (absinthe 1.5.5) lib/absinthe/phase/document/execution/resolution.ex:24: Absinthe.Phase.Document.Execution.Resolution.resolve_current/3
        (absinthe 1.5.5) lib/absinthe/pipeline.ex:369: Absinthe.Pipeline.run_phase/3
        (absinthe_plug 1.5.4) lib/absinthe/plug.ex:530: Absinthe.Plug.run_query/4
        (absinthe_plug 1.5.4) lib/absinthe/plug.ex:290: Absinthe.Plug.call/2
        (phoenix 1.5.7) lib/phoenix/router/route.ex:41: Phoenix.Router.Route.call/2
        (phoenix 1.5.7) lib/phoenix/router.ex:352: Phoenix.Router.__call__/2
        (myapp 0.1.0) lib/myapp_web/endpoint.ex:1: MyappWeb.Endpoint.plug_builder_call/2
        (myapp 0.1.0) lib/plug/debugger.ex:132: MyappWeb.Endpoint."call (overridable 3)"/2

I’m not yet doing anything with the query function:

def query(queryable, _) do
  queryable
end

And finally I added the datasource per context — I followed the same pattern from the pragmatic Absinthe tutorial, e.g.:

I’m hopeful that I’m close and my guess is that I can pass my context.current_user into Dataloader, so working on that at the moment! :slight_smile:

What I ended up doing here was just updating my context datasource functions to take repo_opts:

def datasource(tenant_opts) do
  Dataloader.Ecto.new(Repo, query: &query/2, repo_opts: tenant_opts)
end

Then in my schema I check to see if the context is set. If it is, I pass it through, and otherwise I pass the skip_account_id option so I send an unauthorized message if my user isn’t authenticated.

# Set up Dataloader
  def context(ctx) do
    # Check for current user and set tenant_opts
    tenant_opts = if user = ctx[:current_user] do
      [account_id: user.account_id]
    else
      [skip_account_id: true]
    end

    loader =
      Dataloader.new
      |> Dataloader.add_source(Customers, Customers.datasource(tenant_opts))
      |> Dataloader.add_source(Events, Events.datasource(tenant_opts))
      |> Dataloader.add_source(Leads, Leads.datasource(tenant_opts))
      |> Dataloader.add_source(Organizations, Organizations.datasource(tenant_opts))
      |> Dataloader.add_source(Users, Users.datasource(tenant_opts))
      |> Dataloader.add_source(Accounts, Accounts.datasource(tenant_opts))

    Map.put(ctx, :loader, loader)
  end

This seems to be working as all my queries are running and scoping to the current_user, but any feedback welcome if there’s a better way to do this. A million thanks @benwilson512 for all your help!

This doesn’t work the way you think it does.

iex(1)> x = 1
1
iex(2)> if true do
...(2)> x = 2
...(2)> end
warning: variable "x" is unused (if the variable is not meant to be used, prefix it with an underscore)
  iex:3

2
iex(3)> x
1

You should do:

tenant_opts = if user = ctx[:current_user] do
  [account_id: user.account_id]
else
  [skip_account_id: true]
end
1 Like

Good catch, I didn’t catch this since I was authenticating every request — updated my post!

Here’s what I use for Dataloader implementing the same case

  @spec context(map()) :: map()
  def context(ctx) do
    loader =
      Dataloader.new()
      |> Dataloader.add_source(
        Repo,
        Dataloader.Ecto.new(Repo, repo_opts: [organization_id: ctx.current_user.organization_id])
      )

    Map.put(ctx, :loader, loader)
  end
1 Like