Proxy support for the multitenancy context strategy (pgbouncer and Supavisor) about ash_postgres HOT 3 OPEN

Definitely worth exploring :)

from ash_postgres.

@zachdaniel I can work on a PR but need some more advice from you

from ash_postgres.

So the guide on setting up a dynamic repository is here: https://hexdocs.pm/ecto/replicas-and-dynamic-repositories.html

The main issue with us doing it this way in ash_postgres (i.e every query maybe getting a dynamic repo) in place of changing the schema is that we'd be constantly establishing new connections for each query for actions that may call many queries.

What we would need to do is something like this:

Add a hook for data layers to wrap the entire execution of an action for a given tenant, returning context to be set on all queries/changesets for that tenant, storing that context (keyed by the tenant) in the process dictionary context(Ash.set_context() which deep-merges). Whenever we execute a query where the tenant is different than the one before, we run this hook and carry its contents down to all other queries. This has to be implemented at the action invocation level, not using something like Ash.Query.around_action which just wraps the individual query call.

Whenever we run an action, we check for context for the tenant of that action, and set it on to the current query. We already have the ability to configure the repo dynamically like so:

`Ash.Query.set_context(query, %{data_layer: %{:repo, RepoName}})`. 

So this new hook for ash_postgres would just be setting a new repo. And then we'd configure it like this:

postgres do
  multitenant_context fn config, tenant -> 
    Keyword.put(config, :username, "#{tenant}.#{config[:username]}")
  end
end

The default implementation of that function would be Keyword.put(config, :prefix, tenant).

And if all that is changed in that keyword is the prefix option then we won't set a dynamic repo. If anything else changes, then we will.

There are a fair amount of moving parts here, but it can be done. We will also likely find a few places where we aren't carrying down parent context from one query to the next. We'll just have to iterate on it.

from ash_postgres.