Shortcuts for multi-tenant DBs

Get Help and Help Others
1

I’m working on a large web app that is intended to support lots of users, and each user will input a great deal of personal information. I’ve written an initial prisma schema, but it will grow a lot over time to track all the data. (This is a side-project, just me writing everything on the weekends, and my amount of time to code is quite limited, so I want to automate as much as possible, of course!)

I noticed that while Redwood does a great job at setting me up with default CRUD type operations, Cells, scaffolds, etc., there doesn’t seem to be anything built-in that handles multi-tenancy issues (aside from the user authentication support that’s built-in, of course).

I’d love a way to mark a Model as “user-associated” so that Redwood would generate automatically all the bits that perform authentication and authorization. On the frontend I want queries to be restricted by the current userid, and on the backend I want to enforce that all ops are checked for authorization against the current user specified in a JWT.

An example of what I’m talking about it all the steps in the tutorial here:

How hard would it be to automate all this? I wonder if it’s as simple as a flag that says that a model is user-specific, and some custom function that describes how to get the core “userId” variable from useAuth’s current user?

I also posted another question a little while ago about row-level security. That’s another step on top of this that I’d love to have, but basic authentication/authorization associated with individual DB models is my first requirement, of course) For my use cases it’s really rare that I’ll be pulling info out of a DB that isn’t user-restricted. I know this is different from, say, a site powering shared content like a public blog, etc., but I figure that user-restriction is a requirement for a large number of projects. Overall my feeling about Redwood (and auth) is that everything was going great as I got set up, I got useAuth working with a 3rd-party provider, but then the framework’s hand-holding sort of ended prematurely, and there wasn’t any other “magic” I could do to use useAuth everywhere necessary.

2

I’ve wondered about this too, for now I have just been checking in each service that the user is a member of the organisation that owns the data it is trying to access.
I imagine that the correct way to do it would be to create my own directive to use in addition to useAuth in the SDLs but I don’t think I know how to do that (and possibly it might need to know a fair bit about my data model to know how to navigate the relations to work out whether the user is allowed or not)

3

Ah yeah, that’s a more sophisticated setup than what I was considering as well. For me I’m just thinking of a consumer-facing website that has user logins, and targeting lots of users, so I need everyone to be locked-down to only see their own data. With a hierarchy involving organizations a bit more is involved.

4

In that case a directive might be easier to figure out (I’ve not tried to write one).
If all the tables that contain things that belong to a user have a field like owner that is foreign key linking back to the user record then you can probably do something like check that object.owner === currentUser in a directive and add that in the SDL.
(or just stick it in all the different services, but that is quite a lot of repetition)

5

Thanks for the reply. I can try to look into directives, that’s a nice idea and I haven’t explored it either.

I think it has to do more work than just the owner===user check though, since for update and delete we need to do a lookup first, according to the tutorial post I referenced above.

For example, with things like find and create I can do this (for a “Person” class), where I send in the current user Id from context (context.currentUser.id), which seems pretty easy:

export const person: QueryResolvers['person'] = ({ id }) => {
  return db.person.findFirst({
    where: { id, authId: context.currentUser.id },
  })
}

export const createPerson: MutationResolvers['createPerson'] = ({ input }) => {
  return db.person.create({
    data: { ...input, authId: context.currentUser.id },
  })
}

But then for the update/delete I need this helper function. Maybe there’s a way to do this with a resolver but I don’t know (it seems the resolver needs to know the basic “get by id” function to use for each model, here person(id):

const verifyOwnership = async ({ id }) => {
  if (await person({ id })) {
    return true
  } else {
    throw new ForbiddenError("You don't have access to this person")
  }
}

Then I call it inside update/delete (which also now have to be async):

export const updatePerson: MutationResolvers['updatePerson'] = async ({
  id,
  input,
}) => {
  await verifyOwnership({ id })
  return db.person.update({
    data: input,
    where: { id },
  })
}

export const deletePerson: MutationResolvers['deletePerson'] = async ({
  id,
}) => {
  await verifyOwnership({ id })
  return db.person.delete({
    where: { id },
  })
}

It seems to me like these are essential extra bits of boilerplate that are needed for services in every app that stores any user data whatsoever, unless I’m missing something.