ASP.NET Core 7: Using PostgreSQL to store Identity Information

In this article, we will see using the PostgreSQL database to store the ASP.NET Identity information in it. If the ASP.NET Core application is using PostgreSQL database instead of SQL Server database then we can use it to store application user and role information.

This article is a simple implementation of identity in ASP.NET Core using PostgreSQL. To create a database and tables for storing ASP.NET Identity information, we use classes like IdentityUser, IdentityRole, etc. The EntityFrameworkCore Code-First approach is used to create a database and tables. 

Step 1: Open Visual Studio 2022 and create a new ASP.NET Core API project. Name this project as Core_API_Postgre_Identity. To use Identity classes and PostgreSQL add the following packages to the project

1. Microsoft.AspNetCore.Identity
1. This package is needed to use IdentityUser and IdentityRole classes
2. Microsoft.AspNetCore.Identity.EntityFrameworkCore
1. This package is needed to use IdentityDbContext class. This class is used as a base class to implement entity-to-table mapping for the Code-First approach 
3. Microsoft.EntityFrameworkCore.Design
1. This package is needed to generate EntityFrameworkCore migrations
4. Microsoft.EntityFrameworkCore.Tools
5. Npgsql.EntityFrameworkCore.PostgreSQL
1. This package is used to connect to the PostgreSQL database 

Step 2: In the project add the new folder named Models. In this folder, add class files named as SecurityModels.cs. In this class file, we will add classes that will be used to access new user registration and user login information. The code in Listing 1 shows these classes

namespace Core_API_Postgre_Identity.Models
{
    public class RegisterUser
    {
        public string? Email { get; set; }
        public string? Password { get; set; }
    }

    public class LoginUser
    {
        public string? Email { get; set; }
        public string? Password { get; set; }
    }
}

Listing 1: The Security Models

   

Step 3: In the Models folder, add a new class file and name it as AspNetCoreIdentotyDbContext.cs. In this folder, we will add AspNetCoreIdentityDbContext class derived from IdentotyDbContext class. This class will be used to manage table mapping with the database where we will be creating tables for storing AspNet user information. The code in Listing 2 will show an implementation of the AspNetCoreIdentityDbContext class.     

using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
 
using Microsoft.EntityFrameworkCore;

namespace Core_API_Postgre_Identity.Models
{
    public class AspNetCoreIdentityDbContext : IdentityDbContext
    {
        public AspNetCoreIdentityDbContext(DbContextOptions<AspNetCoreIdentityDbContext> options):base(options)
        {
            
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
        }
    }
}

Listing 2: The AspNetCoreIdentityDbContext class

Step 4: In the appsettings.json, let's add the connection string for the PostgreSQL database. We will use this connection string to connect to the database and generate tables. Listing 3 shows the connection string

"ConnectionStrings": {
    "SecurityDb": "User ID=postgres;Password=P@ssw0rd_;Host=localhost;Port=5433;Database=AspNetCoreIdentity;Pooling=true;"
  }

Listing 3: The PostgreSQL Connection String

Step 5: In this project add a new folder and name it as Services. In this folder, add a new class file and name it AuthenticationService.cs. In this class file, we will add the AuthenticationService class. We will use this class to create a new user and sign in with that user. We will inject UserManager and SignInManager classes using constructor injection. The UserManager class will be sued to create a new user and SignInManager class will be used to sign in to the application. Listing 4, will show the code for the class

using Core_API_Postgre_Identity.Models;
using Microsoft.AspNetCore.Identity;

namespace Core_API_Postgre_Identity.Services
{
    public class AuthenticationService
    {
        UserManager<IdentityUser> _userManager;
        SignInManager<IdentityUser> _signInManager;

        public AuthenticationService(UserManager<IdentityUser> userManager,
        SignInManager<IdentityUser> signInManager)
        {
            _userManager= userManager;
            _signInManager= signInManager;
        }

        public async Task<bool> RegisterNewUser(RegisterUser user)
        {
            var identityUser = new IdentityUser()
            {
                 Email = user.Email,
                 UserName = user.Email
            };
            var result = await _userManager.CreateAsync(identityUser, user.Password);
            if (result.Succeeded) 
            {
                return true;
            }
            return false;
        }

        public async Task<bool> Authenticate(LoginUser user)
        {
          
            var result = await _signInManager.PasswordSignInAsync(user.Email, user.Password, false, true);
            
            if (result.Succeeded) 
            {
                return true;
            }
            return false;
        }

    }
} 

Listing 4: The AuthenticationService class code

The AuthenticationService class contains RegisterNewUser() method. This method accepts the RegisterUser instance as an input parameter. This method uses the CreateAsync() method of the UserManager class to create the new user. The CreateAsync() method accepts the IdentityUser class instance as an input parameter. We will store data from RegisterUser class in IdentityUser class so that we can pass the IdentityUser class instance to CreateAsunc() method to create a new user. The IdentityUser class is used to map with the AspNetUsers table that is used to store users. The Authenticate() method accepts the LoginUser instance. This class contains Email and Password properties using which the user will be signing in. This method uses the PasswordSignInAsync()  method of the SignInManager class to manage the user sign-in.       

Step 6: Modify the Program.cs to register the DbContext class added in Step 3 and AuthentcationService class added in Step 5 in the dependency container. Listing 5 shows the necessary code

......

builder.Services.AddDbContext<AspNetCoreIdentityDbContext>(options => 
{
    options.UseNpgsql(builder.Configuration.GetConnectionString("SecurityDb"));
});

// Add services to the container.
builder.Services.AddIdentity<IdentityUser, IdentityRole>().AddEntityFrameworkStores<AspNetCoreIdentityDbContext>();
builder.Services.AddScoped<AuthenticationService>();

......

Listing 5: The Program.cs

Step 7: In the Controllers folder, add a new empty API controller, name this as AuthController. In this controller, we will inject the AuthenticationService using constructor injection. This controller will contain Register() and AuthUser()  HttpPost methods. These methods will be using AuthenticationService class to register a new user and manage the user authentication user. Listing 6 shows the controller code

using Core_API_Postgre_Identity.Models;
using Core_API_Postgre_Identity.Services;
using Microsoft.AspNetCore.Mvc;

namespace Core_API_Postgre_Identity.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthController : ControllerBase
    {
        private readonly AuthenticationService authService;
        public AuthController(AuthenticationService serv)
        {
            authService = serv;
        }

        [HttpPost]
        [ActionName("register")]
        public async Task<IActionResult> Register(RegisterUser user)
        {
            try
            {
                var response = await authService.RegisterNewUser(user);
                if (response)
                    return Ok($"User {user.Email} is register successfully");
                return BadRequest(response);
            }
            catch (Exception ex)
            {
                return BadRequest(ex.Message); 
            }
        }

        [HttpPost]
        [ActionName("authenticate")]
        public async Task<IActionResult> AuthUser(LoginUser user)
        {
            try
            {
                var response = await authService.Authenticate(user);
                if (response)
                    return Ok($"User {user.Email} is authenticated successfully");
                return BadRequest(response);
            }
            catch (Exception ex)
            {
                return BadRequest(ex.Message);
            }
        }
    }
}

Listing 6: The AuthController code

Step 8: In this Controllers folder, add a new empty API Controller and name it as InfoController. In this controller, we will add a simple HttpGet method that returns a string. On this controller will apply the Authorize attribute so that the user must sign in to access this controller. Listing 7 shows the code of the InfoController

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;

namespace Core_API_Postgre_Identity.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    [Authorize]
    public class InfoController : ControllerBase
    {
        [HttpGet]
        public IActionResult Get()
        { 
            return Ok("I am Authorize");
        }
    }
}

Listing 7: The InfoController code

The InfoController is applied with Authorize attribute. This means that the user must sign in to access the controller.

Step 9: To create a database, we need to generate EntityFrameworkCore Code-First migrations. Open the command prompt and navigate to the project folder and run the command as shown in Listing 8 to generate migration

dotnet ef migrations add firstMigration -c Core_API_Postgre_Identity.Models.AspNetCoreIdentityDbContext

Listing 8: Command to generate Migration     

This command will add a Migrations folder in the project with the migration class. To create a database and tables in PostgreSQL run the following command as shown in Listing 9  

dotnet ef database update -c Core_API_Postgre_Identity..Models.AspNetCoreIdentityDbContext

Listing 9: Command to run the migration to generate database and tables

Figure 1 shows the AspNetCoreIdentity database and Asp.Net Core identity table generated in the PostgreSQL database   

Figure 1: The database and tables generated

Let's run the application, and the swagger page will be displayed as shown in Figure 2

Figure 2: The Swagger Page

Click on the Get Button on /api/Info, without authenticating the user. This will return the Error response. To create a new user, let's make the POST request with /api/Auth/register using the  data as shown in Figure 3

Figure 3: The User Creation

The user1@myapp.com user is created with the password Password_. We can see the user created in PostgreSQL as shown in Figure 4

Figure 4: The user created in the database

So we have created AspNet Core users in PostgreSQL.  Let's authenticate using the user we just created as shown in Figure 5

Figure 5: The User Authentication       

Since we have logged in successfully, let's make a call to InfoController, we will receive a successful response from it as shown in Figure 6

 Figure 6: The InfoController class 

 

The code for this article is on this link.