Identity & ACL (Postgres RLS)

User → PropertyOwner (org) hierarchy. Row-level security in Postgres enforces "owner sees only their data" at the DB layer, structurally. Replaces XAF's implicit CurrentUserBoOid / CurrentPropertyOwnerOid custom operators.

Phase 1Core dependency

Why RLS, not app-layer checks

Identity model

PropertyOwner (organization)
 ├── User (membership: tenant | owner-accountant | owner-director | employee)
 │     └── Profile (extended user info)
 └── Property
       └── Room
             └── Contract (tenant_user_id, room_id)
                   └── Invoice

Admin role = global, sees everything (bypasses RLS via policy).

Tables (Drizzle)

// packages/db/src/schema/property-owners.ts
import { pgTable, uuid, text, timestamp, pgPolicy } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";

export const propertyOwners = pgTable("property_owners", {
  id: uuid().primaryKey().defaultRandom(),
  name: text().notNull(),
  tinNumber: text("tin_number"),         // tax ID
  phone: text(),
  email: text(),
  bankAccount: text("bank_account"),
  createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp({ withTimezone: true })
    .notNull().defaultNow()
    .$onUpdate(() => new Date()),
}, (t) => [
  pgPolicy("po_owner_self", {
    as: "permissive", for: "all", to: "authenticated",
    using: sql`id::text = current_setting('app.owner_id', true)
               or current_setting('app.role', true) = 'admin'`,
  }),
]);

// packages/db/src/schema/profiles.ts
export const profiles = pgTable("profiles", {
  id: uuid().primaryKey().defaultRandom(),
  userId: uuid("user_id").notNull().references(() => users.id, { onDelete: "cascade" }).unique(),
  ownerId: uuid("owner_id").references(() => propertyOwners.id),
  fullName: text("full_name").notNull(),
  position: text({ enum: ["tenant","owner_accountant","owner_director","employee","admin"] }).notNull(),
  registrationNo: text("registration_no"),
  phone: text(),
  avatarUrl: text("avatar_url"),
  createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  pgPolicy("profile_self_or_org", {
    as: "permissive", for: "select", to: "authenticated",
    using: sql`user_id::text = current_setting('app.user_id', true)
               or owner_id::text = current_setting('app.owner_id', true)
               or current_setting('app.role', true) = 'admin'`,
  }),
]);

The RLS pattern (copy-paste for every domain table)

Every owner-scoped table follows this shape:

export const contracts = pgTable("contracts", {
  id: uuid().primaryKey().defaultRandom(),
  ownerId: uuid("owner_id").notNull().references(() => propertyOwners.id),
  // ...domain fields
}, (t) => [
  index("contracts_owner_idx").on(t.ownerId),            // CRITICAL: index the FK in the policy
  pgPolicy("contract_owner_scope", {
    as: "permissive", for: "all", to: "authenticated",
    using: sql`owner_id::text = (select current_setting('app.owner_id', true))   -- (select) caches per statement
               or current_setting('app.role', true) = 'admin'`,
  }),
]);
Two perf rules:
  1. Always index the column the policy filters on (owner_id here).
  2. Always wrap session-state subqueries in (select ...) so Postgres caches them per statement instead of re-evaluating per row.

Role definitions

// packages/db/src/schema/roles.ts
import { pgRole } from "drizzle-orm/pg-core";

export const authenticated = pgRole("authenticated").existing();   // any logged-in user
export const appAdmin     = pgRole("app_admin").existing();        // global admin (rare; bypasses scoping)
// We DON'T create per-tenant roles. Tenancy is via GUCs.

Database setup (one-time, via migration):

-- migrations/manual/0001_roles.sql
CREATE ROLE authenticated NOLOGIN;
CREATE ROLE app_admin NOLOGIN;
GRANT authenticated TO app_admin;       -- admin inherits everything authenticated can do
-- Grant the API connection user the ability to SET ROLE both:
GRANT authenticated, app_admin TO spacehub;

RLS middleware (the bridge)

// apps/api/src/middleware/rls.ts
import { createMiddleware } from "hono/factory";
import { sql } from "@spacehub/db";
import { db } from "../lib/db";

export const withRls = createMiddleware<{ Variables: { tx: any; session: Session } }>(
  async (c, next) => {
    const session = c.get("session");
    if (!session) return c.json({ error: { code: "UNAUTHENTICATED" } }, 401);

    await db().transaction(async (tx) => {
      const role = session.user.role === "admin" ? "app_admin" : "authenticated";
      await tx.execute(sql`SET LOCAL ROLE ${sql.raw(role)}`);
      await tx.execute(sql`
        SELECT set_config('app.user_id',  ${session.user.id}, true),
               set_config('app.owner_id', ${session.activeOrganizationId ?? ""}, true),
               set_config('app.role',     ${session.user.role ?? "tenant"}, true)
      `);
      c.set("tx", tx);
      await next();
    });
  }
);

Testing RLS

Every domain table gets a 4-case test:

  1. Owner A creates a row → owner A can read it.
  2. Owner A creates a row → owner B cannot read it.
  3. Owner A creates a row → admin can read it.
  4. Owner B tries to update owner A's row → no rows affected.
// packages/db/test/rls.test.ts (vitest)
import { describe, it, expect, beforeAll } from "vitest";
import { createDb, withRls, propertyOwners, contracts } from "@spacehub/db";

describe("contracts RLS", () => {
  it("owner A cannot read owner B's contracts", async () => {
    const ctxA = { userId: userA.id, ownerId: ownerA.id, role: "owner_director" };
    const ctxB = { userId: userB.id, ownerId: ownerB.id, role: "owner_director" };

    await withRls(db, ctxA, async (tx) => {
      await tx.insert(contracts).values({ ownerId: ownerA.id, /* ... */ });
    });

    const rowsAsB = await withRls(db, ctxB, (tx) => tx.select().from(contracts));
    expect(rowsAsB).toHaveLength(0);
  });
});

Mongolia-specific role mapping

v1 used User.Position with values Owner_Customer_Director, Owner_Customer_Accountant, etc. Mapping to v2 roles:

v1 Positionv2 roleNotes
Owner_Customer_Directorowner_directorFull org access
Owner_Customer_Accountantowner_accountantRead all, write financial
TenanttenantSees own contracts only — RLS policies must also check tenant_user_id
EmployeeemployeeOrg-scoped, limited write
n/aadminNew: us + support staff

For tables a tenant can see (Contract, Invoice), the policy is two-pronged:

pgPolicy("invoice_visibility", {
  as: "permissive", for: "select", to: "authenticated",
  using: sql`current_setting('app.role', true) = 'admin'
             or owner_id::text = (select current_setting('app.owner_id', true))
             or tenant_user_id::text = (select current_setting('app.user_id', true))`,
})

API surface

GET    /v2/me                              # current user + profile + active org
PATCH  /v2/me                              # update profile
GET    /v2/me/sessions                     # device list
DELETE /v2/me/sessions/{id}                # logout other device

GET    /v2/property-owners                 # admin only (or list of orgs user belongs to)
POST   /v2/property-owners                 # admin only
GET    /v2/property-owners/{id}
PATCH  /v2/property-owners/{id}

GET    /v2/property-owners/{id}/members
POST   /v2/property-owners/{id}/members    # invite (sends email + creates pending member)
DELETE /v2/property-owners/{id}/members/{userId}
PATCH  /v2/property-owners/{id}/members/{userId}   # role change

Build steps (Phase 1, week 2-3)

  1. Migrate User + add Profile, PropertyOwner schemas. Drop XAF's UserMore entity (merge into Profile).
  2. Manual SQL migration creates Postgres roles + grants.
  3. RLS middleware wired into Hono /v2/* routes (after session middleware).
  4. Backfill: one-shot script reads SQL Server User + Customer + UserMore → writes Postgres users + profiles + property_owners + members. Map old enum values to new role strings.
  5. Smoke: integration tests for each table's RLS.
  6. UI: /me, /admin/property-owners, member invite flow.

Open questions

Next: Property & rooms.