Using Row-Level Security in Prisma
Row-level security (RLS) in PostgreSQL enables tables to implement policies that limit access or modification of rows
according to the user's role, enhancing the basic SQL-standard privileges provided by GRANT.
Once activated, all access to the table has to adhere to these policies. If no policies are defined on the table, it defaults to a deny-all rule, meaning no rows can be seen or mutated. These policies can be tailored to specific commands, roles, or both, allowing for detailed management of who can access or change data.
This guide explains how to attach RLS Policies to your types (objects) and configure the schema migration to manage both the RLS and the Prisma models as a single migration unit using Atlas.
Atlas support for Row-Level Security Policies used in this guide is available exclusively to Pro users. To use this feature, run:
atlas login
Getting started with Atlas and Prisma
To set up, follow this guide to bootstrap your project. This example project will be used to demonstrate how to attach RLS policies to your types and manage them alongside your Prisma models.
Composite Schema
The schema.prisma is mostly used for defining tables and interacting with the database. Table policies
or any other database native objects do not have representation in Prisma models.
In order to extend our PostgreSQL schema to include both our Prisma types and their policies, we configure Atlas to read the state of the schema from a Composite Schema data source. Follow the steps below to configure this for your project:
1. Let's add a new model tenant and column tenantId in "user" model to the schema.prisma file:
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema
// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model User {
id Int @id @default(autoincrement())
name String
email String @unique
tenantId Int
}
model Tenant {
id Int @id @default(autoincrement())
name String
}
2. Now, suppose we want to limit access to the users table based on the tenant_id field. We can achieve this by defining
a Row-Level Security (RLS) policy on the users table. Below is the SQL code that defines the RLS policy:
--- Enable row-level security on the users table.
ALTER TABLE "User" ENABLE ROW LEVEL SECURITY;
-- Create a policy that restricts access to rows in the users table based on the current tenant.
CREATE POLICY tenant_isolation ON "User"
USING ("tenantId" = current_setting('app.current_tenant')::integer);
3. Lastly, In your atlas.hcl config file, add a composite_schema that includes both your
custom security policies defined in schema.sql and your Prisma model:
data "external_schema" "prisma" {
program = [
"npx",
"prisma",
"migrate",
"diff",
"--from-empty",
"--to-schema-datamodel",
"prisma/schema.prisma",
"--script"
]
}
data "composite_schema" "app" {
# Load the GORM model first
schema "public" {
url = data.external_schema.prisma.url
}
# Next, load the RLS schema.
schema "public" {
url = "file://atlas/schema.sql"
}
}
env "local" {
dev = "docker://postgres/16/dev?search_path=public"
schema {
# Replace with composite_schema
src = data.composite_schema.app.url
}
migration {
dir = "file://atlas/migrations"
}
}
Usage
After setting up our composite schema, we can get its representation using the atlas schema inspect command, generate
schema migrations for it, apply them to a database, and more. Below are a few commands to get you started with Atlas:
Inspect the Schema
The atlas schema inspect command is commonly used to inspect databases. However, we can also use it to inspect our
composite_schema and print the SQL representation of it:
atlas schema inspect --env local --url "env://schema.src" --format '{{ sql . }}'
The command above prints the following SQL. Note, the tenant_isolation policy is defined in the schema after the users
table:
-- Create "User" table
CREATE TABLE "User" ("id" serial NOT NULL, "name" text NOT NULL, "email" text NOT NULL, "tenantId" integer NOT NULL, PRIMARY KEY ("id"));
-- Create index "User_email_key" to table: "User"
CREATE UNIQUE INDEX "User_email_key" ON "User" ("email");
-- Enable row-level security for "User" table
ALTER TABLE "User" ENABLE ROW LEVEL SECURITY;
-- Create policy "tenant_isolation"
CREATE POLICY "tenant_isolation" ON "User" AS PERMISSIVE FOR ALL TO PUBLIC USING ("tenantId" = (current_setting('app.current_tenant'::text))::integer);
-- Create "Tenant" table
CREATE TABLE "Tenant" ("id" serial NOT NULL, "name" text NOT NULL, PRIMARY KEY ("id"));
Generate Migrations For the Schema
To generate a migration for the schema, run the following command:
atlas migrate diff \
--env local
Note that a new migration file is created with the following content:
-- Modify "User" table
ALTER TABLE "User" ENABLE ROW LEVEL SECURITY, ADD COLUMN "tenantId" integer NOT NULL;
-- Create policy "tenant_isolation"
CREATE POLICY "tenant_isolation" ON "User" AS PERMISSIVE FOR ALL TO PUBLIC USING ("tenantId" = (current_setting('app.current_tenant'::text))::integer);
-- Create "Tenant" table
CREATE TABLE "Tenant" ("id" serial NOT NULL, "name" text NOT NULL, PRIMARY KEY ("id"));
Apply the Migrations
To apply the migration generated above to a database, run the following command:
atlas migrate apply \
--env local \
--url "postgresql://postgres:postgres@:5432/postgres?search_path=public&sslmode=disable"
Sometimes, there is a need to apply the schema directly to the database without generating a migration file. For example, when experimenting with schema changes, spinning up a database for testing, etc. In such cases, you can use the command below to apply the schema directly to the database:
atlas schema apply \
--env local \
--url "postgresql://postgres:postgres@:5432/postgres?search_path=public&sslmode=disable"
Code Example
After setting up our Prisma model and the RLS policies, we can open a Prisma client and pass the different mutations and queries the relevant tenant ID we work on. This ensures that the database upholds our RLS policy.
import { Prisma, PrismaClient } from "@prisma/client";
function forTenant(tenantId: number) {
return Prisma.defineExtension((prisma) =>
prisma.$extends({
query: {
$allModels: {
async $allOperations({ args, query }) {
const [, result] = await prisma.$transaction([
prisma.$executeRaw`SELECT set_config('app.current_tenant', ${tenantId}::text, TRUE)`,
query(args),
])
return result;
},
},
},
})
);
}
const prisma = new PrismaClient();
const tenantPrisma = prisma.$extends(forTenant(1));
tenantPrisma.user.findMany().then((users: any) => {
console.log("Users for Tenant 1:", users);
})
const tenantPrisma2 = prisma.$extends(forTenant(2));
tenantPrisma2.user.findMany().then((users: any) => {
console.log("Users for Tenant 2:", users);
})