🚣‍♀️   RedwoodJS Postgres RLS

(a) Demonstration of Postgres row-level security in a RedwoodJS application

Warning

Because this method wraps each request in a batch transaction, explicitly running a transaction using db.$transaction() may result in unexpected behavior. Prisma expects to change this in the future, and more information can be found on the official RLS example from Prisma.

This repository provides a demonstration of how to use Postgres row-level security from within a RedwoodJS application. The README has been divided into two sections:

• Getting Started with this repository.
• Supporting RLS in a RedwoodJS application.

Getting Started

The following provides steps to clone and setup this repository on your development machine.

0) Prerequisites

• See and fulfill the RedwoodJS prerequisites
• Postgres database (one of)
  • Docker - Used to run a short-lived Postgres container.
  • Local installation - A tutorial from Redwood on installing Postgres to your machine.
  • ... - Your preferred method of accessing a Postgres database while developing applications.

1) Get the code

git clone https://github.com/realStandal/redwood-rls-demo.git

cd redwood-rls-demo

yarn install

2) Start or configure your database

Start your Postgres database and (optionally) configure the application to make use of its root user.

2.1) Start the database container (Docker)

A compose configuration has been provided which will start and expose a Postgres container which can be connected to using the username postgres and password secret.

docker compose up -d

Once you are done using this database, it can be stopped using the following.

docker compose down

2.2) Update the connection string (non-Docker)

This demonstration has been setup with the assumption you will be running a database using the attached compose configuration. If you are not using it, you will need to create and configure an environment file (or update .env.defaults) with the updated connection string.

DATABASE_URL=...

3) Migrate and seed your database

yarn rw prisma migrate dev

After your database has been migrated, your can seed it using the provided script.

yarn rw exec seed

It will create numerous Tenants, Users, and Posts by these users. As defined by the migrated security policies: users will only be able to access posts created by users in the same tenant as them. In addition, posts can only be updated and deleted by the user who created the post. The list below provides a summary of the data which will be added by the script.

• Password used by all users: 123
• Tenant A
  • User A1 with 4 posts
  • User A2 with 1 post
• Tenant B
  • User B1 with 3 posts
  • User B2 with 2 posts

4) Start the development server and login

Start Redwood's development server, which should eventually open the /login page in a new browser window. Use one of the usernames listed in the previous section with the password: 123 to login. After logging in, you should be navigated to the /posts page where only posts by the selected user's tenant will be visible. Clicking the "Logout" button will allow you to login to another account - switching to a user in another tenant should cause a new list of posts to appear.

yarn rw dev

Supporting RLS

This section details supporting Postgres row-level security from within a RedwoodJS application. These steps have been applied to this repository and are listed here to be generalized across applications.

1) Create a user which respects RLS policies

In order to use row-level security, a database needs to be connected to as a non root user which does not have the BYPASSRLS attribute. To check if the configured user will respect RLS policies, a script has been included which prints the results to the console. This script can be copy-and-pasted into your application for easy reuse.

yarn rw exec check-rls

To simplify creating a user which respects RLS policies, another script has been included which will prompt you for a username, password, and whether or not there is an existing database the user should have access to. This script will use the database configured in .env.defaults or .env and should be ran by a root user.

yarn rw exec setup-user

Warning

When accessing Prisma Studio using a user which respects RLS policies, you may not have a complete view of your application's data.

Your application can then connect to your database using this user to ensure data is accessed according to any security policies you have in place.

2) Extend the Prisma Client

In order to extend a Prisma Client, extensions need to be enabled as a preview feature in your database's schema.

generator client {
  previewFeatures = ["clientExtensions"]
}

After being enabled, we can extend all operations to include parameters on the current transaction. We can then make use of this parameter in security policies and as the default value of columns inserted using the extended client. In particular, this is useful for associating operations on the database with the user who made the request. The following has been taken from this repository and demonstrates associating queries with two distinct values.

import { PrismaClient } from '@prisma/client'

export const db = new PrismaClient()

export const getAuthDb = ({ tenantId, userId }) => {
  return db.$extends((client) => {
    return client.$extends({
      query: {
        $allModels: {
          async $allOperations({ args, query }) {
            const [, , result] = await client.$transaction([
              client.$executeRaw`SELECT set_config('app.tenantId', ${tenantId}, TRUE)`,
              client.$executeRaw`SELECT set_config('app.userId', ${userId}, TRUE)`,
              query(args),
            ])

            return result
          },
        },
      },
    })
  })
}

2.1) Bypass client

It may also be useful to extend the Prisma Client in order to create a bypass client with security policies which allow for operations on the database outside the scope of a user. The following has been taken from this repository and is provided to Redwood's dbAuth which it can use to update users.

export const bypassDb = db.$extends((client) => {
  return client.$extends({
    query: {
      $allModels: {
        async $allOperations({ args, query }) {
          const [, result] = await client.$transaction([
            client.$executeRaw`SELECT set_config('app.bypass', 'on', TRUE)`,
            query(args),
          ])

          return result
        },
      },
    },
  })
})

3) Provide to the extended client to services

We can provide the extended client to all services by creating a Yoga Plugin which includes a customized client in the context of each request. If the user does not exist, or if the application is being accessed anonymously, the original client will be added instead. Like the other code samples, this one has been taken from this repository.

import type { Plugin } from 'graphql-yoga'

import type { GlobalContext } from '@redwoodjs/graphql-server'

import { db, getAuthDb } from 'src/lib/db'

export const usePrismaAuth = (): Plugin<GlobalContext> => {
  return {
    onContextBuilding: ({ context, extendContext }) => {
      extendContext({
        db: !context.currentUser
          ? db
          : getAuthDb({
              tenantId: context?.currentUser?.tenantId,
              userId: context?.currentUser?.id,
            }),
      })
    },
  }
}

3.1) Implement the plugin

The plugin can be added to your application's GraphQL handler (api/src/functions/graphql.{js|ts}) using the extraPlugins field.

import { usePrismaAuth } from 'src/plugins/prisma-auth'

export const handler = createGraphQLHandler({
  extraPlugins: [usePrismaAuth()],
})

3.2) Add type declarations (optional)

If your project is written in TypeScript - or if you get errors trying to access context.db - you can extend Redwood's GlobalContext interface to include proper types for the context's Prisma Client. The following has been taken from this repository and is added to a file in a types directory which you should add to the root of your project.

import type { getAuthDb } from 'api/src/lib/db'

declare module '@redwoodjs/graphql-server' {
  interface GlobalContext {
    db: ReturnType<typeof getAuthDb>
  }
}

4) Add RLS policies using a database migration

Because Prisma does not support expressing security policies using its schema, RLS can be enabled and CREATE POLICY commands can be added to an existing migration file. A new, blank migration can be created using the command below.

yarn rw prisma migrate dev --create-only

The following is a portion of this repository's RLS migration with explanations as to what each command is doing and why it's present.

-- 1) Enable row-level security on the "Tenants" table.
ALTER TABLE "Tenant" ENABLE ROW LEVEL SECURITY;

-- 2) Ensure security policies apply to the table's owner (by default they do not).
ALTER TABLE "Tenant" FORCE ROW LEVEL SECURITY;

-- 3) Create a policy which ensures the `app.tenantId` transaction parameter is equal to the target row's `id` column.
CREATE POLICY tenant ON "Tenant" USING ("id" = current_setting('app.tenantId', TRUE)::text);

-- 4) Create a policy allowing access to the table if the `app.bypass` parameter is equal to `'on'`.
CREATE POLICY tenant_bypass ON "Tenant" USING (current_setting('app.bypass', TRUE)::text = 'on');

Once you've enabled RLS and have defined all security policies, the migration can be applied to your database.

yarn rw prisma migrate dev

5) Use the extended client in services

Instead of importing the Prisma Client from api/src/lib/db, the client which is scoped to the request which invoked the service can be accessed as context.db - without the need to import from any files.

export const posts = () => {
  return context.db.post.findMany()
}

5.1) Use the bypass client

Some services or areas of your application will require you to bypass your security policies. The example below has been taken from this repository and highlights how a user is initially selected from a database before the extended client is added to the request's context.

import { bypassDb } from 'src/lib/db'

export const getCurrentUser = async (session) => {
  if (!session)
    throw new AuthenticationError('Invalid session')

  return bypassDb.user.findUnique({ where: { id: session.id } })
}

License

This example is available under the MIT license.

References

• Documentation on Prisma Client extensions
• Documentation on Raw database access
• Prisma Client Extension - Row Level Security example
• Postgres Row Security Policies
• RedwoodJS Documentation