How to Use the PostgreSQL Row Level Security Policy Writer Prompt to Isolate Tenants Safely

Row level security lets PostgreSQL enforce tenant isolation inside the database, so a missed WHERE clause in the app does not expose another customer's data. It only works when the details are right. The app must not connect as the table owner, the tenant setting must not leak between pooled requests, and views must run with the caller's rights. The PostgreSQL Row Level Security Policy Writer for Multi Tenant SaaS: Tenant Context with set_config, USING vs WITH CHECK, FORCE ROW LEVEL SECURITY, Roles Without BYPASSRLS, Pooler Safety, and Isolation Tests prompt writes the roles, policies, application code, and tests for your schema.

What the prompt produces

1. A role plan with an owner role for migrations, an application role that cannot bypass security, and a separate role for any cross tenant job.
2. A tenant context function that returns no rows when the tenant is not set.
3. Policies for each table with ENABLE and FORCE row level security, plus both USING and WITH CHECK clauses.
4. Composite foreign keys and indexes so child rows cannot point at another tenant's parent.
5. Application code that sets the tenant per transaction, safe under transaction pooling.
6. View and function review, including security_invoker for newer PostgreSQL versions.
7. Isolation tests in SQL that roll back.
8. Verification queries confirming security is enabled and the app role cannot bypass it.

How to fill the inputs

SchemaDDL holds the CREATE TABLE statements and the tenant column on each table.

CurrentRoles says which role owns the tables and which role the app uses today.

ConnectionSetup names the driver or ORM and any pooler with its pool mode. Transaction pooling changes how the tenant must be set.

AccessRules covers anything beyond basic isolation, such as admins, support staff, or reporting jobs.

PgVersion is your PostgreSQL major version.

ViewsAndFunctions lists views and functions that read tenant tables.

Reading the example output

The example secures a projects and invoices schema for a Node app behind PgBouncer in transaction mode:

• The app moves to a new role that is not the owner and has NOBYPASSRLS. The owner role stays for migrations.
• A billing export role gets read access across tenants so the nightly job does not need the app role.
• The tenant function turns an empty setting into NULL, which avoids a cast error after a transaction ends and returns no rows.
• Both tables get ENABLE and FORCE, with policies that use USING for reads and WITH CHECK for writes.
• A composite foreign key ties each invoice's project to the same tenant.
• The view is set to security_invoker, so it respects the caller's policies.
• The Node snippet sets the tenant with set_config inside a transaction, which is safe under transaction pooling.
• The tests confirm zero rows without a tenant, one tenant's rows with it set, and an error on a cross tenant insert.

Tips for better results

• Run the tests as part of your migration checks.
• Add the tenant column to every index your policies filter on.
• Review SECURITY DEFINER functions, since they run with their owner's rights.
• Keep a short list of roles allowed to bypass security and audit it.
• Test with your real pooler, not a direct connection.

Mistakes to avoid

• Do not let the app connect as the table owner or a superuser.
• Do not use session level SET for the tenant under transaction pooling.
• Do not write USING without WITH CHECK on tables the app writes to.
• Do not forget that unique constraints can reveal that a value exists in another tenant.

Who it is for

Backend developers building SaaS products, database engineers adding isolation to an existing schema, security reviewers checking multi tenant designs, and teams moving from app only filtering to database enforced rules.

Related PromptDig links

Open the PostgreSQL Row Level Security Policy Writer for Multi Tenant SaaS: Tenant Context with set_config, USING vs WITH CHECK, FORCE ROW LEVEL SECURITY, Roles Without BYPASSRLS, Pooler Safety, and Isolation Tests prompt and paste your schema. For more coding prompts, Browse more prompts. If you have a database prompt that works, Share a prompt.