03. IdentityServer4 EF With PostgreSQL .NET Core 3.1

You can find the project here.

Remove existing MSSQL migrations

If you followed my previous tutorial we were adding migrations for operational and configuration stores in MSSQL database. We will now say bye-bye to the MSSQL database and switch to PostgreSQL which is a really good open-source and free relational database engine. We will leave most of the code changes but need to remove the migrations as we will need to re-run the commands to create new migrations for PostgreSQL.

I strongly suggest following the tutorials in the order they were laid out as this is the third one and will much more sense after the first and second one. However, if you feel like to want to jump right in you can do so by grabbing the code from the previous tutorial here. Let’s start by deleting the “Data” folder and all its content from the project root folder which will effectively remove the migrations from the project.

PostgreSQL code changes

In order to connect to PostgreSQL, we need to add the PostgreSQL NuGet packages for Entity Framework. Open the NuGet Package Manager and search for “Npgsql.EntityFrameworkCore.PostgreSQL”. I used version 3.1.0. Install the package and it should automatically include all dependencies.

Now lets open the “Startup.cs” as we need to change some code in ConfigureServices method. We need to update database context options as “UseSqlServer” extension method is for MSSQL server. We need to replace it with the extension method from the installed PostgreSQL NuGet package.

        .AddConfigurationStore(options =>
	{
		options.ConfigureDbContext = b => b.UseNpgsql(connectionString, sql => sql.MigrationsAssembly(migrationsAssembly));
	})
	.AddOperationalStore(options =>
	{
		options.ConfigureDbContext = b => b.UseNpgsql(connectionString, sql => sql.MigrationsAssembly(migrationsAssembly));
		options.EnableTokenCleanup = true;
	});

Last code change we need to do is the connection string which is in “appsettings.json” file in the root folder of the project. Open it and modify the “DefaultConnection” value to your PostgreSQL configuration. I have a local PostgreSQL server instance on port default port “5432” with database name “IdentityServerQuickstart” so it would look something like this

{
	"ConnectionStrings": {
		"DefaultConnection": "Server=localhost;Port=5432;Uid=postgres;Pwd=spitfire123;Database=IdentityServerQuickstart"
	}
}

Migrations for PostgreSQL

Open the Package Manager Console in Visual Studio. You can easily do that by typing “package manager console” in Visual Studio search box at the top. Execute these two commands to create migrations for Operation store and Configuration store

Add-Migration InitialPersistedGrantDbMigration -c PersistedGrantDbContext -o Data/Migrations/IdentityServer/PersistedGrantDb
Add-Migration InitialConfigurationDbMigration -c ConfigurationDbContext -o Data/Migrations/IdentityServer/ConfigurationDb

Now we again have folders with structure “Data\Migrations\IdentityServer” holding the migrations for the Identity Server stores for the PostgreSQL database. Remember, we still didn’t migrate to the user store. We will do that in the future tutorial and the migration for user store will also go into the “Data\Migrations” folder but not under the “IdentityServer” folder.

Let’s update the database with the newly created migrations. Execute the commands in the Package Manager Console

Update-Database -Context PersistedGrantDbContext
Update-Database -Context ConfigurationDbContext

Congratulations! You just successfully migrated the IdentityServer4 stores to a PostgreSQL database.

Seed data

In the previous tutorial, I explained why we need seed data. It is not important at the moment and we will do it in one of the future tutorials. OpenID Connect protocol requires one default Identity Resource called “sub” or subject which is a unique identifier for the user. We will also add other commonly used resources.

Wrap up

In conclusion, it was really easy to switch from using the MSSQL database to an open-source and free solution aka PostgreSQL. Hold your pants as the next tutorial is all about migrating the users to the ASP.NET Identity user store. We are getting there!

You can find the project here.

Support

For direct assistance schedule a technical meeting with Ivan to talk about your requirements.
For a general overview of our services and a live demo schedule a meeting with Maja.