fbpx

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.

Comments
  • Alexander says:

    Hi from Russia!
    Your manual the best. Thanks!

  • Dangelo says:

    Trying to use identityserver with postgreSQL.

    When using Update-Database -Context PersistedGrantDbContext in the console, there is an “System.ArgumentException: Keyword not supported: ‘port’.” error.

    I think it refer to the Port=5432 of the connection string.

    Anyway, the tables are created in Postgres, but if I run the program, and go to http://localhost:5000/grants
    then it breaks.

    “An unhandled exception occurred while processing the request.
    ArgumentException: Keyword not supported: ‘port’.
    Microsoft.Data.Common.DbConnectionOptions.ParseInternal(Dictionary parsetable, string connectionString, bool buildChain, Dictionary synonyms, bool firstKey)”

    Is this case familiar to any of you?

    Love this guide. It is very precise.

    • deblokt says:

      Please make sure you are using the “UseNpgsql” extension in Startup instead of the default one for MS SQL.

      • Dangelo says:

        Sorry. It is my mistake!
        I had to change the “b.UseNpgsql” in two sentences instead of just one.
        I will just copy and paste the scrypt the next time!

        Thank you Deblokt.

  • jengus says:

    how to execute “Add-Migration InitialPersistedGrantDbMigration -c PersistedGrantDbContext -o Data/Migrations/IdentityServer/PersistedGrantDb” and
    “Add-Migration InitialConfigurationDbMigration -c ConfigurationDbContext -o Data/Migrations/IdentityServer/ConfigurationDb within Visual Studio Code? thank you

  • Gopal Chettri says:

    Dear Deblokt,
    Thank you very much for writing such a wonderful tutorial. It really helps a lot.
    I have a few questions as follows –
    1. Is there any way we can change all the Identity columns to UUID (sequential UUID) in postgresql
    2. If yes, can you please point me out how to do that.
    Thank you once again.

  • Adeel says:

    Hi Deblokt – I am really enjoying following through your tutorial. Not only I learned about identity server, but I am learning about EF and changing the DB (which I wanted to do for one of my project anyway). Good job!

Comments are closed.