Row Level Security والـ Multi-tenancy: كل عميل يشوف بياناته بس

import { pool, setup } from "./db.js";

await setup();

// كل الـ queries لازم فيها WHERE tenant_id… لحد ما حد ينسى
async function listInvoices(tenantId, search) {
  const { rows } = await pool.query(
    "SELECT tenant_id, customer FROM invoices WHERE customer LIKE $1",
    [`%${search}%`],
  );
  return rows;
}

console.log("tenant 1 بيدوّر على «م»:", await listInvoices(1, "م"));
await pool.end();

$ node main.js
tenant 1 بيدوّر على «م»: [
  { tenant_id: '1', customer: 'مكتبة الأمل' },
  { tenant_id: '2', customer: 'مطعم الفلّ' }
]

import { pool, setup } from "./db.js";

await setup();
await pool.query(`
  ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
  ALTER TABLE invoices FORCE ROW LEVEL SECURITY; -- حتى صاحب الجدول
  CREATE POLICY tenant_isolation ON invoices
    USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint)
    WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint);
`);

async function asTenant(tenantId, sql, params) {
  const c = await pool.connect();
  try {
    await c.query("BEGIN");
    // true = local: الإعداد ده للـ transaction دي بس
    await c.query("SELECT set_config('app.tenant_id', $1, true)", [String(tenantId)]);
    const { rows } = await c.query(sql, params);
    await c.query("COMMIT");
    return rows;
  } catch (err) {
    await c.query("ROLLBACK");
    throw err;
  } finally {
    c.release();
  }
}

console.log("tenant 1:", await asTenant(1, "SELECT customer FROM invoices WHERE customer LIKE $1", ["%م%"]));
console.log("tenant 2:", await asTenant(2, "SELECT customer FROM invoices"));
console.log("من غير tenant:", (await pool.query("SELECT count(*)::int AS n FROM invoices")).rows);

try {
  await asTenant(1, "INSERT INTO invoices (tenant_id, customer, amount) VALUES (2, 'دسيسة', 1)");
} catch (err) {
  console.log("tenant 1 بيكتب لـ tenant 2:", err.message);
}
console.log("tenant 1 بيمسح كل حاجة:", await asTenant(1, "DELETE FROM invoices RETURNING customer"));
console.log("tenant 2 بعدها:", await asTenant(2, "SELECT customer FROM invoices"));
await pool.end();

$ node main.js
tenant 1: [ { customer: 'مكتبة الأمل' } ]
tenant 2: [ { customer: 'مطعم الفلّ' } ]
من غير tenant: [ { n: 0 } ]
tenant 1 بيكتب لـ tenant 2: new row violates row-level security policy for table "invoices"
tenant 1 بيمسح كل حاجة: [ { customer: 'شركة النور' }, { customer: 'مكتبة الأمل' } ]
tenant 2 بعدها: [ { customer: 'مطعم الفلّ' } ]

import { pool, setup } from "./db.js";

await setup();
await pool.query(`
  ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
  ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
  CREATE POLICY tenant_isolation ON invoices
    USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint);
`);

// ❌ set_config بـ false = للـ session كلها، والـ connection بترجع للـ pool وهي شايلاه
await pool.query("SELECT set_config('app.tenant_id', '2', false)");
await pool.query("SELECT customer FROM invoices");

// request تاني من مستخدم مالوش tenant خالص… على نفس الـ connection
console.log("request جديد:", (await pool.query("SELECT customer FROM invoices")).rows);
await pool.end();

$ node main.js
request جديد: [ { customer: 'مطعم الفلّ' } ]

import { pool, setup } from "./db.js";

await setup();
await pool.query(`
  DROP TABLE IF EXISTS notes;
  CREATE TABLE notes (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id bigint NOT NULL DEFAULT NULLIF(current_setting('app.tenant_id', true), '')::bigint,
    body      text NOT NULL
  );
`);


await pool.query(`
  ALTER TABLE notes ENABLE ROW LEVEL SECURITY;
  ALTER TABLE notes FORCE ROW LEVEL SECURITY;
  CREATE POLICY notes_tenant ON notes
    USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint)
    WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint);
`);

async function withTenant(tenantId, fn) {
  const id = Number(tenantId);
  if (!Number.isInteger(id) || id <= 0) throw new Error("tenant غلط");
  const c = await pool.connect();
  try {
    await c.query("BEGIN");
    await c.query("SELECT set_config('app.tenant_id', $1, true)", [String(id)]);
    const result = await fn(c);
    await c.query("COMMIT");
    return result;
  } catch (err) {
    await c.query("ROLLBACK");
    throw err;
  } finally {
    c.release();
  }
}

await withTenant(1, (c) => c.query("INSERT INTO notes (body) VALUES ($1)", ["خطة 2027"]));
await withTenant(2, (c) => c.query("INSERT INTO notes (body) VALUES ($1)", ["منيو جديد"]));
for (const t of [1, 2]) {
  const { rows } = await withTenant(t, (c) => c.query("SELECT body FROM notes"));
  console.log(`tenant ${t}:`, rows.map((r) => r.body));
}
try {
  await withTenant("1 OR 1=1", (c) => c.query("SELECT body FROM notes"));
} catch (err) {
  console.log(String(err));
}
await pool.end();

$ node main.js
tenant 1: [ 'خطة 2027' ]
tenant 2: [ 'منيو جديد' ]
Error: tenant غلط