Implement Database-Level Identity and Hierarchical Authorization Integration

[silvestrinigor]

Implement Database-Level Identity and Hierarchical Authorization Integration

Summary

Implement synchronization between application users and database identities, enabling the database to participate in authorization and tenant isolation using hierarchical partition access control and Row-Level Security (RLS).

The goal is to ensure that:

• application authorization and database authorization remain synchronized
• queries are automatically filtered by partition access
• hierarchical partition inheritance is enforced at the database level
• SQL queries cannot bypass application security rules
• auditing and identity propagation become possible

This implementation should remain infrastructure-specific and preserve database portability between SQL Server and PostgreSQL.

---

Goals

Identity Synchronization

Synchronize application users with database identities.

When:

• a user is created
• a password changes
• permissions change
• a user is disabled

the corresponding database identity should also be updated.

---

Database-Level Authorization

Move partition access filtering into the database using:

• Row-Level Security (RLS)
• hierarchical partition access
• security context propagation

This should replace most application-side partition filtering logic.

---

Hierarchical Partition Access

Users may have access to multiple partitions.

Partitions may contain child partitions.

Access must automatically propagate to descendant partitions.

Example:

Company
 ├── Finance
 │    ├── Accounts
 │    └── Tax
 └── Operations
      ├── Inventory
      └── Purchasing

Access to Finance should automatically grant access to:

• Accounts
• Tax

---

Proposed Architecture

Application Layer

Application remains responsible for:

• authentication
• business rules
• semantic permissions
• workflows

Examples:

• can approve article
• can cancel operation
• can publish entity

---

Database Layer

Database becomes responsible for:

• tenant isolation
• partition hierarchy filtering
• row-level filtering
• sensitive field protection
• identity-aware query execution

---

Database Security Context

Introduce infrastructure abstraction:

public interface IDatabaseSecurityContext
{
    Task ApplyAsync(
        DbConnection connection,
        Actor actor,
        CancellationToken cancellationToken);
}

Responsibilities:

• propagate actor identity
• propagate partition context
• apply SQL security session context

Implementations:

• SqlServerDatabaseSecurityContext
• PostgresDatabaseSecurityContext

---

Database Identity Synchronization

Introduce:

public interface IDatabaseIdentityService
{
    Task ProvisionAsync(...);

    Task DeleteAsync(...);

    Task ChangePasswordAsync(...);

    Task SyncPermissionsAsync(...);
}

Responsibilities:

• create SQL identities
• assign/revoke roles
• synchronize permissions
• manage SQL logins/users

---

Partition Hierarchy Model

Introduce closure table structure.

Tables

partitions

id
parent_id
name

partition_closure

ancestor_id
descendant_id
depth

actor_partition_access

actor_id
partition_id

---

Row-Level Security

Implement RLS policies using:

• actor identity
• partition hierarchy
• closure table traversal

All queries against partitioned entities should automatically respect access boundaries.

---

SQL Server Implementation

Use:

• SESSION_CONTEXT
• SECURITY POLICY
• predicate functions

---

PostgreSQL Compatibility

Future PostgreSQL implementation should use:

• SET LOCAL
• SET ROLE
• CREATE POLICY

Architecture should remain database-agnostic outside Infrastructure.

---

User Provisioning Strategy

Initial implementation may use strong consistency with shared transaction.

Example:

BEGIN TRANSACTION
    Create application user
    Create SQL identity
    Grant roles
COMMIT

If SQL identity provisioning fails:

• rollback application user creation

Future evolution may support asynchronous provisioning.

---

Security Considerations

SQL Identity Naming

Do not use usernames directly as SQL identifiers.

Prefer:

actor_<guid>

to avoid:

• SQL injection
• escaping issues
• rename complexity
• invalid characters

---

Password Handling

Application and database should maintain separate password hashes.

Application:

• Argon2/bcrypt/PBKDF2 hash

Database:

• internal database-managed password hash

---

Expected Benefits

• automatic tenant isolation
• hierarchical authorization enforcement
• reduced application-side filtering complexity
• stronger defense against authorization bugs
• database-level auditing
• consistent identity model
• improved security guarantees
• future PostgreSQL compatibility