Identity Server - EF to PostgreSQL

08 Feb 2022

In PostgreSQL branch, containerized PostgreSQL will be used as persistent storage, it will auto populate Operational Store and Configuration Store by Entity Framework Code First.

Prerequisites
- .NET Core SDK 3.1
- Visual Studio (2019 or 2022)
- Docker Desktop
- WSL2

Replace EF nuget packages in IdentityProvider project

<PackageReference Include="Npgsql" Version="5.0.0" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="3.1.18" />

Make sure this EntityFrameworkCore package is included

<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.18" />

In ConfigureServices.cs, replace UseSqlServer to UseNpgsql

services.AddDbContext<ApplicationDbContext>(builder =>
    builder.UseNpgsql(connectionString, sqlOptions => sqlOptions.MigrationsAssembly(migrationsAssembly))
);

// EF client, scope, and persisted grant stores
identityServerBuilder
    .AddOperationalStore(options =>
        options.ConfigureDbContext = builder =>
            builder.UseNpgsql(connectionString, sqlOptions => sqlOptions.MigrationsAssembly(migrationsAssembly)))
    .AddConfigurationStore(options =>
        options.ConfigureDbContext = builder =>
            builder.UseNpgsql(connectionString, sqlOptions => sqlOptions.MigrationsAssembly(migrationsAssembly)));

Run commands in Package Manager Console to generate initial database context code

Add-Migration InitialApplicationDbMigration -c ApplicationDbContext -o Migrations/ApplicationDb
Add-Migration InitialPersistedGrantDbMigration -c PersistedGrantDbContext -o Migrations/PersistedGrantDb
Add-Migration InitialConfigurationDbMigration -c ConfigurationDbContext -o Migrations/ConfigurationDb

Update Program.cs to load settings from appSettings.jason

public static IHostBuilder CreateHostBuilder(string[] args) =>
    Host.CreateDefaultBuilder(args)
        .ConfigureAppConfiguration((hostContext, configurationBuilder) =>
        {
            IHostEnvironment env = hostContext.HostingEnvironment;
            configurationBuilder.SetBasePath(Directory.GetCurrentDirectory());
            configurationBuilder
                .AddJsonFile("appSettings.json", optional: false, reloadOnChange: true)
                .AddJsonFile($"appSettings.{env.EnvironmentName}.json", optional: true, reloadOnChange: true);

            configurationBuilder.AddEnvironmentVariables();
        })
        .ConfigureWebHostDefaults(webBuilder =>
        {
            webBuilder.UseStartup<Startup>();   
        });

Add docker-compose to run PosgreSQL in docker container

version: '3.4'

services:
  db:
    image: postgres:latest
    restart: always
    ports:
      - 5432:5432
    environment:
      POSTGRES_PASSWORD: P@ssword!
      POSTGRES_DB: IS4Database 

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

adminer is the tool to manage PostgreSQL database Adminer Login Adminer Database View

Add connection string in appSettings

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Port=5432;Database=IS4Database;User Id=postgres;Password=P@ssword!"
  }
}

Password comes from #6 POSTGRES_PASSWORD

Get DefaultConnection in Startup.cs

public IConfiguration Configuration { get; }
public Startup(IConfiguration configuration)
{
    Configuration = configuration;
}
public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();
    // using local db (assumes Visual Studio has been installed)
    var connectionString = Configuration.GetConnectionString("DefaultConnection");
    ......
}

Run PostgreSQL
```
docker-compose up --build
```
F5 in Visual Studio, it will start 3 instances: Client, API and Identity Provider
Navigate to Privacy, it will redirect to Identity Server to login (username / password: scott / Password123!)
After login successfully, it will redirect back to Privacy page
Click on “Call Api” button to consume weather forecast API, which is restricted to authorized user

Jay Lin

Identity Server - EF to PostgreSQL