PostgreSQL Row Level Security with .NET EF Core

Row Level Security (RLS) is PostgreSQL's built-in feature for controlling which rows users can access in database tables. Instead of granting access to entire tables, RLS applies automatic filters at the row level, creating invisible WHERE clauses that protect your data without touching application code.

Why RLS Matters

Defense Against Code Bugs: Application-level filters are fragile. One forgotten WHERE tenant_id = @id and you've leaked data across tenants. RLS enforces protection at the database level, catching what your code misses.

Cleaner Code: Without RLS, every query needs explicit tenant filtering scattered across your codebase. RLS centralizes this logic in the database, eliminating repetitive security code.

Multi-Tenant Security: For SaaS applications sharing databases, RLS delivers single-schema simplicity with isolation guarantees approaching physical separation. One connection pool, one database, strict tenant boundaries.

Implementation Guide

1. Configure PostgreSQL

Enable RLS and create policies for your table:

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL,
    name TEXT NOT NULL,
    description TEXT
);

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Filter reads, writes, updates, and deletes by tenant
CREATE POLICY tenant_isolation ON projects
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant')::UUID)
    WITH CHECK (tenant_id = current_setting('app.current_tenant')::UUID);

2. Extract Tenant Context

Pull the tenant ID from authenticated user claims:

public interface ITenantProvider
{
    string? GetCurrentTenantId();
}

public class JwtTenantProvider : ITenantProvider
{
    private readonly IHttpContextAccessor _httpContextAccessor;

    public JwtTenantProvider(IHttpContextAccessor httpContextAccessor)
    {
        _httpContextAccessor = httpContextAccessor;
    }

    public string? GetCurrentTenantId()
    {
        return _httpContextAccessor.HttpContext?.User
            .FindFirst("tenant_id")?.Value;
    }
}

3. Create EF Core Interceptor

Set the session variable when connections open:

public class RlsConnectionInterceptor : DbConnectionInterceptor
{
    private readonly ITenantProvider _tenantProvider;

    public RlsConnectionInterceptor(ITenantProvider tenantProvider)
    {
        _tenantProvider = tenantProvider;
    }

    public override async Task ConnectionOpenedAsync(
        DbConnection connection,
        ConnectionEndEventData eventData,
        CancellationToken cancellationToken = default)
    {
        var tenantId = _tenantProvider.GetCurrentTenantId();

        if (!string.IsNullOrEmpty(tenantId))
        {
            using var command = connection.CreateCommand();
            command.CommandText = $"SET LOCAL app.current_tenant = '{tenantId}';";
            await command.ExecuteNonQueryAsync(cancellationToken);
        }

        await base.ConnectionOpenedAsync(connection, eventData, cancellationToken);
    }
}

4. Wire Up Dependency Injection

builder.Services.AddHttpContextAccessor();
builder.Services.AddScoped<ITenantProvider, JwtTenantProvider>();
builder.Services.AddScoped<RlsConnectionInterceptor>();

builder.Services.AddDbContext<ApplicationDbContext>((serviceProvider, options) =>
{
    var interceptor = serviceProvider.GetRequiredService<RlsConnectionInterceptor>();

    options.UseNpgsql(connectionString)
           .AddInterceptors(interceptor);
});

5. Write Clean Controllers

Your controllers need zero tenant filtering logic:

[ApiController]
[Route("api/[controller]")]
[Authorize]
public class ProjectsController : ControllerBase
{
    private readonly ApplicationDbContext _context;
    private readonly ITenantProvider _tenantProvider;

    public ProjectsController(
        ApplicationDbContext context,
        ITenantProvider tenantProvider)
    {
        _context = context;
        _tenantProvider = tenantProvider;
    }

    [HttpGet]
    public async Task<ActionResult<List<Project>>> GetProjects()
    {
        // RLS filters automatically - no WHERE clause needed
        return Ok(await _context.Projects.ToListAsync());
    }

    [HttpPost]
    public async Task<ActionResult<Project>> CreateProject(CreateProjectRequest request)
    {
        var tenantId = _tenantProvider.GetCurrentTenantId();
        if (string.IsNullOrEmpty(tenantId))
            return Unauthorized();

        var project = new Project
        {
            TenantId = Guid.Parse(tenantId),
            Name = request.Name,
            Description = request.Description
        };

        _context.Projects.Add(project);
        await _context.SaveChangesAsync();

        return CreatedAtAction(nameof(GetProject), new { id = project.Id }, project);
    }
}

Critical Gotchas

Use SET LOCAL, Not SET: SET LOCAL limits variables to transaction scope, preventing leakage across pooled connections. Using plain SET is dangerous with connection pooling.

Index Your Tenant Columns: RLS policies execute on every query. Without proper indexes on tenant identifier columns, performance will suffer.

Beware Superusers: PostgreSQL superusers bypass RLS by default. Use dedicated application roles with limited privileges.

Test Thoroughly: Verify policies work as expected by attempting cross-tenant access with different user contexts.

The Bottom Line

RLS moves security from application code into the database where it belongs. Your queries stay simple, your data stays isolated, and bugs in your filtering logic won't expose sensitive information. For multi-tenant applications, it's the difference between hoping your code is perfect and knowing your database enforces boundaries automatically.

PostgreSQL Row Level Security with .NET and EF Core

Comments

More from this blog

Fluent Pipeline DSL in .NET – Turning Complex Workflows into Readable, Type-Safe Code

Seamless Navigation in Angular: Pre-fetching Data with Route Resolvers and NgRx SignalStore

Mastering Domain Events Made Simple with MediatR in C# and .NET 8

Enhancing Database Operations with EF Core 7 Interceptors

Why RLS Matters

Implementation Guide

1. Configure PostgreSQL

2. Extract Tenant Context

3. Create EF Core Interceptor

4. Wire Up Dependency Injection

5. Write Clean Controllers

Critical Gotchas

The Bottom Line

Command Palette

Comments

More from this blog

Why RLS Matters

Implementation Guide

1. Configure PostgreSQL

2. Extract Tenant Context

3. Create EF Core Interceptor

4. Wire Up Dependency Injection

5. Write Clean Controllers

Critical Gotchas

The Bottom Line