ash-project / ash-project/ash_postgres
Proxy support for the multitenancy context strategy (pgbouncer and Supavisor)
- 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
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