@indrajitsir/nest-auth-sql-adapter

TypeORM-based authorization adapter for @indrajitsir/nest-auth-core.

It resolves the authenticated user's roles (and, optionally, resource/action permissions) from your existing database tables using the TypeORM QueryBuilder — no library-owned entities, tables, or migrations required. Every table and column name is configurable through an AuthorizationSchema.

Features

• Role resolution (RBAC) — resolves user_id → role_id → role_name from two configurable tables (v0.2.0).
• Resource/action permissions — the v0.1.0 permission model stays supported through the optional permission schema section.
• Schema-driven — your organization keeps its existing column names; the library adapts to them.
• Identifier escaping — every schema-supplied identifier is escaped via DataSource.driver.escape(), so reserved words (e.g. a delete column) work on sqlite, PostgreSQL, and MySQL.
• Pluggable pieces — identity extraction, permission strategy, and the repository itself can be replaced.

Installation

npm install @indrajitsir/nest-auth-sql-adapter @indrajitsir/nest-auth-core

Peer dependencies (install them if your project does not already have them):

npm install @nestjs/common typeorm

Not published yet? If you are installing from this repository, you can use the packed tarballs instead:

npm install ./indrajitsir-nest-auth-core-0.2.0.tgz \
            ./indrajitsir-nest-auth-sql-adapter-0.2.0.tgz

Quickstart (RBAC)

Register both modules in your application module. The SQL module is @Global(), so the core module can resolve the provider and its dependencies from anywhere — core never imports the adapter package.

// app.module.ts
import { Module } from "@nestjs/common";

import { AuthorizationModule } from "@indrajitsir/nest-auth-core";
import {
  AuthorizationSqlModule,
  SqlAuthorizationProvider,
} from "@indrajitsir/nest-auth-sql-adapter";

import { dataSource } from "./data-source";

@Module({
  imports: [
    AuthorizationModule.forRoot({
      provider: SqlAuthorizationProvider,
      global: true,
    }),

    AuthorizationSqlModule.forRoot({
      dataSource,

      schema: {
        roleMapping: {
          table: "role_base_access_mapping", // user_id → role_id
          userIdColumn: "user_id",
          roleIdColumn: "role_id",
        },

        role: {
          table: "mapping_access", // role_id → role_name
          roleIdColumn: "role_id",
          roleNameColumn: "role_name",
        },
      },
    }),
  ],
})
export class AppModule {}

Then protect endpoints:

// employees.controller.ts
import { Controller, Delete, Get, Param, UseGuards } from "@nestjs/common";

import { Authorization, AuthorizationGuard } from "@indrajitsir/nest-auth-core";

@Controller("employees")
@UseGuards(AuthorizationGuard)
export class EmployeesController {
  @Authorization("ADMIN", "HR_MANAGER") // ADMIN OR HR_MANAGER
  @Get()
  findAll() {}

  @Authorization("ADMIN") // ADMIN only
  @Delete(":id")
  remove(@Param("id") id: string) {}
}

How role resolution works

Given a request from user 101, the repository executes the equivalent of:

SELECT role.role_id, role.role_name
FROM role_base_access_mapping mapping
INNER JOIN mapping_access role
    ON mapping.role_id = role.role_id
WHERE mapping.user_id = ?

The returned role names are compared with the roles declared on the endpoint:

user 101 → role_id 1 → role_name "ADMIN"
@Authorization("ADMIN", "HR_MANAGER")
ADMIN ∈ {ADMIN, HR_MANAGER}  →  ALLOW

Both queries join the same two tables through the configured roleIdColumn, so any table/column layout works as long as the chain user → role_id → role_name exists.

Schema reference

AuthorizationSchema has three sections:

| Section | Required | Purpose | | --- | --- | --- | | roleMapping | yes | Maps a user to role id(s): { table, userIdColumn, roleIdColumn } | | role | for RBAC | Maps a role id to a role name: { table, roleIdColumn, roleNameColumn } | | permission | for v0.1.0 | Maps role + resource to action columns (see below) |

schema: {
  roleMapping: {
    table: "role_base_access_mapping",
    userIdColumn: "user_id",
    roleIdColumn: "role_id",
  },

  role: {
    table: "mapping_access",
    roleIdColumn: "role_id",
    roleNameColumn: "role_name",
  },
}

The schema is validated at startup by AuthorizationSchemaValidator — a missing required column, an empty section, or a schema with neither role nor permission fails fast with a clear error.

Resource/action mode (v0.1.0)

If your tables still store per-action permission columns, add the permission section and keep using the resource/action decorator:

schema: {
  roleMapping: { /* ... */ },

  permission: {
    table: "mapping_access",
    roleIdColumn: "role_id",
    resourceColumn: "table_name", // the "resource" the row applies to

    permissions: {
      CREATE: "create_update",   // action → column that stores the flag
      UPDATE: "create_update",
      READ: "read",
      DELETE: "delete",
      ACTIVATE: "activate_deactivate",
      DEACTIVATE: "activate_deactivate",
    },
  },
}

@Authorization({ resource: "employee", action: "READ" })

The repository selects only the column mapped to the requested action and normalizes the value to a boolean PermissionRecord.

Module options

AuthorizationSqlModule.forRoot({
  dataSource,               // required: your TypeORM DataSource
  schema,                   // required: AuthorizationSchema
  permissionStrategy,       // optional: default AnyPermissionStrategy
  identityExtractor,        // optional: default IdentityExtractor
  repository,               // optional: default QueryBuilderAuthorizationRepository
})

| Option | Type | Default | Description | | --- | --- | --- | --- | | dataSource | DataSource | — | TypeORM DataSource used for authorization queries. | | schema | AuthorizationSchema | — | Table/column mapping for your existing database. | | permissionStrategy | Type<PermissionEvaluationStrategy> | AnyPermissionStrategy | How multiple permission rows are combined. | | identityExtractor | Type<IdentityExtractorInterface> | IdentityExtractor | How the user id is read from request.user. | | repository | Type<AuthorizationRepository> | QueryBuilderAuthorizationRepository | Custom query implementation. |

Identity extraction

The default IdentityExtractor reads user.id:

new IdentityExtractor().getUserId({ id: "101" }); // "101"

If your authenticated user lives in request.user with a different shape (e.g. a JWT payload { sub: "..." }), provide a custom extractor:

import { Injectable } from "@nestjs/common";
import { IdentityExtractorInterface } from "@indrajitsir/nest-auth-sql-adapter";

@Injectable()
export class JwtIdentityExtractor implements IdentityExtractorInterface {
  getUserId(user: unknown): string {
    return (user as { sub?: string }).sub ?? "";
  }

  getTenantId?(user: unknown): string | undefined {
    return (user as { tenant?: string }).tenant;
  }
}

AuthorizationSqlModule.forRoot({
  dataSource,
  schema,
  identityExtractor: JwtIdentityExtractor,
})

Permission strategies

When a user holds several roles, PermissionRecord[] is combined by the configured strategy:

| Strategy | Behavior | | --- | --- | | AnyPermissionStrategy (default) | ALLOW if any role grants the permission | | AllPermissionsStrategy | ALLOW only if every role grants it | | PriorityPermissionStrategy | Uses the role with the highest priority |

AuthorizationSqlModule.forRoot({
  dataSource,
  schema,
  permissionStrategy: PriorityPermissionStrategy,
})

Custom strategies implement PermissionEvaluationStrategy:

class DenyOverridesStrategy implements PermissionEvaluationStrategy {
  evaluate(permissions: PermissionRecord[]): boolean {
    return permissions.some((p) => p.allowed && !permissions.some((d) => !d.allowed));
  }
}

Custom repository

The repository contract is intentionally small:

export interface AuthorizationRepository {
  getPermissions(
    context: AuthorizationContext,
    policy: ResourceAuthorizationPolicy,
  ): Promise<PermissionRecord[]>;

  getRoles?(context: AuthorizationContext): Promise<RoleRecord[]>;
}

Pass your own implementation via the repository option when you need a different query strategy (raw SQL, a stored procedure, a different driver, etc.). getRoles is optional: role-based policies fail safe (DENY) when it is missing.

Public API

| Export | Kind | Purpose | | --- | --- | --- | | AuthorizationSqlModule | Module | forRoot({ dataSource, schema, ... }), registered @Global() | | SqlAuthorizationProvider | Provider | AuthorizationProvider implementation for SQL backends | | QueryBuilderAuthorizationRepository | Repository | Default TypeORM QueryBuilder implementation | | AuthorizationSchema | Type | { roleMapping, role?, permission? } | | RoleMappingSchema / RoleSchema / PermissionSchema | Types | Schema sections | | AuthorizationSchemaValidator | Service | Validates a schema at startup | | AuthorizationRepository | Contract | getPermissions() + optional getRoles() | | PermissionRecord / RoleRecord / UserRoleMapping | Types | Normalized result records | | IdentityExtractorInterface / IdentityExtractor | Service | Reads the user id from the authenticated user | | PermissionEvaluationStrategy | Contract | Combines multiple permission rows | | AnyPermissionStrategy / AllPermissionsStrategy / PriorityPermissionStrategy | Strategies | Built-in combinations | | AUTHORIZATION_REPOSITORY / AUTHORIZATION_SCHEMA / PERMISSION_STRATEGY / IDENTITY_EXTRACTOR | Tokens | DI tokens |

Security notes

• Role resolution failures deny: a missing user, a user with no roles, or a database error during lookup all result in 403 Forbidden — never in an accidental grant.
• All identifiers come from configuration and are escaped through the driver.
• Authentication is deliberately separate: the adapter only ever reads request.user through the configured IdentityExtractor. Wire up your own JWT/session guard before the AuthorizationGuard.

Development

npm run build       # compile to dist/
npm run typecheck   # strict type check
npm test            # run the monorepo test suite (vitest)

See the end-to-end examples in examples/ for complete, runnable NestJS apps with a sqlite in-memory database: v0.2.0-rbac-example (role-based) and v0.1.0-resource-action-example (resource/action).