- Home
- Skills
- APIs & Backend
- postgres rls multitenant
Works with the AI Agents you already use
postgres rls multitenant
Production-grade Postgres Row-Level Security (RLS) patterns for multi-tenant Node.js backends.
$15
postgres rls multitenant
Example session with this skill installed
I need to add RLS to a new 'projects' table in my NestJS app using Drizzle. Every project belongs to an organization. Only admins in that organization should be able to update them.
- Read your context and instructions
- Compiled the postgres rls multitenant
I will implement the RLS policy for the projects table, including the helper functions for identity context and the migration SQL.
Postgres RLS Implementation for Projects
The following SQL defines the tenant-scoped policy. It ensures that while all organization members can view projects, only users with the admin role within that tenant can modify them.
-- 1. Enable and Force RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;
-- 2. Define the Select Policy (All members of the tenant)
CREATE POLICY projects_tenant_isolation_select ON projects
FOR SELECT
TO app_user
USING (tenant_id = app_current_tenant_id());
-- 3. Define the Write Policy (Only tenant admins)
-- Note: 'app_is_tenant_manager()' is a helper that checks 'app.role' GUC
CREATE POLICY projects_tenant_admin_write ON projects
FOR ALL
TO app_user
USING (
tenant_id = app_current_tenant_id()
AND app_is_tenant_manager()
)
WITH CHECK (
tenant_id = app_current_tenant_id()
AND app_is_tenant_manager()
);
-- 4. Helper Function (if not exists)
CREATE OR REPLACE FUNCTION app_current_tenant_id() RETURNS uuid
LANGUAGE sql STABLE AS $$
SELECT NULLIF(current_setting('app.tenant_id', true), '')::uuid
$$;
In your Drizzle repository or middleware, ensure you wrap the query in a transaction that sets the local config:
await db.transaction(async (tx) => {
await tx.execute(sql`SELECT set_config('app.tenant_id', ${tenantId}, true)`);
await tx.execute(sql`SELECT set_config('app.role', ${userRole}, true)`);
// Proceed with application logic
return await tx.select().from(projects);
});
Next steps
- Run the migration using your
app_ownerrole (which must haveBYPASSRLS). - Add the
app_is_tenant_managerlogic to yoursql-templates.mdbased on your specific role mapping. - Update your connection pool configuration to include the
DISCARD ALLreset hook on release.
Connects securely to your tools. The creator never sees your data.
About this skill
Implementing multi-tenant isolation at the application layer is a liability. This skill provides the design patterns and implementation logic to move tenant security into the Postgres kernel using Row-Level Security (RLS), preventing cross-tenant data leaks even if your application code is compromised.
What it does
- Policy design classifies tables by scope (Global, Tenant, User, or Immutable) to apply the correct RLS predicates.
- Context management implements transaction-local
set_configpatterns to prevent session state from leaking across pooled connections. - Role provisioning configures a dual-role system separating the
app_owner(migrations/seeds) from theapp_user(runtime) with strictNOBYPASSRLSenforcement. - Defensive pooling provides hooks for Node.js drivers to force
ROLLBACKandDISCARD ALLon connection release. - Automated proofs includes test suites for pool-reuse isolation and write-policy mutation to verify security in CI.
How it works
- Classify your schema using the provided scope matrix to determine which tables require
tenant_idoruser_idcolumns. - Apply the SQL templates for helper functions and policies that read from transaction-local settings.
- Configure the Node.js backend using
AsyncLocalStorageto inject tenant context into every database transaction automatically. - Verify the implementation by running the isolation proof tests that simulate connection reuse and unauthorized write attempts.
Frameworks & tools
PostgreSQL (v15+ recommended), Node.js, TypeScript. Supported ORMs include Drizzle, Prisma, and raw pg driver. Compatible with NestJS, Fastify, Express, and Next.js.
Why this beats prompting it yourself
Generic LLM prompts often overlook the "connection pool leak" problem, where session-level settings persist between requests. This skill implements production-tested guards like DISCARD ALL and FORCE ROW LEVEL SECURITY that specifically address how Postgres behaves in pooled, multi-tenant environments.
Use cases
- Building a SaaS where users belong to organizations and must never see data from other workspaces.
- Hardening an existing Node.js backend against SQL injection by ensuring the database role has limited visibility.
- Implementing audit-ready immutable ledgers where rows can be inserted but never updated or deleted via RLS.
Known limitations
Does not support application-level authorization without Postgres policies. Not intended for Supabase-hosted projects using their internal auth.uid() model.
Find more skills like this on the Claude Skills Marketplace, the Codex Skills Marketplace and the Gemini Skills Marketplace, or browse Claude Skills for APIs & Backend.
Requires Postgres 15+ for default schema permission changes. Not for Supabase-specific auth.uid() implementations. Migrations must be run by a role with BYPASSRLS.
How to use
- 1
Connect your agent to the Agensi MCP
Copy the prompt and paste it into Claude, ChatGPT, Cursor or any agent that supports MCP. It sets up the connection for you. Manual setup
- 2
Sign in to Agensi
Your agent shows a sign-in link. Sign in and click Allow.
- 3
Ask for what you need
Your agent loads this skill and uses it on your task. It always asks before buying a skill.
Reviews
No reviews yet
Be one of the first to try it. Every listed skill goes through the trust checks below.
Security scanned
Checked by our 8-point scan before listing
Fresh listing
Recently published to Agensi
Money-back guarantee
Doesn't do what its page promises? You get your money back.
Trust & safety
Security scan
Security scan passed 12 days ago