Recently, I attempted to implement client extension as advised on Github. My approach involved defining row level security policies in my migration.sql file:
-- Enabling Row Level Security
ALTER TABLE "User" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Company" ENABLE ROW LEVEL SECURITY;
-- Applying Row Level Security for table owners
ALTER TABLE "User" FORCE ROW LEVEL SECURITY;
ALTER TABLE "Company" FORCE ROW LEVEL SECURITY;
-- Defining row security policies
CREATE POLICY tenant_isolation_policy ON "Company" USING ("id" = current_setting('app.current_company_id', TRUE)::uuid);
CREATE POL
In addition, I have specified only two models in my schema.prisma file:
generator client {
provider = "prisma-client-js"
previewFeatures = ["clientExtensions"]
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model Company {
id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
name String
users User[]
}
model User {
id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
companyId String @default(dbgenerated("(current_setting('app.current_company_id'::text))::uuid")) @db.Uuid
email String @unique
company Company @relation(fields: [companyId], references: [id], onDelete: Cascade)
}
Furthermore, my script.js file contains various functions aimed at validating the functionality of RLS. Despite creating test data for both company and user tables, I am facing difficulties in restricting access to companies or users as required.
//Setting a company_id as current_company_id for RLS Validation
const setUserId = await prisma.$executeRaw`SET app.current_company_id = 'id_of_a_company';`;
// Creating a specific company with a user
await prisma.company.create({
data: {
name: "Company 1",
users: {
create: {
email: "<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="e693958394d7a685898b9687889fd7c885898b">[email protected]</a>",
},
},
},
});
// Attempting to query users from Company 1 as a user who should have access
const company1Users = await prisma.user.findMany({
where: {
company: {
name: "Company 1",
},
},
});
// Trying to fetch users from Company 2 as a user without access privileges
const company2Users = await prisma.user.findMany({
where: {
company: {
name: "Company 2",
},
},
});
Any insights on what could be going wrong here?