My learnings from implementing RLS

[hakontro]

This mostly just summarizes our discussion from the RW discord here: https://discord.com/channels/679514959968993311/1088030943446577223

A couple of issues I faced during trying to implement this myself:

1. It's boring to add the RLS code per related model.
   By using custom Prisma Generators we can generate migrations based on model comments! See the discord for more code. One thing I ran into here is that, if resetting the migration, the order matters: the migrations for the models needs to be run before the RLS stuff (so you don't set policies on non-existing tables). It's also smart to replicate the directory names Prisma makes, which isn't hard of course:

const appendRlsScripts = (modelname, baseDirectory) => {
  const scripts = `-- RLS script for ${modelname} generated from the custom Prisma Generator
---------------------------------------------------------

ALTER TABLE "${modelname}" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "${modelname}" FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_policy ON "${modelname}" USING ("orgId" = (current_setting('app.current_org_id'::text))::integer);
CREATE POLICY tenant_bypass ON "${modelname}" USING (current_setting('app.bypass', TRUE)::text = 'on');
`
  // Align migration name with the Prisma generated migrations
  const d = new Date().toLocaleString()
  const dateString = dateFormat(d, 'yyyymmddMMssL')

  const directoryPath = path.join(
    baseDirectory,
    `${dateString}_${modelname}_RLS`
  )
  fs.mkdirSync(directoryPath, {
    recursive: true,
  })

  const filePath = path.join(directoryPath, `migration.sql`)

  fs.writeFileSync(filePath, scripts)
}

2. Custom postgres user setup is necessary, but that doesn't necessarily fit well with the Prisma workflow
   I find myself purging the database and migration scripts every now and then while developing. Would be great to have a way to keep track of those, like when setting up the rls_user here: https://github.com/dthyresson/prisma-extension-supabase-rls
   Could be achieved with a generator as well!

3. It would be cool to hide the orgId (my tenant identificator) from the code generated by Redwood.
   I don't want my GraphQL API to expose the fact that orgId is all over my models, and I don't want to handle it when writing application code (kind of the point of using postgres RLS). You can easily override for example the SDL generator:

  type ${singularPascalName} {
    ${query.split('\n').filter(l => !l.includes('orgId')).join('\n')}
  }

but doing this for all of the generators feels a little wrong. There's many of them, and these can change as redwood upgrades I suppose.. not idea what a good solution is!

[Jonatthu]

Your insights are truly remarkable and greatly appreciated! Thank you for generously sharing your knowledge with us.

[Jonatthu]

@hakontro discord link is private btw

[ciekawy]

does this all mean supabase doc is wrong suggesting it's possible to use prisma with RLS?