Works with the AI Agents you already use

    Claude CodeClaude CodeCursorCursorCodex CLICodex CLIMuseMuseDotsDots+21 more

    postgres rls multitenant

    1

    Production-grade Postgres Row-Level Security (RLS) patterns for multi-tenant Node.js backends.

    $15

    Secure checkout via Stripe

    Money-back guarantee

    Converts to your local currency at checkout

    Security scanned
    postgres rls multitenant

    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

    1. Run the migration using your app_owner role (which must have BYPASSRLS).
    2. Add the app_is_tenant_manager logic to your sql-templates.md based on your specific role mapping.
    3. Update your connection pool configuration to include the DISCARD ALL reset 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_config patterns to prevent session state from leaking across pooled connections.
    • Role provisioning configures a dual-role system separating the app_owner (migrations/seeds) from the app_user (runtime) with strict NOBYPASSRLS enforcement.
    • Defensive pooling provides hooks for Node.js drivers to force ROLLBACK and DISCARD ALL on connection release.
    • Automated proofs includes test suites for pool-reuse isolation and write-policy mutation to verify security in CI.

    How it works

    1. Classify your schema using the provided scope matrix to determine which tables require tenant_id or user_id columns.
    2. Apply the SQL templates for helper functions and policies that read from transaction-local settings.
    3. Configure the Node.js backend using AsyncLocalStorage to inject tenant context into every database transaction automatically.
    4. 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.

    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. 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. 2

      Sign in to Agensi

      Your agent shows a sign-in link. Sign in and click Allow.

    3. 3

      Ask for what you need

      Your agent loads this skill and uses it on your task. It always asks before any payment.

    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

    Permissions

    Read Files
    Environment Variables
    Listed12 days ago

    What's inside

    Frequently Asked Questions

    This skill moves multi-tenant data isolation from the application layer to the database kernel using Postgres Row-Level Security (RLS). It prevents cross-tenant data leaks by ensuring that even if an application bug or SQL injection occurs, the database role can only access data belonging to the specific tenant context of the current transaction.

    Unlike generic prompts, this skill includes production-grade logic for connection pooling, specifically preventing 'session leakage' where one user's identity persists to the next request. It uses transaction-local settings and specific SQL cleanup commands to ensure isolation remains intact in high-concurrency Node.js environments.

    You will receive a comprehensive implementation kit including SQL policy templates for different table scopes, Node.js middleware for context injection using AsyncLocalStorage, and a specialized test suite to verify isolation within your CI/CD pipeline.

    It is designed for Node.js backends using TypeScript or JavaScript and works with the 'pg' driver, Drizzle, and Prisma. It requires PostgreSQL v15 or newer to support the latest security features and is compatible with any major framework like NestJS, Express, or Next.js.

    This skill is optimized for custom-hosted Postgres environments (like AWS RDS, GCP Cloud SQL, or self-hosted). It is not recommended for Supabase projects that rely on their proprietary `auth.uid()` functions, as this implementation uses a different context management pattern.

    The skill provides a 'dual-role' architecture: one role for schema migrations (app_owner) and a restricted runtime role (app_user). This ensures your application never runs with full administrative privileges, adhering to the principle of least privilege.

    New in APIs & Backend