Implicit schema exposion to support 1 schema(namespace) / tenant multitenancy

[bozada]

The 3 main multitenancy options for postgres are:

• Tenants have their own DB
• Tenants are in same DB, share tables, separation provided by RLS
• Tenants are in the same DB, each of them in their own schema (namespace), each tenant schema is structurally identical (tables, functions, triggers, ...)

Postgraphile supports the first one out of the box. The second is achieveable with already existing plugins.

But there is no real solution for the third option. With an inflection plugin it is possible, to imitate this by running multiple postgraphiles simultenously. But it is no viable for an SaaS app with 100s or 1000s of tenants. As far as I understand Postgraphile exposes the chosen schema(s) explicitly. But postgres is great at handling implicit calls thanks to role specific search_path's.

I know allowing implicit exposion would mean if the exposed schema is not identical to any of the tenant schemas would cause errors / breaks. But its not postgraphiles job to enforce this design choice, just like its not a querry builders job in a non graphql app.

[benjie]

This is not the first time I've received this request; so thanks for bringing it to my attention again.

PostGraphile explicitly references everything it uses currently, e.g. select "id" from "app_public"."users" rather than just select "id" from "users". In most cases it does this using the namespaceName property of the relevant entity (table, function, view, etc) in our introspection results.

I think it might make sense to swap all of this out for something like reference(entity) which would internally do something like:

function reference(entity: PgClass | PgProc | ...) {
  return sql.identifier(entity.namespaceName, entity.name);
}

Then to support this use case you'd only need to override this reference function to:

function reference(entity: PgClass | PgProc | ...) {
  return sql.identifier(entity.name);
}

and it would work for your multi-tenant needs. I'd be happy to entertain a PR (with tests) that implements this behaviour, or alternatively you could sponsor its development (if you search the issues you may find others who may be interested in clubbing together).

This will definitely not be the default behaviour though - that would almost certainly lead to lots of people's schemas breaking.

[jmsegrev]

@benjie I'm in need of this feature, particularly the third case of the multitenancy options listed by @bozada. I'm considering tackling this, could you give some insight as to how would yo go about implementing this feature?, anything will be of help.

[jmsegrev]

I forgot to mention, that my particular use case, depends on values from the http request to set the postgres search_path in order to select the respective schema to query against.

[benjie]

Best hint I can give is to search for sql.identifier; but I’m unlikely to merge this into the v4 branch now so if you were to start working on it, please do it against master.

An alternative approach would be to override pgClient.query and regexp replace a known schema with the relevant one for your current user.

[benjie]

Support for this was added in the latest release of PostGraphile V5, the changes required in the V5 architecture were minimal (the changes in V4 would have been much more significant!): https://github.com/graphile/crystal/pull/1774