ash-project / ash-project/ash_postgres

Proxy support for the multitenancy context strategy (pgbouncer and Supavisor)

Open
#141 3 comments 1 reaction 0 assignees View on GitHub
enhancement
Dominant language
Elixir
Stars
189
Forks
168
Avg merge
13h 1m
Merged PRs (30d)
22

Description

**Is your feature request related to a problem? Please describe.**
The [Supavisor](https://github.com/supabase/supavisor) proxy for Postgres offers several benefits, the main one being the elimination of long-lived idle database connections. It can also route requests to different Postgres instances, satisfying both scalability and regulatory needs. Unfortunately, Ash's multitenancy apparently doesn't work with this feature because, while Ash's multitenancy context strategy can change the database schema name at runtime, it is apparently unable to similarly alter the Postgres username.

**Describe the solution you'd like**
Allow Postgres multitenancy to dynamically modify the Postgres username with a tenant slug, similar to the way that Ash Postgres' context multitenancy strategy uses a different schema name based on the tenant at runtime. Supabase requires the user name format "tenant.usename". See the Supavisor [docs](https://github.com/supabase/supavisor/wiki/Installation-and-Usage) for more information.

**Describe alternatives you've considered**

I've heard that "dynamic Ecto repositories" exists but have not researched this.

**Express the feature either with a change to resource syntax, or with a change to the resource interface**

To avoid hard-coding a solution, perhaps a function could be provided to the 'multitenancy' macro. I would want this to be more DRY than the following but this is the general idea. Note in the following example, the schema name wouldn't need to change, but IT DEPENDS, and thus this is an opportunity to customize behavior that is currently hard-coded. 'username' and 'schema' are optional.

```elixir
multitenancy do
strategy :context # This feature might also be applicable to attribute multitenancy.. requires more thought
username: fn (org_id, username)->"#{org_id}.#{username}" end
schema: fn(org_id, schema)->schema end
end
```

Being able to provide a function to map a tenant to all database credentials (host, user, password, schema) would be powerful in its own right and would eliminate the need for supabase and pgbouncer for many use cases.

**Additional context**
I have not researched what would be needed to support the Postgres proxy [pgbouncer](https://www.pgbouncer.org). Ideally, one feature would address the needs of both pgbouncer and Supavisor.

There would be a table of tenants in a master Postgres database instance in the table `organizations`. This would be used to generate and track unique "org_id" values. This represents a single point of failure which seems unavoidable.

There would not be a foreign key from `user` to `organization` because these tables could be located on different physical Postgres instances. Or perhaps in the tenant databases there would also be an organization table but containing only one row.

Contributor guide

Open the contributing guide

Research direction

Start by examining the Ash Postgres context multitenancy strategy and the `multitenancy` resource syntax described in the issue, then review the linked Supavisor and PgBouncer documentation. Define whether the feature maps only usernames or all database credentials, and how the `organizations` table and tenant slug participate; completion requires an agreed interface that supports the stated proxy use cases.

Written by the indexing model from the issue text.

Assessment

Tech stack
elixir, postgresql
Domain
backend-api-design, databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.