role-based schema prefix for multi-tenancy apps

[tom-pryor]

Hi, we're currently exploring ways to add a GraphQL API to our existing SaaS multi tenant application. We currently use postgres schemas to isolate our clients and each client has identical tables, etc. in their schema. For example

myapp (database)

• client1 (schema)
  • users (table)
  • products (table)
• client2 (schema)
  • users (table)
  • products (table)

All tenants share the same code base and the code simply does a SET search_path <name> to select the schema based of the subdomain, e.g client1.myapp.com.

Hasura looks amazing but we're not really sure how we can replicate this functionality as it seems to fail at the first hurdle. When we go to track our existing tables it's populating them with the tenant name (e.g client1_users, should just be users) and we're not sure how to perform this transparent schema switching.

Is there any way we can accomplish this with Hasura? We'd really love to use this project!

[denizkenan]

@tom-pryor : adding schema name as prefix to query is crucial to be honest. For example, our all hasura applications relies on this feature.
I can think of couple of ways to achieve a workable solution without changing hasura behaviour:

most logical one to create an interface between hasura and your client where correct query is generated. In other words, you can convert this query:

{
    __schema__.users{
    ...
    }
}

to:

{
    Client1.users{
    ...
    }
}

this conversion can take place in a middleware, part of sdk, or a proxy api server.
also Hasura needs to have a complete duplicate of metadata for each schema.

Besides, I don't recall any project that supports such functionality. Treating schema names as Business logic data store is quite anti-pattern.
https://stackoverflow.com/questions/14893964/how-many-schemas-can-be-created-in-postgres/14895450

[tirumaraiselvan]

@tom-pryor As a workaround, is it possible for you to client-side template all your queries with the schema name?

[tirumaraiselvan]

One idea is to have a default schema per role (default: public). Say, a role called tenant1 can specify that its default schema is tenant1 . Then the public schema tables can get a prefix like public_users and tenant1 schema has no prefix and can query simply like users.

I have edited the title to reflect this feature request.

[furlongce]

Are there any plans to implement something like this? We're in the same boat.

[Camsteack]

It would be great to have something like that ! My main issue personally is that each schema needs to have a complete set of duplicate metadata so it makes it really difficult to apply updates once you start have more than a few tenants. Is there a plan or a way to handle that better where we could only have one set of files that we could apply to each schema ? That would be awesome as at the moment the only viable way to have a multi tenant environment is to use one schema with a tenant column.
Thanks a lot for the awesome product though !

[JohnRock0422]

Any roadmap about support this?

[ilijaNL]

Bump

[rahulagarwal13]

@tom-pryor , @JohnRock0422 , @ilijaNL - Thanks for the feature request. We are currently working on dynamic Db connection feature which is in alpha stage currently. This feature allows you to set custom connection templates using Kriti templating language for routing to databases. Please see below a sample template where you can use Kriti to route to different databases using tenant IDs. The assumption here is that schema is same across tenants and that the primary Db is the main source of truth. We understand that you are looking for this feature across schemas and not across databases but still wanted to update you on this in case it is of any use for you. Dynamic Routing to different schemas within the same database is still on the roadmap.

Here is the RFC for it. Please let us know if you have any feedback for us? You can also fill this form to get access to the custom build for checking it out.

[ilijaNL]

Hmm so this is tenancy on database level? It would be nice if schema and table (prefix/postfix) would be possible as well

[rahulagarwal13]

@ilijaNL - yes on the database level. Got it schema/table level is being considered but cannot provide a timeline yet. It would be great if you could provide some more light on your use-case and what application goal you are trying to achieve

[furlongce]

We need it at the schema level as well. It's common to use schemas for isolation rather than databases (especially when the schemas will be identical) so you don't have to worry about a ton of different connection pools.

…

On Mon, Dec 19, 2022, 10:03 PM Rahul Agarwal ***@***.***> wrote: @ilijaNL <https://github.com/ilijaNL> - yes on the database level. Got it schema/table level is being considered but cannot provide a timeline yet. It would be great if you could provide some more light on your use-case and what application goal you are trying to achieve — Reply to this email directly, view it on GitHub <#3606 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADR6DI6WMMICFY45GF5HGLDWOEOXNANCNFSM4KBRY6SA> . You are receiving this because you commented.Message ID: ***@***.***>

[ilijaNL]

Schema level is probably most common tenancy approach. Every tenant has own (identical) schema, like described https://hasura.io/blog/multi-tenancy-history-quick-take-and-how-to-with-hasura/ An Example by Data Schema Derived Multi-Tenancy

[tirumaraiselvan]

@furlongce @ilijaNL There is a workaround to achieve schema-level tenancy with the "Dynamic DB Resolution" feature we have in preview.

You can create "stub" databases corresponding to each schema and import the actual schema into the public schema of this stub database via a foreign data wrapper: https://www.postgresql.org/docs/current/sql-importforeignschema.html. Then, you can use Dynamic DB resolution to resolve to the corresponding DB based on request context.

[ilijaNL]

@tirumaraiselvan Hmm that is an interesting approach, however I wonder what the implications are on the latency. A working example would be nice to have. Another approach could be to be able to change search_path (on postgres) dynamically however then we need some ability to hook into the hasura execution pipeline...

[cfurlong0018]

@tirumaraiselvan that's a really invasive solution, do you have a timeline for when a more integrated solution might get added? It seems like it'd be relatively simple to accept the schema as a JWT claim (simpler than connecting to multiple databases)

[cfurlong0018]

@tirumaraiselvan @rahulagarwal13 Can you let us know if there's a timeline for a resolution? This is becoming prohibitive to our usage of Hasura. The metadata is getting extremely large and it doesn't play nice with Hasura's APIs