SQLite and GRPC

[pyrostew]

Hello!

I've reading through the wiki and examples for a little while now and find that this library is a nearly perfect fit for the project I am working on. With 2 missing bits.

We are using a SQLite DB for our application as it is working in an embedded environment where it is not practical to use SQLServer. I note there isn't anything built-in for supporting full SQLite DBs, but is it possible/practical to extend that from my code? Or would it need to be an addition to the library?

We are also using GRPC for our API, I'm not sure how well this library and GRPC will integrate at the moment, has anyone had a go at this?

[JonPSmith]

Hi @pyrostew,

The AuthP library only supports SQL Server and PostgreSql for production (it does support SQLite for tests, but is it uses an in-memory database). I have been asked if I would support other database types, but I have decided not to do that because this library / documentation are already very complex and takes my time to support it.

You could clone this repo and added support for SQLite is fairly simple, but there are lots of little things than can catch you out, like SQLite concurrency is different. Alternatively your can read the many articles I have written and add/copy the parts that your app needs.

Finally GRPC. AuthP library provides the backend part so if you want to use GRPC then you can.

[pyrostew]

Thanks for quick response, @JonPSmith.

Understood on not adding support directly in the library, what are the touch points for adding support myself? At the moment I've seen that adding an extension method for "UsingSQLite" is and an assembly for the migrations is necessary. I think they can be done without changing the library, or are there more touch points internally?

I suspected GRPC would work, thanks for the confirmation.

[JonPSmith]

Hi @pyrostew,

If you are really interested in doing this I will create a list of things that you would need to do. Most things you can do yourself but there may be a couple of things that I would need to the library to allow anyone to use another database type from SQLServer / PostgreSql.

I'd be happy to do add access points to the AuthP library as they will be small and doesn't need add lots of changes / tests.

Let me know if you want to take this on and I will write out all the things that you would need to do.

[pyrostew]

Hi @JonPSmith,

Yes I will be taking this on, having your support is much appreciated.

Let me know what needs doing.

Many thanks

[JonPSmith]

The key parts of the AuthP library that you can't override / alter are

• The AuthPDatabaseTypes enum which defines the type of the database that holds the AuthP's data.
• The AuthPermissionsDbContext EF Core context for the AuthP's data.

If you want to to use another database type I would make two small changes:

• Add a CustomDatabase enum to the AuthPDatabaseTypes enum. This tells the AuthP has a database type setup, otherwise the setup throws an exception.
• Add an optional method to the AuthPermissionsDbContext which will be run at the start of the OnModelCreating. This allows you to set up concurrency tokens to the entities, etc.

The things you need to do are:

1. Create a class to containing a method to be run on OnModelCreating in the AuthPermissionsDbContext for any extra setup data. I will provide a interface for this Action. You will then need to register the class as a scoped service.
2. Create a AuthPermissionsDbContext migration with your database type - see the SetupMigrations project which has comments about how it works. You will need manually register the class created in step 1 in your SetupMigrations.
3. Create a extension method that registers your database. Look at the UsingEfCoreSqlServer extension method in the AuthPermissions.SetupExtensions class. Note that you need to create a RunStartupMethodsSequentially version of the "Lock Database" method for your database, or set UseLocksToUpdateGlobalResources to false.

NOTE: The information assumes you aren't using sharding. If you need sharding there are a lot more work.

If this makes sense then I will create a 4.2.0-preview version for you to try out. I suspect I have missed something or there is an internal value you can't access, but its a start.

[pyrostew]

That sounds perfect. I have already started fiddling with the new extension method and creating a new migrations assembly.

What sort of timescale can I run with for getting the 4.2.0-preview? No pressure, just so I can plan my work.

[JonPSmith]

Hopefully tomorrow (I am on UK time).

[JonPSmith]

Hi @pyrostew,

Just released version 4.2.0-preview001 with:

• CustomDatabase enum added to the AuthPDatabaseTypes enum
• Added ICustomConfiguration interface, which allows you to register a service which will be run within the OnModelCreating to set up any extra configuration to the AuthP DbContext.

This code is within the dev branch. See the 4.2.0-preview001 version commit for the changes. The most useful part is AuthPermissionsDbContext, which shows where the custom configuration is run.

[pyrostew]

Hi @JonPSmith,

Sorry for the delay in feeding back, had some other priorities this week and didn't get as far as I wanted.

I have managed to integrate that version into my project and seems to be working well in terms of creating the DB. Though I haven't finished hooking up all of the authentication yet.

Will keep you posted.

[pyrostew]

I think I've hit a bit of a sticking point with the JWTs. As far as I can tell there doesn't seem to be any permissions claims being added to the token when it is being generated. I've been following example 2 as a broad guide. The main deviation from the example is my application is based on an IHost created from calling CreateDefaultHost.

At the moment it feels like I'm missing a bit of configuration for generating the token, or a configuration for a bit of middleware.

The relevant bit of the startup class

services.AddIdentity<IdentityUser, IdentityRole>(o => o.SignIn.RequireConfirmedAccount = false)
   .AddEntityFrameworkStores<SettingsDb>();

JwtSecurityTokenHandler.DefaultOutboundClaimTypeMap.Clear();
services.AddAuthentication(auth =>
{
   auth.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme;
   auth.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme;
   auth.DefaultScheme = JwtBearerDefaults.AuthenticationScheme;
})
   .AddJwtBearer(options =>
   {
      options.SaveToken = true;
      options.TokenValidationParameters = new TokenValidationParameters
      {
         ValidateIssuer = true,
         ValidIssuer = "MSSI Server",
         ValidateAudience = true,
         ValidAudience = "MSSI Clients",
         ValidateIssuerSigningKey = true,
         IssuerSigningKey = key,
         ClockSkew = TimeSpan.Zero
      };
   });
services.RegisterAuthPermissions<Permissions>(o =>
{
   o.UseLocksToUpdateGlobalResources = false;
   o.ConfigureAuthPJwtToken = new AuthPJwtConfiguration
   {
      Issuer = "MSSI Server",
      Audience = "MSSI Clients",
      SigningKey = Encoding.ASCII.GetString(key.Key),
      TokenExpires = TimeSpan.FromMinutes(5),
      RefreshTokenExpires = TimeSpan.FromHours(1)
   };
})
   .UsingEfCoreSqlite(@"Data Source=D:\Config\MSSI\MSSI.db")
   .IndividualAccountsAuthentication()
   .RegisterFindUserInfoService<IndividualAccountUserLookup>()
   .AddRolesPermissionsIfEmpty(RolesDefinition)
   .AddAuthUsersIfEmpty(UsersWithRolesDefinition)
   .SetupAspNetCoreAndDatabase(options =>
   {
      //Migrate individual account database
      options.RegisterServiceToRunInJob<StartupServiceMigrateAnyDbContext<AuthPermissionsDbContext>>();
   });

The token generating method

public override Task<AuthResult> Validate(AuthResponse request, ServerCallContext context)
{
   if (!m_userManager.CheckPasswordAsync(
      m_userManager.FindByNameAsync(request.Challenge.PasswordChallenge.UserId).Result, request.ResponseToken)
      .Result)
      throw new AuthenticationException("Response Token is not valid for the specified user.");

   var tokens =
      m_tokenBuilder.GenerateTokenAndRefreshTokenAsync(request.Challenge.PasswordChallenge.UserId).Result;

   return Task.FromResult(new AuthResult() { Token = tokens.Token, RefreshToken = tokens.RefreshToken });
}

[JonPSmith]

I'm not sure what the problem is, so let me explain how AuthP deals with a Web API application and the JWT Token.

The AuthP library provides a ITokenBuilder to create a JWT Token. This ITokenBuilder service contains a call to the IClaimsCalculator that uses the "userId" string (Unicode) provided by the authentication part found in the JWT to lookup a registered AuthP user get the various claims for this user to add to the JWT Token. These claims contain the AuthP permissions, the tenant DataKey (if app is a multi-tenent), and optionally extra claims via registered IClaimsAdder service. (AuthP's ITokenBuilder also has code to support the refresh token).

Now, one reason for why you don't get any permissions claims is because you haven't registered the user with AuthP. There are many ways to do this - see Three ways to securely add new users article.

In Example2 the AuthenticateController contains an Authenticate API that takes in the users' email + password and authenticates the user. If the user / password are correct, then it uses the ITokenBuilder to create a JWT Token containing the normal JWT parts and the user's claims. Further requests will contain the JWT Token that will be turned into claims for the user.

The ultimate step is to replace the AuthP's ITokenBuilder with your own JWT Token builder, but you MUST call the AuthP's IClaimsCalculator service to add the AuthP claims into your build JWT Token. That might handle your Unicode issue.

[pyrostew]

So at the moment I have the 2 users I'm testing with registered in AuthP as far as I can tell (the AuthP link tables are populated with seemingly sensible data). The token is getting generated after authenticating the user, using, I presume, a default ITokenBuilder which assumed would take the necessary steps to create a token with the relevant claims? Looking at the token in JWT.io I see the UserName and the various required bits like Audience, Issue, Expiry, etc. but nothing that seems to represent the permissions defined in the DB. Is it a requirement to do my own ITokenBuilder or should the default one do the job?

[JonPSmith]

That's most likely because AuthP couldn't find the user. One thing that that can do that is the JwtSecurityTokenHandler will alter the user's userId. This can be negated by adding JwtSecurityTokenHandler.DefaultOutboundClaimTypeMap.Clear(); to your startup - see Example2's Program

[JonPSmith]

Hi @pyrostew,

Sorry, its been a long time but I have just released version 5.0.0 of the AuthP library that contains the feature you need, called "custom databases. See the Setup the custom database feature for more information. The good thing is my example is using Sqlite, and there are fully working versions in the new repo called AuthP.CustomDatabaseExamples

I have also written the first article about this feature than might be useful too.

NOTE: The implementation for the non-sharding was pretty easy, but the sharding one took a lot of time!