erp-suite/apps/shared-libs/core/database/policies

RLS Policies - ERP Suite Shared Library

Centralized Row-Level Security (RLS) policies for multi-tenant isolation across all ERP-Suite modules.

Overview

This module provides:

• 5 generic RLS policy templates (SQL)
• TypeScript functions for dynamic policy application
• Helper utilities for RLS management and context handling

Files

• rls-policies.sql - SQL policy templates and helper functions
• apply-rls.ts - TypeScript utilities for applying RLS policies
• README.md - This documentation
• usage-example.ts - Usage examples

RLS Policy Types

1. Tenant Isolation Policy

Purpose: Ensures users can only access data from their own tenant.

Usage: Apply to all tables with tenant_id column.

-- SQL
SELECT apply_tenant_isolation_policy('core', 'partners');

// TypeScript
import { applyTenantIsolationPolicy } from '@erp-suite/core';
await applyTenantIsolationPolicy(pool, 'core', 'partners');

2. User Data Policy

Purpose: Restricts access to data created by or assigned to the current user.

Usage: Apply to tables with created_by, assigned_to, or owner_id columns.

-- SQL
SELECT apply_user_data_policy('projects', 'tasks', ARRAY['created_by', 'assigned_to']::TEXT[]);

// TypeScript
import { applyUserDataPolicy } from '@erp-suite/core';
await applyUserDataPolicy(pool, 'projects', 'tasks', ['created_by', 'assigned_to']);

3. Read Own Data Policy

Purpose: Allows users to read only their own data (SELECT only).

Usage: Apply when users need read access to own data but restricted write.

-- See rls-policies.sql for template
-- No dedicated function yet - use custom SQL

4. Write Own Data Policy

Purpose: Allows users to insert/update/delete only their own data.

Usage: Companion to READ_OWN_DATA_POLICY for write operations.

-- See rls-policies.sql for template
-- No dedicated function yet - use custom SQL

5. Admin Bypass Policy

Purpose: Allows admin users to bypass RLS restrictions for support/management.

Usage: Apply as permissive policy to allow admin full access.

-- SQL
SELECT apply_admin_bypass_policy('financial', 'invoices');

// TypeScript
import { applyAdminBypassPolicy } from '@erp-suite/core';
await applyAdminBypassPolicy(pool, 'financial', 'invoices');

Quick Start

1. Apply RLS to Database

First, run the SQL functions to create the helper functions:

psql -d your_database -f apps/shared-libs/core/database/policies/rls-policies.sql

2. Apply Policies to Tables

Single Table

import { applyCompleteRlsPolicies } from '@erp-suite/core';
import { Pool } from 'pg';

const pool = new Pool({ /* config */ });

// Apply tenant isolation + admin bypass
await applyCompleteRlsPolicies(pool, 'core', 'partners');

Multiple Tables

import { applyCompletePoliciesForSchema } from '@erp-suite/core';

await applyCompletePoliciesForSchema(pool, 'inventory', [
  'products',
  'warehouses',
  'locations',
  'lots'
]);

Batch Application

import { batchApplyRlsPolicies } from '@erp-suite/core';

await batchApplyRlsPolicies(pool, [
  { schema: 'core', table: 'partners' },
  { schema: 'core', table: 'addresses' },
  { schema: 'inventory', table: 'products', includeAdminBypass: false },
  { schema: 'projects', table: 'tasks', tenantColumn: 'company_id' },
]);

3. Set RLS Context

Before querying data, set the session context:

import { setRlsContext, withRlsContext } from '@erp-suite/core';

// Manual context setting
await setRlsContext(pool, {
  tenantId: 'uuid-tenant-id',
  userId: 'uuid-user-id',
  userRole: 'admin',
});

// Or use helper for scoped context
const result = await withRlsContext(pool, {
  tenantId: 'tenant-uuid',
  userId: 'user-uuid',
  userRole: 'user',
}, async (client) => {
  return await client.query('SELECT * FROM core.partners');
});

API Reference

Policy Application

Function	Description
applyTenantIsolationPolicy()	Apply tenant isolation policy to a table
applyAdminBypassPolicy()	Apply admin bypass policy to a table
applyUserDataPolicy()	Apply user data policy to a table
applyCompleteRlsPolicies()	Apply complete policies (tenant + admin)
applyCompletePoliciesForSchema()	Apply to multiple tables in a schema
batchApplyRlsPolicies()	Batch apply policies to multiple tables

RLS Management

Function	Description
enableRls()	Enable RLS on a table
disableRls()	Disable RLS on a table
isRlsEnabled()	Check if RLS is enabled
listRlsPolicies()	List all policies on a table
dropRlsPolicy()	Drop a specific policy
dropAllRlsPolicies()	Drop all policies from a table

Status and Inspection

Function	Description
getSchemaRlsStatus()	Get RLS status for all tables in a schema

Context Management

Function	Description
setRlsContext()	Set session context for RLS
clearRlsContext()	Clear RLS context from session
withRlsContext()	Execute function within RLS context

Migration Guide

From Vertical-Specific RLS to Centralized

Before (in vertical migrations):

-- apps/verticales/construccion/database/migrations/001-rls.sql
CREATE OR REPLACE FUNCTION get_current_tenant_id() ...;
CREATE POLICY tenant_isolation_projects ON projects.projects ...;

After (using shared-libs):

// apps/verticales/construccion/database/migrations/001-rls.ts
import { applyCompleteRlsPolicies } from '@erp-suite/core';
import { pool } from '../db';

export async function up() {
  await applyCompleteRlsPolicies(pool, 'projects', 'projects');
}

Best Practices

1. Always apply tenant isolation to tables with tenant_id
2. Include admin bypass for support and troubleshooting (default: enabled)
3. Use user data policies for user-specific tables (tasks, notifications, etc.)
4. Set RLS context in middleware or at the application boundary
5. Test RLS policies thoroughly before deploying to production
6. Document custom policies if you deviate from the templates

Testing RLS Policies

Check if RLS is enabled

import { isRlsEnabled } from '@erp-suite/core';

const enabled = await isRlsEnabled(pool, 'core', 'partners');
console.log(`RLS enabled: ${enabled}`);

List policies

import { listRlsPolicies } from '@erp-suite/core';

const status = await listRlsPolicies(pool, 'core', 'partners');
console.log(`Policies on core.partners:`, status.policies);

Get schema-wide status

import { getSchemaRlsStatus } from '@erp-suite/core';

const statuses = await getSchemaRlsStatus(pool, 'core');
statuses.forEach(status => {
  console.log(`${status.table}: RLS ${status.rlsEnabled ? 'enabled' : 'disabled'}, ${status.policies.length} policies`);
});

Troubleshooting

RLS blocking legitimate queries?

1. Check that RLS context is set correctly:

   await setRlsContext(pool, { tenantId, userId, userRole });
   

2. Verify the policy USING clause matches your data:

   const status = await listRlsPolicies(pool, 'schema', 'table');
   console.log(status.policies);
   

3. Test with admin bypass to isolate the issue:

   await setRlsContext(pool, { userRole: 'admin' });
   

Policy not being applied?

1. Ensure RLS is enabled:

   const enabled = await isRlsEnabled(pool, 'schema', 'table');
   

2. Check that the policy exists:

   const status = await listRlsPolicies(pool, 'schema', 'table');
   

3. Verify the session context is set:

   SELECT current_setting('app.current_tenant_id', true);
   

Examples

See usage-example.ts for complete working examples.

Contributing

When adding new RLS policy types:

1. Add SQL template to rls-policies.sql
2. Add TypeScript function to apply-rls.ts
3. Export from index.ts
4. Update this README
5. Add example to usage-example.ts

Support

For questions or issues, contact the ERP-Suite core team or file an issue in the repository.