Building Composable RLS: Enterprise Data Security on Autopilot

Enterprise applications require rigorous data integrity and security, but manually implementing these cross-cutting concerns is error-prone. This post, the (almost) final in our series on building an automated Data Access Layer (DAL), focuses on implementing the most challenging requirement: Row-Level Security (RLS).

We build upon our existing system of composable global query filters, extending it to enforce entity-based RLS automatically. This means a developer writes a simple query, and the DAL guarantees that only records accessible to the current user are returned, even for complex joins.

The RLS Contract: IProtected

To enable this, we update our IDbCtx to carry the authenticated user's Ulid? UserId. A key security choice is to adopt a "fail-closed" stance: if UserId is null, the query will be filtered to return no rows.

We define the security behavior with the IProtected interface.

[EntityFilter<IProtected>(nameof(Filter))]
public interface IProtected
{
    // The entity must expose the ID used for the permission check.
    Ulid GetPermissionObjectId();
    // Filter method dynamically applies an INNER JOIN to permissions table.
}

The filter method attached via [EntityFilter] performs two critical steps:

1. Unauthenticated Check: If no UserId is set in the context, it returns q.Where(_ => false). Access denied.
2. Permission Join: If a user is present, it executes an InnerJoin between the entity query and the IPermissionEntity table. This join is conditional on perm.ObjectId matching the entity's object ID, and perm.SubjectId matching the current user's ID.

Projected Permissions for Scalability

What if permissions are hierarchical? For instance, a user is granted access to a Post, but needs to query its child Comments. Our architecture handles this by allowing the Comment entity to "project" its security check to its parent's ID.

public partial class Comment : IProtected
{
    // Tells the RLS filter to use the Post ID for the security check.
    [ExpressionMethod(nameof(GetPermissionObjectIdExpression))]
    public Ulid GetPermissionObjectId() => Post.Id;

    private static Expression<Func<Comment, Ulid>> GetPermissionObjectIdExpression()
        => x => x.Post.Id;
}

The DAL automatically converts this method call into an expression tree that joins the Comment table to the Post table, then joins the Post to the Permission table. All the complexity of multi-table security is handled by the DAL framework, not the application code.

The Result: Guaranteed Compliance

When a developer executes a simple ModifyAsync() on a protected, tenanted, and soft-deletable Comment entity, the DAL composes all four concerns into a single, efficient, and guaranteed-correct SQL statement. This eliminates common security and data integrity vulnerabilities from the application layer.

For the full source code, the detailed filter logic, and the final generated SQL, check out the complete article on our blog.