Matheus
70c5586595
- packages/auth: Argon2id password hashing, JWT access tokens (jose), opaque refresh tokens with rotation, generic error messages (no user-enumeration via timing or message differences) - roles/permissions/role_permissions/user_roles/sessions/audit_logs schema (agente.md secoes 142-150); RBAC scope PLATFORM vs TENANT - withUserContext(): narrow RLS exception so a user can discover their own tenant_memberships before a tenant is chosen (login flow) - userHasPermission()/isPlatformUser(): explicit service-layer RBAC checks (roles/permissions tables are not RLS-protected — documented why in docs/AUTHENTICATION.md) - seed: permission catalog, 4 system roles, initial Platform Super Admin (password written once to FIRST_LOGIN.txt, 600, outside Git) - automated end-to-end test: login, RBAC check, refresh rotation, logout
140 lines
4.8 KiB
SQL
140 lines
4.8 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "role_scope" AS ENUM ('PLATFORM', 'TENANT');
|
|
|
|
-- AlterTable
|
|
ALTER TABLE "users" ADD COLUMN "must_change_password" BOOLEAN NOT NULL DEFAULT false;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "roles" (
|
|
"id" UUID NOT NULL,
|
|
"key" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"scope" "role_scope" NOT NULL,
|
|
"is_system" BOOLEAN NOT NULL DEFAULT false,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "roles_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "permissions" (
|
|
"id" UUID NOT NULL,
|
|
"key" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
|
|
CONSTRAINT "permissions_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "role_permissions" (
|
|
"role_id" UUID NOT NULL,
|
|
"permission_id" UUID NOT NULL,
|
|
|
|
CONSTRAINT "role_permissions_pkey" PRIMARY KEY ("role_id","permission_id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "user_roles" (
|
|
"id" UUID NOT NULL,
|
|
"user_id" UUID NOT NULL,
|
|
"role_id" UUID NOT NULL,
|
|
"tenant_id" UUID,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "user_roles_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "sessions" (
|
|
"id" UUID NOT NULL,
|
|
"user_id" UUID NOT NULL,
|
|
"refresh_token_hash" TEXT NOT NULL,
|
|
"active_tenant_id" UUID,
|
|
"user_agent" TEXT,
|
|
"ip_address" TEXT,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"expires_at" TIMESTAMP(3) NOT NULL,
|
|
"revoked_at" TIMESTAMP(3),
|
|
|
|
CONSTRAINT "sessions_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "audit_logs" (
|
|
"id" UUID NOT NULL,
|
|
"tenant_id" UUID,
|
|
"user_id" UUID,
|
|
"action" TEXT NOT NULL,
|
|
"entity_type" TEXT,
|
|
"entity_id" TEXT,
|
|
"before" JSONB,
|
|
"after" JSONB,
|
|
"ip_address" TEXT,
|
|
"user_agent" TEXT,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "audit_logs_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "roles_key_key" ON "roles"("key");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "permissions_key_key" ON "permissions"("key");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "user_roles_user_id_role_id_tenant_id_key" ON "user_roles"("user_id", "role_id", "tenant_id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "sessions_refresh_token_hash_key" ON "sessions"("refresh_token_hash");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "sessions_user_id_idx" ON "sessions"("user_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "audit_logs_tenant_id_created_at_idx" ON "audit_logs"("tenant_id", "created_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "audit_logs_user_id_idx" ON "audit_logs"("user_id");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "role_permissions" ADD CONSTRAINT "role_permissions_role_id_fkey" FOREIGN KEY ("role_id") REFERENCES "roles"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "role_permissions" ADD CONSTRAINT "role_permissions_permission_id_fkey" FOREIGN KEY ("permission_id") REFERENCES "permissions"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_role_id_fkey" FOREIGN KEY ("role_id") REFERENCES "roles"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- The login flow needs to discover which tenants a user belongs to BEFORE a
|
|
-- tenant has been chosen (chicken-and-egg: RLS normally requires
|
|
-- app.current_tenant_id to already be set). Extend the tenant_isolation
|
|
-- policy so a session can also see its OWN memberships via
|
|
-- app.current_user_id, without ever exposing other users' memberships or
|
|
-- other tenants' data. See docs/AUTHENTICATION.md.
|
|
DROP POLICY "tenant_isolation" ON "tenant_memberships";
|
|
|
|
CREATE POLICY "tenant_isolation" ON "tenant_memberships"
|
|
USING (
|
|
tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
|
|
OR user_id = NULLIF(current_setting('app.current_user_id', true), '')::uuid
|
|
);
|
|
|
|
-- roles / permissions / role_permissions / user_roles / sessions / audit_logs
|
|
-- are intentionally NOT protected by RLS. Unlike tenant business data
|
|
-- (extensions, agents, campaigns, calls, ...), these are auth-internal
|
|
-- tables touched only by the trusted packages/auth service layer, which
|
|
-- applies its own explicit WHERE clauses and RBAC checks (defense-in-depth
|
|
-- per agente.md secao 32: RBAC + object authorization + tenant repositories
|
|
-- + RLS are complementary layers, not all mandatory for every table).
|