Rest API with DATA/DB isolation and using JWT token bearer authentication

[cland]

Hi, This is more a question to anyone who help me get my simple test to work. I have a Rest API that uses JWT token bearer authentication and works fine. I am now trying to adjust to handle multi-tenancy where by each tenant has their own database in Postgresql (can be MySql). Below is my current setup: Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    services.AddAutoMapper(typeof(Startup));

    services.TryAddSingleton<IHttpContextAccessor, HttpContextAccessor>();

    var signinKey = Configuration.GetSection("Config")["signinkey"];

    services.AddMultiTenant().
        WithConfigurationStore().
        WithRouteStrategy();

    // Register the db context, but do not specify a provider/connection string since
    // these vary by tenant.
    services.AddDbContext<ApplicationDbContext>();

    services.AddIdentity<IdentityUser, IdentityRole>()
        .AddEntityFrameworkStores<ApplicationDbContext>()
        .AddDefaultTokenProviders();

    services.AddAuthentication(options =>
    {
        options.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme;
        options.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme;
        options.DefaultScheme = JwtBearerDefaults.AuthenticationScheme;
    }
    )
    .AddJwtBearer(options =>
    {
        options.SaveToken = true;
        options.RequireHttpsMetadata = false;
        options.TokenValidationParameters = new TokenValidationParameters
        {
            ValidateIssuer = true,
            ValidateAudience = true,
            ValidateIssuerSigningKey = true,
            ValidAudience = "http://mylab.sytes.net",
            ValidIssuer = "http://mylab.sytes.net",
            IssuerSigningKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(signinKey))
        };
    });

    services.Configure<ForwardedHeadersOptions>(options =>
    {
        options.ForwardedHeaders =
        Microsoft.AspNetCore.HttpOverrides.ForwardedHeaders.XForwardedFor | Microsoft.AspNetCore.HttpOverrides.ForwardedHeaders.XForwardedProto;
    });
}// End ConfigureServices method

// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
    app.UseForwardedHeaders(); // from old config, remove ???
    if (env.EnvironmentName == "Development")
    {
        app.UseDeveloperExceptionPage();
    }

    app.UseStaticFiles();
    app.UseRouting();
    app.UseMultiTenant();

//Just initialize some data/users for each of the tenant databases.
    var ti = new TenantInfo("finbuckle", "finbuckle-id1", "finbuckle-name", "Server=localhost;Port=5432;Uid=postgres;Pwd=Mabasa10;Database=Tenant1", null );
    SeedDatabase.Initialize(app.ApplicationServices.GetRequiredService<IServiceScopeFactory>().CreateScope().ServiceProvider,ti);
    ti = new TenantInfo("megacorp", "megacorp-id2", "megacorp-name", "Server=localhost;Port=5432;Uid=postgres;Pwd=Mabasa10;Database=Tenant2", null);
    SeedDatabase.Initialize(app.ApplicationServices.GetRequiredService<IServiceScopeFactory>().CreateScope().ServiceProvider, ti);
    ti = new TenantInfo("initech", "initech-id3", "Initech LLC", "Server=localhost;Port=5432;Uid=postgres;Pwd=Mabasa10;Database=Tenant3", null);
    SeedDatabase.Initialize(app.ApplicationServices.GetRequiredService<IServiceScopeFactory>().CreateScope().ServiceProvider, ti);

    app.UseAuthentication();
    app.UseHttpsRedirection();
} //end Configure method

Then my dbcontext looks like so:

public class ApplicationDbContext : MultiTenantIdentityDbContext
{
    public ApplicationDbContext(TenantInfo tenantInfo) : base(tenantInfo)
    {
    }

    public ApplicationDbContext(TenantInfo tenantInfo, DbContextOptions<ApplicationDbContext> options) 
        : base(tenantInfo, options)
    {
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {

        //optionsBuilder.UseSqlite(ConnectionString);            
        optionsBuilder.UseNpgsql(ConnectionString);
        base.OnConfiguring(optionsBuilder);
    }
    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
        #region "Seed Data"
        builder.Entity<IdentityRole>().HasData(
            new { Id = "1", Name = SystemRoles.Role_Admin, NormalizedName=SystemRoles.Role_Admin.ToUpper()},                
            new { Id = "2", Name = SystemRoles.Role_User, NormalizedName = SystemRoles.Role_User.ToUpper() }
        );
        #endregion
    }
} //end class

When I try to run my application it gives me this error:

Any ideas or pointer to a better implementation of this. I am using .net core 3.1 Perhaps @hbermani and/or @natelaff can help because i see one thread where they are talking of something else but looks like they are doing what i am looking for or partly?

[natelaff]

Its actually funny because I have started thinking this week about how to handle seeding as well in the db per tenant model. EF Core data seeding, especially for default roles is useless so this is the approach you have to take. The issue you're running into i believe, is exactly what I was anticipating would happen with this approach, where this is being initialized before you have an actual tenant (in my API project I get tenant name from a claim in token), so I don't think you're able to accomplish what you're looking to do here, but maybe (hopefully) someone can prove me wrong.

[AndrewTriesToCode]

@cland Thanks for raising this issue. I haven't actually tried using the EFCore seeding functionality yet... so you are forging new ground here.

Just as a quick reality check can you add

new { ..., TenantId = "initech" };

to the seed lines just to confirm that works?

By default the MultiTenantIdentityDbContext will register IdentityRole as multitenant, but in your use case will different tenants need to have unique sets of roles? If not there is a way to specify exactly which Identity entities should be multitenant but it is a little more complicated.

@natelaff Thanks for the input!

[natelaff]

@achandlerwhite well, they're not using the built in EF Core seeding via HasData (that's the useless part), so this is the necessary approach for doing something like default roles and claims. But I'm quite sure at the point this is being called there is no tenant context.

[AndrewTriesToCode]

@natelaff I agree with you the challenge of seeding a database per tenant setup is a lot trickier and I would argue close to "provisioning" and all the complexity that implies.

I'm working on adding support for IHostedService so all the multitenant stuff can workwith AspNetCore proper. I could see a background service perhaps being a good way to provision/on-board new tenants.

[AndrewTriesToCode]

@natelaff I think he is using HasData in his Configure method, but it doesn't fire off until his Initialize function causing the issue. Your points are 100% valid though.

[natelaff]

@achandlerwhite yes, you're exactly right. I have an entire provisioning API that deals with initializing the tenant database, and I believe that's where I will have to add default roles/claims because I didn't anticipate this method of actually working, and suspect this is what @cland will have to do as well.

EDIT: Ah yes, I missed the user/role creation. That works fine. Adding the default user is where this tanks. I personally don't like having to seed an ID column for each role and role claim. The roles are fine, the claims get messy real fast.

[cland]

Thank you all for your quick response. I must say you guys I way advanced than me and need a bit of time to really digest what you are saying :)

But just to add a few points. The app will have standard roles regardless of the tenant. And I am trying to achieve this type of multi-tenancy (sort of):

Apologies if this is how you already understood it to be.

I will digest again your responses so far and also try what @achandlerwhite suggested.

[natelaff]

Yes, that is what I do as well. I haven't added roles yet, this was coming, but for my default user, this is handled in the provisioning process.

So, I have a provisioning API that creates the database and schema (from a SQL bacpac, of course this could be done any number of ways), and handles other things like Azure Storage containers, DNS, adds to my 'catalog' db, etc...

When the database isassigned during this process I have a stored proc that I quickly add to the db then execute, which among a handful of other initialization tasks is what adds my default user. This is likely where I'm going to also create default roles/claims.

[AndrewTriesToCode]

@cland

I started to write out how to make just the role non-multitenant, but it occurs to me that if you truly have a separate database per tenant then you might be better off just using the regular IdentityDbContext. The multitenant one really is about separating data when tenants share a single database, but at the cost of added complexity.

Of course the tenant resolution and connection string per tenant will still be important so I think this library can still be helpful for you :)

[natelaff]

Yeah, I wouldn't attempt that either. Using IdentityDbContext is the best way to do this. The second you have a requirement for instant roles you'll be thankful you did. In db per tenant, there is no reason to use the MultitenantDbContext.

In full disclosure for myself, I never implemented this library, but still intend to. I have a custom multi tenancy library that basically does exactly this that I was looking to replace so I didn't have to maintain my own. I think once I got to Identity Server is where things went weird and I had to fall back to how I had it. But there were a few things I think would be interesting to add as PR that I'd like to help with (custom Tenant type for instance), and a handful of other things. If that was done, I'd be easily able to swap mine out and could help maintain this one instead :)

[AndrewTriesToCode]

@cland I've got custom tenant type coming soon. It really adds a lot of power. I expect to open the PR this weekend if you want to take a look

[Hammurabidoug]

Would something like this work work you?

    public class IsolatedDbMultiTenantIdentityDbContext : IsolatedDbMultiTenantIdentityDbContext<IdentityUser, IdentityRole, string>
    {
        protected IsolatedDbMultiTenantIdentityDbContext(TenantInfo tenantInfo) : base(tenantInfo)
        {
        }

        protected IsolatedDbMultiTenantIdentityDbContext(TenantInfo tenantInfo, DbContextOptions options) : base(tenantInfo, options)
        {
        }

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

            builder.Entity<IdentityUser>();
        }
    }

    /// <summary>
    /// An Identity database context that enforces tenant integrity on entity types
    /// marked with the MultiTenant annotation or attribute.
    /// <remarks>
    /// TUser and TRole are not multitenant by default.
    /// All other Identity entity types are multitenant by default.
    /// </remarks>
    /// </summary>
    public abstract class IsolatedDbMultiTenantIdentityDbContext<TUser, TRole, TKey> : MultiTenantIdentityDbContext<TUser, TRole, TKey, IdentityUserClaim<TKey>, IdentityUserRole<TKey>, IdentityUserLogin<TKey>, IdentityRoleClaim<TKey>, IdentityUserToken<TKey>>
        where TUser : IdentityUser<TKey>
        where TRole : IdentityRole<TKey>
        where TKey : IEquatable<TKey>
    {
        protected IsolatedDbMultiTenantIdentityDbContext(TenantInfo tenantInfo) : base(tenantInfo)
        {
        }

        protected IsolatedDbMultiTenantIdentityDbContext(TenantInfo tenantInfo, DbContextOptions options) : base(tenantInfo, options)
        {
        }

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

            builder.Entity<IdentityUserClaim<TKey>>();
            builder.Entity<IdentityUserRole<TKey>>();
            builder.Entity<IdentityUserLogin<TKey>>();
            builder.Entity<IdentityRoleClaim<TKey>>();
            builder.Entity<IdentityUserToken<TKey>>();
        }
    }

    /// <summary>
    /// An Identity database context that enforces tenant integrity on entity types
    /// marked with the MultiTenant annotation or attribute.
    /// <remarks>
    /// No Identity entity types are multitenant by default.
    /// </remarks>
    /// </summary>
    public abstract class IsolatedDbMultiTenantIdentityDbContext<TUser, TRole, TKey, TUserClaim, TUserRole, TUserLogin, TRoleClaim, TUserToken> : IdentityDbContext<TUser, TRole, TKey, TUserClaim, TUserRole, TUserLogin, TRoleClaim, TUserToken>, IMultiTenantDbContext
        where TUser : IdentityUser<TKey>
        where TRole : IdentityRole<TKey>
        where TUserClaim : IdentityUserClaim<TKey>
        where TUserRole : IdentityUserRole<TKey>
        where TUserLogin : IdentityUserLogin<TKey>
        where TRoleClaim : IdentityRoleClaim<TKey>
        where TUserToken : IdentityUserToken<TKey>
        where TKey : IEquatable<TKey>
    {
        public TenantInfo TenantInfo { get; }

        public TenantMismatchMode TenantMismatchMode { get; set; } = TenantMismatchMode.Throw;

        public TenantNotSetMode TenantNotSetMode { get; set; } = TenantNotSetMode.Throw;

        protected string ConnectionString => TenantInfo.ConnectionString;

        protected IsolatedDbMultiTenantIdentityDbContext(TenantInfo tenantInfo)
        {
            this.TenantInfo = tenantInfo;
        }

        protected IsolatedDbMultiTenantIdentityDbContext(TenantInfo tenantInfo, DbContextOptions options) : base(options)
        {
            this.TenantInfo = tenantInfo;
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
            builder.ConfigureMultiTenant();
        }
    }

[cland]

Thanks @Hammurabidoug just saw your comment and will digest it.

Just wanted to respond to the outcome after doing what @achandlerwhite suggested. I am getting a different error now after adding the TenantId as suggested.

Still going thru all the responses though.

[Hammurabidoug]

How about this?

    public class ToDoDbContext : IdentityDbContext<IdentityUser>, IMultiTenantDbContext
    {
        public ToDoDbContext(TenantInfo tenantInfo)
        {
            this.TenantInfo = tenantInfo;
        }

        public ToDoDbContext(TenantInfo tenantInfo, DbContextOptions<ToDoDbContext> options) : base(options)
        {
            this.TenantInfo = tenantInfo;
        }

        protected string ConnectionString => TenantInfo.ConnectionString;

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

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<ToDoItem>();

            base.OnModelCreating(modelBuilder);
        }

        public TenantInfo TenantInfo { get; }

        public TenantMismatchMode TenantMismatchMode { get; set; } = TenantMismatchMode.Throw;

        public TenantNotSetMode TenantNotSetMode { get; set; } = TenantNotSetMode.Throw;
    }

[AndrewTriesToCode]

@cland I can help with that error -- do you have a repo on github I can clone to take a look at?

[natelaff]

Please share findings. If there is a way to seed data when connecting to each tenant DB that would be awesome...

[cland]

@cland I can help with that error -- do you have a repo on github I can clone to take a look at?

I have dumped the code here: https://github.com/cland/multitenant

Hope you will find all in order.

[cland]

Got something working (well sort of). Created a new branch here: https://github.com/cland/multitenant/tree/CLAND_Updates Have simple controller and returning simple json data back from the table.

SUCCESSFUL 1) Creation of the databases for each tenant and seeding the database with roles and admin user. 2) Returning the roles (just as test) from a test controller "TasksController". (Weird; it works in browser but not in postman (Help?) ) 3) The tenant routing seems to be working fine.

TODO and CHALLENGES and UNKNOWNS: 1) Can't access my api from postman. Just hangs. 2) Test the authentication 3) Not sure how to then ensure that the right context is injected into the controller for all subsequent calls (not tried it.) 4) Also not sure if what I have done is acceptable coding or correct without any issues later.

POSTMAN PROBLEM

FINE IN BROWSER

UPDATE

1. postman: disabled the SSL certificate validation option in the settings section and it started working fine.
2. Authentication and authorization working perfectly as I wanted it to.
3. See by branch link for how I am currently doing it. Not sure if there is a much better and reusable way.
4. Would appreciate and pointers if the way I have currently done is not good or if there is a better way to do it.

MY RESULTS Login then call a simple gets that only allows logged users with role "RoleUser". Login:_

Get Call (TaskController) If I access this endpoint before login should get a 401 unauthorized.

CONCLUSION

Thanks everyone for the help. Special thanks to @achandlerwhite for this package, it has answered something I struggled with for quite sometime. I am open to new ways of implementing this, I know @achandlerwhite you probably working on something. For now I'm happy with the current solution.

[natelaff]

Correct me if I'm wrong @cland, but your startup code relies on statically pushed TenantInfo...?

[cland]

Correct me if I'm wrong @cland, but your startup code relies on statically pushed TenantInfo...?

No. It's working just like @achandlerwhite dataisolation sample with the appSettings.json containing the tenant info. So If I add another tenant in appsettings.json it should just work as well.

The initialize or seeding the db for the first time is happening in the startup but I could read my tenant information from the appSettings.json, (or I am sure it could possibly come from a databases. yes?). Once db is setup, just tested by removed that initialize/seeding code, it continues to work perfectly picking up the correct tenant and it's associated db based on the routing like /initech/api/....

Hope I understood you correctly. Check out the full code here: https://github.com/cland/multitenant/tree/CLAND_Updates