Beyond Flat RBAC: How Transitive ReBAC and PostgreSQL Recursive CTEs Solve AI Multi-Tenancy

ReBAC vs RBAC in enterprise AI is not merely a choice between authorization acronyms. It is a question of whether access policy can reflect the organizational structure where AI agents, tools, files, and knowledge actually live. A global business may need a France sales team to access a regional assistant while preventing that same group from retrieving German HR content. A handful of static roles rarely expresses these boundaries cleanly as departments and teams change.

This article explains how to model authorization around relationships, what a PostgreSQL recursive Common Table Expression (CTE) contributes, and which security decisions remain the responsibility of the application. The goal is not to claim that a recursive query solves governance by itself; it is to show how a coherent data model can make policy easier to express, review, and test.

Why flat role lists strain at enterprise scale

Role-Based Access Control (RBAC) assigns roles such as Admin, Editor, or Viewer and maps those roles to permitted actions. It works well for many applications. The pressure begins when one role needs different scope depending on a person’s team, region, and resource ownership. Adding combinations such as FranceSalesEditor and GermanySalesEditor may solve an immediate need, but the number of role variants can grow with every organizational boundary and action.

AI systems increase the stakes because the protected objects are not only pages and database records. An authorization decision may govern whether a person can use an agent, search a knowledge base, execute a connector, or modify a workspace file. A broad grant to an agent can indirectly expose information through retrieval or tool execution unless the resource and its data paths are scoped consistently.

RBAC remains useful for describing what actions a role permits. ReBAC adds a way to reason about who is related to which organizational or resource context. Enterprise systems commonly combine these ideas rather than discard roles altogether.

Model the organization and the protected resources

A practical hierarchy can be represented as a tree: organization, region, department, and team. A role assignment binds a user and a role to a node. A resource record identifies a governed object—such as an AI agent, knowledge base, file, or tool—and resource-scope records associate that object with one or more nodes. Role definitions then describe allowed actions for resource types.

Organization
└── Europe
    ├── France
    │   └── Sales
    └── Germany
        └── Human Resources

For example, a sales role assigned at the Europe node could inherit selected actions for descendants, while a France-scoped knowledge resource remains unavailable to a user whose relationship does not cover France. A global resource may have an explicit organization-wide policy. The meaning of “global,” empty scope, multiple scopes, and ancestor access must be defined deliberately; ambiguity in these rules is a security defect, not a minor implementation detail.

Resolve inherited scope with a recursive CTE

PostgreSQL recursive CTEs let a query express a base set and then repeatedly follow parent-child relationships. At a high level, an authorization query can start from the nodes where a user has an applicable role assignment, traverse permitted descendants, and test whether the requested resource’s scope intersects the resulting set. Keeping resolution close to the relational data can avoid application-side loops that issue one query per hierarchy level.

WITH RECURSIVE reachable_nodes AS (
  SELECT assignment.node_id
  FROM role_assignments AS assignment
  WHERE assignment.user_id = :user_id
    AND assignment.is_active = TRUE

  UNION ALL

  SELECT child.id
  FROM org_nodes AS child
  JOIN reachable_nodes AS parent
    ON child.parent_id = parent.node_id
)
SELECT EXISTS (
  SELECT 1
  FROM resources AS resource
  JOIN resource_scopes AS scope
    ON scope.resource_id = resource.id
  WHERE resource.id = :resource_id
    AND scope.org_node_id IN (SELECT node_id FROM reachable_nodes)
) AS scope_matches;

This is an illustrative query pattern, not a drop-in authorization implementation. A production query must also verify tenant membership, role permissions for the requested action and resource type, active assignment state, resource ownership, and the site’s precise semantics for unscoped resources. The query should be parameterized, reviewed against the actual schema, and protected against cycles or malformed hierarchy data.

Keep authorization decisions explicit and testable

A safe authorization path typically follows a clear sequence: authenticate the caller; identify the tenant and requested action; load the resource’s type and scope; resolve applicable role permissions and hierarchy relationships; evaluate a deny-by-default policy; then record the decision with enough context for audit without logging unnecessary sensitive content. Authentication proves identity; it does not grant access to every resource in the tenant.

Koios positions Transitive ReBAC as part of its enterprise governance model. Its messaging describes hierarchical organizational nodes and PostgreSQL recursive CTEs for evaluating inherited permissions, with AI agents, knowledge bases, workspace files, and connectors treated as scoped resources. Actual deployments should validate behavior against their concrete schema and configuration; do not infer latency guarantees or security properties from the use of CTEs alone.

• Tenant isolation: Include tenant boundaries in joins and keys; never rely on a node identifier alone if it is not globally unique.
• Action checks: A role that allows reading a knowledge base should not implicitly allow publishing an agent or executing every connector.
• Default deny: Missing assignments, malformed scope, and query errors should not silently become allow decisions.
• Lifecycle changes: Deprovisioning and role changes need defined effective timing and auditable outcomes.
• Retrieval boundaries: Ensure RAG filtering and tool execution enforce the same authorization context as the user-facing interface.

Performance: measure the query, not the slogan

A recursive CTE can consolidate traversal into a database query, but “one query” does not automatically mean “fast.” Performance depends on hierarchy depth and shape, row counts, indexes, selectivity, concurrency, and the query plan. Use realistic tenant sizes and explain plans. Index parent identifiers, tenant identifiers, assignment lookup columns, and resource-scope foreign keys as appropriate to the schema. Verify that filters restrict traversal to the intended tenant.

Operational metrics should include authorization latency percentiles, query volume, denied and allowed decisions by action class, and database load. Sampled, privacy-conscious audit records should make it possible to answer: who requested which action on which resource, under what tenant and policy context, and what decision resulted? If permission results are cached, define invalidation behavior for role, hierarchy, and resource-scope changes; otherwise a correct database model can still be undermined by stale grants.

Validation scenarios for an AI governance rollout

Before deploying a hierarchy-backed policy, test both expected access and expected denial. A useful test matrix includes a manager with an inherited role, a user assigned directly to a team, a sibling department, a different tenant with a coincidentally similar node name, a removed employee, a resource with multiple scopes, and a resource intended to be global. Test actions separately: read, search, consume, execute, write, and publish are not interchangeable.

Also test structural edge cases: moving a team to another parent, disabling an assignment, attempting a cycle, and handling an unavailable authorization store. Confirm that the system fails closed where required and that logs can explain the decision without exposing prompts, credentials, or the protected document itself. These tests help ensure that inheritance remains an intentional policy rather than an accidental consequence of a join.

ReBAC and RBAC are complementary building blocks

Flat RBAC is often a good starting point for simple products. In a complex enterprise, roles alone may become overloaded with organizational scope. Relationship-based modeling separates action capability from structural context: roles can say what a user may do, while relationships and resource scopes help determine where those actions apply. PostgreSQL recursive CTEs provide one way to compute hierarchical reachability over relational data.

For enterprise AI, that distinction matters because the same agent can connect people to data, tools, and actions across departments. Map the organizational hierarchy, scope every governed resource, make deny-by-default checks explicit, and test boundary cases across tenants and action types. Koios presents this governance approach as part of an Enterprise AI Harness: policy enforcement should travel with the execution path, not stop at the login screen.

Related reading: Enterprise AI security gateway and zero-trust controls and enterprise SSO for AI platforms. For database semantics, see the PostgreSQL documentation on WITH queries.