Providing Multitenancy with ASP.NET Core and PostgreSQL Row Level Security

I have written quite a lot of articles on Multitenancy with Spring Boot, but I am actually writing C# for a living. It's interesting to see how to provide Multitenancy with ASP.NET Core and Entity Framework Core.

So in this article we are building a simple CRUD application to create, read and update customers. PostgreSQL Row Level Security is used to provide data isolation for multiple Tenants of the application:

I've found most of the example implementations for Multitenancy with ASP.NET Core to be overly complicated.

So here is my overly complicated implementation!

Creating the Database

Let's start with the database.

It's always a good idea to create a new database schema and not pollute an existing schema:

CREATE SCHEMA IF NOT EXISTS sample;

In the database we'll create a tenant table, which is going to hold the tenants:

CREATE TABLE IF NOT EXISTS sample.tenant
(
    tenant_id INTEGER PRIMARY KEY
    , name VARCHAR(255) NOT NULL
    , description VARCHAR(255) NOT NULL
    , UNIQUE(name)
);

And we are adding two example Tenants, that will be using the sample application:

INSERT INTO sample.tenant(id, name, description)
VALUES (1, '33F3857A-D8D7-449E-B71F-B5B960A6D89A', 'Tenant 1')
ON CONFLICT  DO NOTHING;

INSERT INTO sample.tenant(id, name, description)
VALUES (2, '7344384A-A2F4-4FC4-A382-315FCB421A72', 'Tenant 2')
ON CONFLICT  DO NOTHING;

The customers will be managed in a customer table, which also contains a tenant_name column. We will apply a Row Security Policy on this column to restrict, which rows can be returned by normal queries or inserted, updated, or deleted by data modification commands.

CREATE TABLE IF NOT EXISTS sample.customer
(
    customer_id SERIAL PRIMARY KEY
    , first_name VARCHAR(255) NOT NULL
    , last_name VARCHAR(255) NOT NULL
    , tenant_name VARCHAR(255) NOT NULL DEFAULT current_setting('app.current_tenant')::VARCHAR
);

I want to keep it simple, so the tenant_name is simply using the value of the variable current_setting('app.current_tenant'), which is passed from ASP.NET Core down to PostgreSQL per session. My reasoning is, that this keeps the applications domain model clean from any tenant identifiers.

Also we don't want to connect to the database as the database owner, so add a new user app_user:

---------------------------
-- USERS                 --
---------------------------
IF NOT EXISTS (
  SELECT FROM pg_catalog.pg_roles
  WHERE  rolname = 'app_user') THEN

  CREATE ROLE app_user LOGIN PASSWORD 'app_user';

END IF;

And finally set the Row Level Security Policies on your tables:

---------------------------
-- RLS                   --
---------------------------
ALTER TABLE sample.customer ENABLE ROW LEVEL SECURITY;

---------------------------
-- RLS POLICIES         --
---------------------------

DROP POLICY IF EXISTS tenant_customer_isolation_policy ON sample.customer;

CREATE POLICY tenant_customer_isolation_policy ON sample.customer
    USING (tenant_name = current_setting('app.current_tenant')::VARCHAR);

--------------------------------
-- GRANTS                     --
--------------------------------
GRANT USAGE ON SCHEMA sample TO app_user;

-------------------------------------
-- GRANT TABLE                     --
-------------------------------------
GRANT SELECT ON TABLE sample.tenant TO app_user;

GRANT ALL ON SEQUENCE sample.customer_customer_id_seq TO app_user;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE sample.customer TO app_user;

... and we are done!

The ASP.NET Core application

The Tenant Database

We start the implementation by defining the Tenant domain model:

// Licensed under the MIT license. See LICENSE file in the project root for full license information.

namespace AspNetCoreMultitenancy.Multitenancy
{
    /// <summary>
    /// A Tenant.
    /// </summary>
    public class Tenant
    {
        /// <summary>
        /// Id.
        /// </summary>
        public int Id { get; set; }

        /// <summary>
        /// Name.
        /// </summary>
        public string? Name { get; set; }

        /// <summary>
        /// Description.
        /// </summary>
        public string? Description { get; set; }
    }
}

By implementing an IEntityTypeConfiguration<Tenant> interface we configure the database mapping:

// Licensed under the MIT license. See LICENSE file in the project root for full license information.

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace AspNetCoreMultitenancy.Multitenancy
{
    public class TenantEntityTypeConfiguration : IEntityTypeConfiguration<Tenant>
    {
        public void Configure(EntityTypeBuilder<Tenant> builder)
        {
            builder
                .ToTable("tenant", "sample")
                .HasKey(x => x.Id);

            builder
                .Property(x => x.Id)
                .HasColumnName("tenant_id");

            builder
                .Property(x => x.Name)
                .HasColumnName("name")
                .IsRequired();

            builder
                .Property(x => x.Description)
                .HasColumnName("description")
                .IsRequired();
        }
    }
}

It's useful to also provide a TenantDbContext to access the Tenant Database:

// Licensed under the MIT license. See LICENSE file in the project root for full license information.

using Microsoft.EntityFrameworkCore;

namespace AspNetCoreMultitenancy.Multitenancy
{
    /// <summary>
    /// A DbContext to access the Tenant Database.
    /// </summary>
    public class TenantDbContext : DbContext
    {
        public TenantDbContext(DbContextOptions<TenantDbContext> options)
            : base(options)
        {
        }

        /// <summary>
        /// Tenants.
        /// </summary>
        public DbSet<Tenant> Tenants { get; set; } = null!;

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.ApplyConfiguration(new TenantEntityTypeConfiguration());
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
        }
    }
}

That's it for managing the Tenants.

Where to keep the current Tenant?

And now comes the most important question of this article:

  • How do we pass the current Tenant through the application?

See I really don't want to fiddle around with Dependency Injection like passing an IHttpContextAccessor through the application, just to get the Tenant name. The idea is to just let the Tenant flow with the async execution by using a global AsyncLocal<Tenant>. I am calling the class TenantExecutionContext:

// Licensed under the MIT license. See LICENSE file in the project root for full license information.

using System;
using System.Threading;

namespace AspNetCoreMultitenancy.Multitenancy
{
    /// <summary>
    /// Holds the Tenant in an Ambient Context, which simplifies the code.
    /// </summary>
    public static class TenantExecutionContext
    {
        /// <summary>
        /// Holds the Tenant in an <see cref="AsyncLocal{T}"/>, so it flows top-down.
        /// </summary>
        private static AsyncLocal<Tenant> tenant = new AsyncLocal<Tenant>();

        /// <summary>
        /// Gets the current Tenant 
        /// </summary>
        public static Tenant? Tenant => tenant.Value;

        public static void SetTenant(Tenant value)
        {
            if(value == null)
            {
                throw new InvalidOperationException($"Tried set an empty Tenant");
            }

            var currentTenant = tenant.Value;

            if(currentTenant == null || string.Equals(currentTenant.Name, value.Name, StringComparison.InvariantCulture))
            {
                tenant.Value = value;

                return;
            }

            throw new InvalidOperationException($"Tried assign the Tenant to '{value.Name}', but it is already set to {currentTenant.Name}");
       }
    }
}

The Tenant Name is going to be passed to the ASP.NET Core application using a Header with the Name X-TenantName. Using a custom Middleware we can extract the Tenant Name, look it up from the Tenant database and write it into the TenantExecutionContext:

// Licensed under the MIT license. See LICENSE file in the project root for full license information.

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Http;
using Microsoft.EntityFrameworkCore;
using System.Threading.Tasks;

namespace AspNetCoreMultitenancy.Multitenancy
{
    /// <summary>
    /// Used to write the Tenant Header into the <see cref="TenantExecutionContext"/> to 
    /// flow with the async. This uses the <see cref="TenantDbContext"/> to set the Tenant, 
    /// you might find more efficient ways.
    /// </summary>
    public class MultiTenantMiddleware
    {
        /// <summary>
        /// The Header "X-TenantName" the Request contains.
        /// </summary>
        private static readonly string TenantHeaderName = "X-TenantName";

        private readonly RequestDelegate _next;

        public MultiTenantMiddleware(RequestDelegate next)
        {
            _next = next;
        }

        public async Task InvokeAsync(HttpContext context, TenantDbContext tenantDbContext)
        {
            // Try to get the Tenant Name from the Header:
            if (context.Request.Headers.ContainsKey(TenantHeaderName))
            {
                string tenantName = context.Request.Headers[TenantHeaderName];

                // It's probably OK for the Tenant Name to be empty, which may or may not be valid for your scenario.
                if (!string.IsNullOrWhiteSpace(tenantName))
                {
                    var tenantNameString = tenantName.ToString();

                    var tenant = await tenantDbContext.Tenants
                        .AsNoTracking()
                        .FirstOrDefaultAsync(x => x.Name == tenantNameString, context.RequestAborted);

                    if (tenant == null)
                    {
                        context.Response.StatusCode = 400;
                        await context.Response.WriteAsync("Invalid Tenant Name", context.RequestAborted);

                        return;
                    }

                    // We know the Tenant, so set it in the TenantExecutionContext:
                    TenantExecutionContext.SetTenant(tenant);
                }
            }

            await _next(context);
        }
    }

    public static class TenantMiddlewareExtensions
    {
        public static IApplicationBuilder UseMultiTenant(this IApplicationBuilder builder)
        {
            return builder.UseMiddleware<MultiTenantMiddleware>();
        }
    }
}

Remember how Postgres used a Session variable to resolve the current Tenant Name? By implementing a DbConnectionInterceptor we can set the app.current_tenant session variable, when the Database connection is opened:

// Licensed under the MIT license. See LICENSE file in the project root for full license information.

using Microsoft.EntityFrameworkCore.Diagnostics;
using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;

namespace AspNetCoreMultitenancy.Multitenancy
{
    public class PostgresTenantDbConnectionInterceptor : DbConnectionInterceptor
    {
        public override void ConnectionOpened(DbConnection connection, ConnectionEndEventData eventData)
        {
            if(TenantExecutionContext.Tenant != null)
            {
                using (var cmd = connection.CreateCommand())
                {
                    PrepareCommand(cmd, TenantExecutionContext.Tenant);

                    cmd.ExecuteNonQuery();
                }
            }
        }

        public override async Task ConnectionOpenedAsync(DbConnection connection, ConnectionEndEventData eventData, CancellationToken cancellationToken = default)
        {
            if (TenantExecutionContext.Tenant != null)
            {
                using (var cmd = connection.CreateCommand())
                {
                    PrepareCommand(cmd, TenantExecutionContext.Tenant);

                    await cmd.ExecuteNonQueryAsync(cancellationToken);
                }
            }
        }

        private void PrepareCommand(DbCommand cmd, Tenant tenant)
        {
            cmd.CommandText = $"SET app.current_tenant = '{tenant.Name}'";
        }
    }
}

Implementing the Multi-Tenant Application

We start by defining the Domain Model Customer, which will be managed by the ASP.NET Core application:

// Licensed under the MIT license. See LICENSE file in the project root for full license information.

namespace AspNetCoreMultitenancy.Models
{
    /// <summary>
    /// Customer.
    /// </summary>
    public class Customer
    {
        /// <summary>
        /// Id.
        /// </summary>
        public int Id { get; set; }

        /// <summary>
        /// First Name.
        /// </summary>
        public string? FirstName { get; set; }

        /// <summary>
        /// Last Name.
        /// </summary>
        public string? LastName { get; set; }
    }
}

... and implement an IEntityTypeConfiguration<Customer> to configure the database mapping:

// Licensed under the MIT license. See LICENSE file in the project root for full license information.

using AspNetCoreMultitenancy.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace AspNetCoreMultitenancy.Database.Mappings
{
    public class CustomerEntityTypeConfiguration : IEntityTypeConfiguration<Customer>
    {
        public void Configure(EntityTypeBuilder<Customer> builder)
        {
            builder
                .ToTable("customer", "sample")
                .HasKey(x => x.Id);

            builder
                .Property(x => x.Id)
                .HasColumnName("customer_id");

            builder
                .Property(x => x.FirstName)
                .HasColumnName("first_name");

            builder
                .Property(x => x.LastName)
                .HasColumnName("last_name");
        }
    }
}

... and we will provide the database access using the ApplicationDbContext:

// Licensed under the MIT license. See LICENSE file in the project root for full license information.

using AspNetCoreMultitenancy.Database.Mappings;
using AspNetCoreMultitenancy.Models;
using Microsoft.EntityFrameworkCore;

namespace AspNetCoreMultitenancy.Database
{
    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) 
            : base(options)
        {
        }

        /// <summary>
        /// Customers.
        /// </summary>
        public DbSet<Customer> Customers { get; set; } = null!;

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.ApplyConfiguration(new CustomerEntityTypeConfiguration());
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
        }
    }
}

Providing the Web service CRUD Endpoints

It's always a good idea to separate your Domain Model and your Web service model. In this example it's a little overkill, but let's be a good citizen and define a DTO for the Web service endpoints:

// Licensed under the MIT license. See LICENSE file in the project root for full license information.

using System.Text.Json.Serialization;

namespace AspNetCoreMultitenancy.Dto
{
    /// <summary>
    /// A Customer.
    /// </summary>
    public class CustomerDto
    {
        /// <summary>
        /// Id.
        /// </summary>
        [JsonPropertyName("id")]
        public int Id { get; set; }

        /// <summary>
        /// First Name.
        /// </summary>
        [JsonPropertyName("firstName")]
        public string? FirstName { get; set; }

        /// <summary>
        /// Last Name.
        /// </summary>
        [JsonPropertyName("lastName")]
        public string? LastName { get; set; }
    }
}

In the CustomerConverter we are converting between the Model and DTOs:

// Licensed under the MIT license. See LICENSE file in the project root for full license information.

using AspNetCoreMultitenancy.Dto;
using AspNetCoreMultitenancy.Models;

namespace AspNetCoreMultitenancy.Converters
{
    public static class CustomerConverter
    {
        public static Customer ToModel(CustomerDto source)
        {
            return new Customer
            {
                Id = source.Id,
                FirstName = source.FirstName,
                LastName = source.LastName,
            };
        }

        public static CustomerDto ToDto(Customer source)
        {
            return new CustomerDto
            {
                Id = source.Id,
                FirstName = source.FirstName,
                LastName = source.LastName,
            };
        }
    }
}

In the CustomerController we are now providing the RESTful CRUD Endpoints:

// Licensed under the MIT license. See LICENSE file in the project root for full license information.

using AspNetCoreMultitenancy.Converters;
using AspNetCoreMultitenancy.Database;
using AspNetCoreMultitenancy.Dto;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace AspNetCoreMultitenancy.Controllers
{
    [Route("api/customer")]
    [ApiController]
    public class CustomerController : ControllerBase
    {
        private readonly ApplicationDbContext applicationDbContext;

        public CustomerController(ApplicationDbContext applicationDbContext)
        {
            this.applicationDbContext = applicationDbContext;
        }

        [HttpGet]
        public async Task<IActionResult> Get(CancellationToken cancellationToken)
        {
            var customers = await applicationDbContext.Customers
                .AsNoTracking()
                .ToListAsync(cancellationToken);

            var customerDtos = customers?
                .Select(customer => CustomerConverter.ToDto(customer))
                .ToList();

            return Ok(customerDtos);
        }

        [HttpGet("{id}")]
        public async Task<IActionResult> Get(int id, CancellationToken cancellationToken)
        {
            var customer = await applicationDbContext.Customers.FindAsync(id);

            if(customer == null)
            {
                return NotFound();
            }

            var customerDto = CustomerConverter.ToDto(customer);

            return Ok(customerDto);
        }

        [HttpPost]
        public async Task<IActionResult> Post([FromBody] CustomerDto customerDto, CancellationToken cancellationToken)
        {
            if(!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            var customer = CustomerConverter.ToModel(customerDto);

            await applicationDbContext.AddAsync(customer, cancellationToken);
            await applicationDbContext.SaveChangesAsync(cancellationToken);

            return CreatedAtAction(nameof(Get), new { id = customer.Id }, CustomerConverter.ToDto(customer));
        }

        [HttpPut("{id}")]
        public async Task<IActionResult> Put(int id, [FromBody] CustomerDto customerDto, CancellationToken cancellationToken)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            var customer = CustomerConverter.ToModel(customerDto);

            applicationDbContext.Customers.Update(customer);
            await applicationDbContext.SaveChangesAsync(cancellationToken);

            return NoContent();
        }

        [HttpDelete("{id}")]
        public async Task<IActionResult> Delete(int id, CancellationToken cancellationToken)
        {
            var customer = await applicationDbContext.Customers.FindAsync(id);

            if(customer == null)
            {
                return NotFound();
            }

            applicationDbContext.Customers.Remove(customer);

            await applicationDbContext.SaveChangesAsync(cancellationToken);

            return NoContent();
        }
    }
}

Enabling Multitenancy on Startup

Did you see anything related to Multitenancy yet?

No, because the magic happens in the Startup for the application.

By using the UseMultiTenant() extension you are enabling the Multitenant Middleware and by adding the PostgresTenantDbConnectionInterceptor you are setting the app.current_tenant for each PostgreSQL session:

// Licensed under the MIT license. See LICENSE file in the project root for full license information.

using AspNetCoreMultitenancy.Database;
using AspNetCoreMultitenancy.Multitenancy;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;

namespace AspNetCoreMultitenancy
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            // Register Scoped DbContexts:
            services
                // Register the Tenant Database:
                .AddDbContext<TenantDbContext>(options => options.UseNpgsql("Host=localhost;Port=5432;Database=sampledb;Pooling=false;User Id=app_user;Password=app_user;"))
                // Register the Application Database:
                .AddDbContext<ApplicationDbContext>(options => options
                    .AddInterceptors(new PostgresTenantDbConnectionInterceptor())
                    .UseNpgsql("Host=localhost;Port=5432;Database=sampledb;Pooling=false;User Id=app_user;Password=app_user;"));

            services.AddControllers();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            else
            {
                app.UseExceptionHandler("/Error");
            }

            app.UseStaticFiles();

            app.UseMultiTenant();

            app.UseRouting();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }
    }
}

And that's it.

Does it work?

We start with inserting customers to the database of Tenant Tenant 1 (33F3857A-D8D7-449E-B71F-B5B960A6D89A):

> curl -H "X-TenantName: 33F3857A-D8D7-449E-B71F-B5B960A6D89A" -H "Content-Type: application/json" -X POST -d "{\"firstName\" : \"Philipp\", \"lastName\" : \"Wagner\"}"  http://localhost:5000/api/customer

{"id":1,"firstName":"Philipp","lastName":"Wagner"}

> curl -H "X-TenantName: 33F3857A-D8D7-449E-B71F-B5B960A6D89A" -H "Content-Type: application/json" -X POST -d "{\"firstName\" : \"Max\", \"lastName\" : \"Mustermann\"}"  http://localhost:5000/api/customer

{"id":2,"firstName":"Max","lastName":"Mustermann"}

Getting a list of all customers for Tenant 1 will now return two customers:

> curl -H "X-TenantName: 33F3857A-D8D7-449E-B71F-B5B960A6D89A" -H "Content-Type: application/json" -X GET http://localhost:5000/api/customer

[{"id":1,"firstName":"Philipp","lastName":"Wagner"},{"id":2,"firstName":"Max","lastName":"Mustermann"}]

While requesting a list of all customers for Tenant 2 (7344384A-A2F4-4FC4-A382-315FCB421A72) returns an empty list:

> curl -H "X-TenantName: 7344384A-A2F4-4FC4-A382-315FCB421A72" -H "Content-Type: application/json" -X GET http://localhost:5000/api/customer

[]

We can now insert a customer for Tenant 2:

> curl -H "X-TenantName: 7344384A-A2F4-4FC4-A382-315FCB421A72" -H "Content-Type: application/json" -X POST -d "{\"firstName\" : \"Hans\", \"lastName\" : \"Wurst\"}"  http://localhost:5000/api/customer

{"id":3,"firstName":"Hans","lastName":"Wurst"}

Querying the database with Tenant 1 still returns the two customers:

> curl -H "X-TenantName: 33F3857A-D8D7-449E-B71F-B5B960A6D89A" -H "Content-Type: application/json" -X GET http://localhost:5000/api/customer

[{"id":1,"firstName":"Philipp","lastName":"Wagner"},{"id":2,"firstName":"Max","lastName":"Mustermann"}]

Querying with Tenant 2 will now return the inserted customer:

> curl -H "X-TenantName: 7344384A-A2F4-4FC4-A382-315FCB421A72" -H "Content-Type: application/json" -X GET http://localhost:5000/api/customer

[{"id":3,"firstName":"Hans","lastName":"Wurst"}]

Works!

How to contribute

One of the easiest ways to contribute is to participate in discussions. You can also contribute by submitting pull requests.

General feedback and discussions?

Do you have questions or feedback on this article? Please create an issue on the GitHub issue tracker.

Something is wrong or missing?

There may be something wrong or missing in this article. If you want to help fixing it, then please make a Pull Request to this file on GitHub.