Appendix

Validate username and password against a database

When the username and password are passed from the application to the built-in JWT server, the JWT server will by default authenticate them against users defined in the DefaultUserStore.cs file. If users are defined in an authentication database instead of DefaultUserStore.cs, you can choose to

  • Have JWT server to connect to the authentication database and authenticate the user every time when a token is requested (see this section for details); or

  • Populate users from the database to the user list of the DefaultUserStore.cs file, and the user list will be cached and used to authenticate the user when a token is requested.

    The benefit of populating and caching the user list is the JWT server does not need to connect to the authentication database every time when a user is authenticated, but the downside is if the users in the authentication database are updated, the PowerServer Web APIs needs to be restarted to refresh the user list.

This section will show you how to populate and cache the user list of the DefaultUserStore.cs file.

Step 1: Open the DatabaseUserStore.cs file and add the following scripts. Suppose a SQL Server database will be connected. Modify the database connection string according to your environment.

using System.Collections.Generic;
using System.Linq;
using System.Security.Claims;
using IdentityModel;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using SnapObjects.Data;
using SnapObjects.Data.SqlServer;

namespace ServerAPIs
{
    public class DatabaseUserStore : IUserStore
    {
        private readonly ILogger _logger;
        private readonly List<UserModel> _users;
        
        public DatabaseUserStore(ILogger<DatabaseUserStore> logger)
        {
            _logger = logger;
            _users = new List<UserModel>();
            string Constr = @"Data Source=172.16.1.10,1433;Initial Catalog=pb_cloud;Integrated Security=False;User ID=sa;Password=1234;Pooling=True;Min Pool Size=0;Max Pool Size=100;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite";
            SqlServerDataContext _context = new SqlServerDataContext(new SqlConnection(Constr));
            string sql = "select username,password from users where isValid = 1";
            var users = _context.SqlExecutor.Select<DynamicModel>(sql);
            foreach (var u in users)
            {
                _users.Add(new UserModel
                {
                    Username = u.GetValue<string>(0),
                    Password = u.GetValue<string>(1),
                    Claims = new[]
                    {
                        new Claim(JwtClaimTypes.Name, u.GetValue<string>(0)),
                        new Claim(JwtClaimTypes.Scope, "serverapi"),  //this script is added because scope is enabled by default
                    },
                });
            };
            
        }
        
        public UserModel ValidateCredentials(string username, string password)
        {
            var user = _users.FirstOrDefault(x => x.Username == username && x.Password == password);
            if (user != null)
            {
                _logger.LogInformation($"User <{username}> logged in.");
                
                return user;
            }
            else
            {
                _logger.LogError($"Invalid login attempt.");
                
                return default;
            }
        }
    }
}

To connect with a database type different from SQL Server, add the following namespace accordingly.

using SnapObjects.Data.MySql;
using SnapObjects.Data.Oracle;
using SnapObjects.Data.PostgreSql;
using SnapObjects.Data.Odbc;

Step 2: Open the AuthenticationExtensions.cs file and modify the script to inject the DatabaseUserStore class instead of the DefaultUserStore class.

//services.AddSingleton<IUserStore, DefaultUserStore>();
services.AddSingleton<IUserStore, DatabaseUserStore>();