565 lines
14 KiB
TypeScript
565 lines
14 KiB
TypeScript
/**
|
|
* RLS Policy Application Utilities
|
|
*
|
|
* Centralized functions for applying Row-Level Security policies to database tables.
|
|
* These utilities work in conjunction with the SQL policy templates in rls-policies.sql.
|
|
*
|
|
* @module @erp-suite/core/database/policies
|
|
*
|
|
* @example
|
|
* ```typescript
|
|
* import { applyTenantIsolationPolicy, applyCompleteRlsPolicies } from '@erp-suite/core';
|
|
* import { Pool } from 'pg';
|
|
*
|
|
* const pool = new Pool({ ... });
|
|
*
|
|
* // Apply tenant isolation to a single table
|
|
* await applyTenantIsolationPolicy(pool, 'core', 'partners');
|
|
*
|
|
* // Apply complete policies (tenant + admin) to multiple tables
|
|
* await applyCompletePoliciesForSchema(pool, 'inventory', ['products', 'warehouses', 'locations']);
|
|
* ```
|
|
*/
|
|
|
|
import { Pool, PoolClient } from 'pg';
|
|
|
|
/**
|
|
* RLS Policy Configuration Options
|
|
*/
|
|
export interface RlsPolicyOptions {
|
|
/** Schema name */
|
|
schema: string;
|
|
/** Table name */
|
|
table: string;
|
|
/** Column name for tenant isolation (default: 'tenant_id') */
|
|
tenantColumn?: string;
|
|
/** Include admin bypass policy (default: true) */
|
|
includeAdminBypass?: boolean;
|
|
/** Custom user columns for user data policy */
|
|
userColumns?: string[];
|
|
}
|
|
|
|
/**
|
|
* RLS Policy Type
|
|
*/
|
|
export enum RlsPolicyType {
|
|
TENANT_ISOLATION = 'tenant_isolation',
|
|
USER_DATA = 'user_data',
|
|
READ_OWN_DATA = 'read_own_data',
|
|
WRITE_OWN_DATA = 'write_own_data',
|
|
ADMIN_BYPASS = 'admin_bypass',
|
|
}
|
|
|
|
/**
|
|
* RLS Policy Status
|
|
*/
|
|
export interface RlsPolicyStatus {
|
|
schema: string;
|
|
table: string;
|
|
rlsEnabled: boolean;
|
|
policies: Array<{
|
|
name: string;
|
|
command: string;
|
|
using: string | null;
|
|
check: string | null;
|
|
}>;
|
|
}
|
|
|
|
/**
|
|
* Apply tenant isolation policy to a table
|
|
*
|
|
* @param client - Database client or pool
|
|
* @param schema - Schema name
|
|
* @param table - Table name
|
|
* @param tenantColumn - Column name for tenant isolation (default: 'tenant_id')
|
|
* @returns Promise<void>
|
|
*
|
|
* @example
|
|
* ```typescript
|
|
* await applyTenantIsolationPolicy(pool, 'core', 'partners');
|
|
* await applyTenantIsolationPolicy(pool, 'inventory', 'products', 'company_id');
|
|
* ```
|
|
*/
|
|
export async function applyTenantIsolationPolicy(
|
|
client: Pool | PoolClient,
|
|
schema: string,
|
|
table: string,
|
|
tenantColumn: string = 'tenant_id',
|
|
): Promise<void> {
|
|
const query = `SELECT apply_tenant_isolation_policy($1, $2, $3)`;
|
|
await client.query(query, [schema, table, tenantColumn]);
|
|
}
|
|
|
|
/**
|
|
* Apply admin bypass policy to a table
|
|
*
|
|
* @param client - Database client or pool
|
|
* @param schema - Schema name
|
|
* @param table - Table name
|
|
* @returns Promise<void>
|
|
*
|
|
* @example
|
|
* ```typescript
|
|
* await applyAdminBypassPolicy(pool, 'financial', 'invoices');
|
|
* ```
|
|
*/
|
|
export async function applyAdminBypassPolicy(
|
|
client: Pool | PoolClient,
|
|
schema: string,
|
|
table: string,
|
|
): Promise<void> {
|
|
const query = `SELECT apply_admin_bypass_policy($1, $2)`;
|
|
await client.query(query, [schema, table]);
|
|
}
|
|
|
|
/**
|
|
* Apply user data policy to a table
|
|
*
|
|
* @param client - Database client or pool
|
|
* @param schema - Schema name
|
|
* @param table - Table name
|
|
* @param userColumns - Array of column names to check (default: ['created_by', 'assigned_to', 'owner_id'])
|
|
* @returns Promise<void>
|
|
*
|
|
* @example
|
|
* ```typescript
|
|
* await applyUserDataPolicy(pool, 'projects', 'tasks');
|
|
* await applyUserDataPolicy(pool, 'crm', 'leads', ['created_by', 'assigned_to']);
|
|
* ```
|
|
*/
|
|
export async function applyUserDataPolicy(
|
|
client: Pool | PoolClient,
|
|
schema: string,
|
|
table: string,
|
|
userColumns: string[] = ['created_by', 'assigned_to', 'owner_id'],
|
|
): Promise<void> {
|
|
const query = `SELECT apply_user_data_policy($1, $2, $3)`;
|
|
await client.query(query, [schema, table, userColumns]);
|
|
}
|
|
|
|
/**
|
|
* Apply complete RLS policies to a table (tenant isolation + optional admin bypass)
|
|
*
|
|
* @param client - Database client or pool
|
|
* @param schema - Schema name
|
|
* @param table - Table name
|
|
* @param tenantColumn - Column name for tenant isolation (default: 'tenant_id')
|
|
* @param includeAdminBypass - Whether to include admin bypass policy (default: true)
|
|
* @returns Promise<void>
|
|
*
|
|
* @example
|
|
* ```typescript
|
|
* await applyCompleteRlsPolicies(pool, 'core', 'partners');
|
|
* await applyCompleteRlsPolicies(pool, 'sales', 'orders', 'tenant_id', false);
|
|
* ```
|
|
*/
|
|
export async function applyCompleteRlsPolicies(
|
|
client: Pool | PoolClient,
|
|
schema: string,
|
|
table: string,
|
|
tenantColumn: string = 'tenant_id',
|
|
includeAdminBypass: boolean = true,
|
|
): Promise<void> {
|
|
const query = `SELECT apply_complete_rls_policies($1, $2, $3, $4)`;
|
|
await client.query(query, [schema, table, tenantColumn, includeAdminBypass]);
|
|
}
|
|
|
|
/**
|
|
* Apply complete RLS policies to multiple tables in a schema
|
|
*
|
|
* @param client - Database client or pool
|
|
* @param schema - Schema name
|
|
* @param tables - Array of table names
|
|
* @param options - Optional configuration
|
|
* @returns Promise<void>
|
|
*
|
|
* @example
|
|
* ```typescript
|
|
* await applyCompletePoliciesForSchema(pool, 'inventory', [
|
|
* 'products',
|
|
* 'warehouses',
|
|
* 'locations',
|
|
* 'lots'
|
|
* ]);
|
|
* ```
|
|
*/
|
|
export async function applyCompletePoliciesForSchema(
|
|
client: Pool | PoolClient,
|
|
schema: string,
|
|
tables: string[],
|
|
options?: {
|
|
tenantColumn?: string;
|
|
includeAdminBypass?: boolean;
|
|
},
|
|
): Promise<void> {
|
|
const { tenantColumn = 'tenant_id', includeAdminBypass = true } = options || {};
|
|
|
|
for (const table of tables) {
|
|
await applyCompleteRlsPolicies(
|
|
client,
|
|
schema,
|
|
table,
|
|
tenantColumn,
|
|
includeAdminBypass,
|
|
);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Check if RLS is enabled on a table
|
|
*
|
|
* @param client - Database client or pool
|
|
* @param schema - Schema name
|
|
* @param table - Table name
|
|
* @returns Promise<boolean> - True if RLS is enabled
|
|
*
|
|
* @example
|
|
* ```typescript
|
|
* const isEnabled = await isRlsEnabled(pool, 'core', 'partners');
|
|
* console.log(`RLS enabled: ${isEnabled}`);
|
|
* ```
|
|
*/
|
|
export async function isRlsEnabled(
|
|
client: Pool | PoolClient,
|
|
schema: string,
|
|
table: string,
|
|
): Promise<boolean> {
|
|
const query = `SELECT is_rls_enabled($1, $2) as enabled`;
|
|
const result = await client.query(query, [schema, table]);
|
|
return result.rows[0]?.enabled ?? false;
|
|
}
|
|
|
|
/**
|
|
* List all RLS policies on a table
|
|
*
|
|
* @param client - Database client or pool
|
|
* @param schema - Schema name
|
|
* @param table - Table name
|
|
* @returns Promise<RlsPolicyStatus> - Policy status information
|
|
*
|
|
* @example
|
|
* ```typescript
|
|
* const status = await listRlsPolicies(pool, 'core', 'partners');
|
|
* console.log(`Policies on core.partners:`, status.policies);
|
|
* ```
|
|
*/
|
|
export async function listRlsPolicies(
|
|
client: Pool | PoolClient,
|
|
schema: string,
|
|
table: string,
|
|
): Promise<RlsPolicyStatus> {
|
|
const rlsEnabled = await isRlsEnabled(client, schema, table);
|
|
|
|
const query = `SELECT * FROM list_rls_policies($1, $2)`;
|
|
const result = await client.query(query, [schema, table]);
|
|
|
|
return {
|
|
schema,
|
|
table,
|
|
rlsEnabled,
|
|
policies: result.rows.map((row) => ({
|
|
name: row.policy_name,
|
|
command: row.policy_cmd,
|
|
using: row.policy_using,
|
|
check: row.policy_check,
|
|
})),
|
|
};
|
|
}
|
|
|
|
/**
|
|
* Enable RLS on a table (without applying any policies)
|
|
*
|
|
* @param client - Database client or pool
|
|
* @param schema - Schema name
|
|
* @param table - Table name
|
|
* @returns Promise<void>
|
|
*
|
|
* @example
|
|
* ```typescript
|
|
* await enableRls(pool, 'core', 'custom_table');
|
|
* ```
|
|
*/
|
|
export async function enableRls(
|
|
client: Pool | PoolClient,
|
|
schema: string,
|
|
table: string,
|
|
): Promise<void> {
|
|
const query = `ALTER TABLE ${schema}.${table} ENABLE ROW LEVEL SECURITY`;
|
|
await client.query(query);
|
|
}
|
|
|
|
/**
|
|
* Disable RLS on a table
|
|
*
|
|
* @param client - Database client or pool
|
|
* @param schema - Schema name
|
|
* @param table - Table name
|
|
* @returns Promise<void>
|
|
*
|
|
* @example
|
|
* ```typescript
|
|
* await disableRls(pool, 'core', 'temp_table');
|
|
* ```
|
|
*/
|
|
export async function disableRls(
|
|
client: Pool | PoolClient,
|
|
schema: string,
|
|
table: string,
|
|
): Promise<void> {
|
|
const query = `ALTER TABLE ${schema}.${table} DISABLE ROW LEVEL SECURITY`;
|
|
await client.query(query);
|
|
}
|
|
|
|
/**
|
|
* Drop a specific RLS policy from a table
|
|
*
|
|
* @param client - Database client or pool
|
|
* @param schema - Schema name
|
|
* @param table - Table name
|
|
* @param policyName - Policy name to drop
|
|
* @returns Promise<void>
|
|
*
|
|
* @example
|
|
* ```typescript
|
|
* await dropRlsPolicy(pool, 'core', 'partners', 'tenant_isolation_partners');
|
|
* ```
|
|
*/
|
|
export async function dropRlsPolicy(
|
|
client: Pool | PoolClient,
|
|
schema: string,
|
|
table: string,
|
|
policyName: string,
|
|
): Promise<void> {
|
|
const query = `DROP POLICY IF EXISTS ${policyName} ON ${schema}.${table}`;
|
|
await client.query(query);
|
|
}
|
|
|
|
/**
|
|
* Drop all RLS policies from a table
|
|
*
|
|
* @param client - Database client or pool
|
|
* @param schema - Schema name
|
|
* @param table - Table name
|
|
* @returns Promise<number> - Number of policies dropped
|
|
*
|
|
* @example
|
|
* ```typescript
|
|
* const count = await dropAllRlsPolicies(pool, 'core', 'partners');
|
|
* console.log(`Dropped ${count} policies`);
|
|
* ```
|
|
*/
|
|
export async function dropAllRlsPolicies(
|
|
client: Pool | PoolClient,
|
|
schema: string,
|
|
table: string,
|
|
): Promise<number> {
|
|
const status = await listRlsPolicies(client, schema, table);
|
|
|
|
for (const policy of status.policies) {
|
|
await dropRlsPolicy(client, schema, table, policy.name);
|
|
}
|
|
|
|
return status.policies.length;
|
|
}
|
|
|
|
/**
|
|
* Batch apply RLS policies to tables based on configuration
|
|
*
|
|
* @param client - Database client or pool
|
|
* @param configs - Array of policy configurations
|
|
* @returns Promise<void>
|
|
*
|
|
* @example
|
|
* ```typescript
|
|
* await batchApplyRlsPolicies(pool, [
|
|
* { schema: 'core', table: 'partners' },
|
|
* { schema: 'core', table: 'addresses' },
|
|
* { schema: 'inventory', table: 'products', includeAdminBypass: false },
|
|
* { schema: 'projects', table: 'tasks', tenantColumn: 'company_id' },
|
|
* ]);
|
|
* ```
|
|
*/
|
|
export async function batchApplyRlsPolicies(
|
|
client: Pool | PoolClient,
|
|
configs: RlsPolicyOptions[],
|
|
): Promise<void> {
|
|
for (const config of configs) {
|
|
const {
|
|
schema,
|
|
table,
|
|
tenantColumn = 'tenant_id',
|
|
includeAdminBypass = true,
|
|
} = config;
|
|
|
|
await applyCompleteRlsPolicies(
|
|
client,
|
|
schema,
|
|
table,
|
|
tenantColumn,
|
|
includeAdminBypass,
|
|
);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Get RLS status for all tables in a schema
|
|
*
|
|
* @param client - Database client or pool
|
|
* @param schema - Schema name
|
|
* @returns Promise<RlsPolicyStatus[]> - Array of policy statuses
|
|
*
|
|
* @example
|
|
* ```typescript
|
|
* const statuses = await getSchemaRlsStatus(pool, 'core');
|
|
* statuses.forEach(status => {
|
|
* console.log(`${status.table}: RLS ${status.rlsEnabled ? 'enabled' : 'disabled'}, ${status.policies.length} policies`);
|
|
* });
|
|
* ```
|
|
*/
|
|
export async function getSchemaRlsStatus(
|
|
client: Pool | PoolClient,
|
|
schema: string,
|
|
): Promise<RlsPolicyStatus[]> {
|
|
// Get all tables in schema
|
|
const tablesQuery = `
|
|
SELECT table_name
|
|
FROM information_schema.tables
|
|
WHERE table_schema = $1
|
|
AND table_type = 'BASE TABLE'
|
|
ORDER BY table_name
|
|
`;
|
|
const tablesResult = await client.query(tablesQuery, [schema]);
|
|
|
|
const statuses: RlsPolicyStatus[] = [];
|
|
for (const row of tablesResult.rows) {
|
|
const status = await listRlsPolicies(client, schema, row.table_name);
|
|
statuses.push(status);
|
|
}
|
|
|
|
return statuses;
|
|
}
|
|
|
|
/**
|
|
* Set session context for RLS (tenant_id, user_id, user_role)
|
|
*
|
|
* @param client - Database client or pool
|
|
* @param context - Session context
|
|
* @returns Promise<void>
|
|
*
|
|
* @example
|
|
* ```typescript
|
|
* await setRlsContext(client, {
|
|
* tenantId: 'uuid-tenant-id',
|
|
* userId: 'uuid-user-id',
|
|
* userRole: 'admin',
|
|
* });
|
|
* ```
|
|
*/
|
|
export async function setRlsContext(
|
|
client: Pool | PoolClient,
|
|
context: {
|
|
tenantId?: string;
|
|
userId?: string;
|
|
userRole?: string;
|
|
},
|
|
): Promise<void> {
|
|
const { tenantId, userId, userRole } = context;
|
|
|
|
if (tenantId) {
|
|
await client.query(`SET app.current_tenant_id = $1`, [tenantId]);
|
|
}
|
|
|
|
if (userId) {
|
|
await client.query(`SET app.current_user_id = $1`, [userId]);
|
|
}
|
|
|
|
if (userRole) {
|
|
await client.query(`SET app.current_user_role = $1`, [userRole]);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Clear RLS context from session
|
|
*
|
|
* @param client - Database client or pool
|
|
* @returns Promise<void>
|
|
*
|
|
* @example
|
|
* ```typescript
|
|
* await clearRlsContext(client);
|
|
* ```
|
|
*/
|
|
export async function clearRlsContext(client: Pool | PoolClient): Promise<void> {
|
|
await client.query(`RESET app.current_tenant_id`);
|
|
await client.query(`RESET app.current_user_id`);
|
|
await client.query(`RESET app.current_user_role`);
|
|
}
|
|
|
|
/**
|
|
* Helper: Execute function within RLS context
|
|
*
|
|
* @param client - Database client or pool
|
|
* @param context - RLS context
|
|
* @param fn - Function to execute
|
|
* @returns Promise<T> - Result of the function
|
|
*
|
|
* @example
|
|
* ```typescript
|
|
* const result = await withRlsContext(pool, {
|
|
* tenantId: 'tenant-uuid',
|
|
* userId: 'user-uuid',
|
|
* userRole: 'user',
|
|
* }, async (client) => {
|
|
* return await client.query('SELECT * FROM core.partners');
|
|
* });
|
|
* ```
|
|
*/
|
|
export async function withRlsContext<T>(
|
|
client: Pool | PoolClient,
|
|
context: {
|
|
tenantId?: string;
|
|
userId?: string;
|
|
userRole?: string;
|
|
},
|
|
fn: (client: Pool | PoolClient) => Promise<T>,
|
|
): Promise<T> {
|
|
await setRlsContext(client, context);
|
|
try {
|
|
return await fn(client);
|
|
} finally {
|
|
await clearRlsContext(client);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Export all RLS utility functions
|
|
*/
|
|
export default {
|
|
// Policy application
|
|
applyTenantIsolationPolicy,
|
|
applyAdminBypassPolicy,
|
|
applyUserDataPolicy,
|
|
applyCompleteRlsPolicies,
|
|
applyCompletePoliciesForSchema,
|
|
batchApplyRlsPolicies,
|
|
|
|
// RLS management
|
|
enableRls,
|
|
disableRls,
|
|
isRlsEnabled,
|
|
listRlsPolicies,
|
|
dropRlsPolicy,
|
|
dropAllRlsPolicies,
|
|
|
|
// Status and inspection
|
|
getSchemaRlsStatus,
|
|
|
|
// Context management
|
|
setRlsContext,
|
|
clearRlsContext,
|
|
withRlsContext,
|
|
|
|
// Types
|
|
RlsPolicyType,
|
|
};
|