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.
Hi from Russia!
Your manual the best. Thanks!
Hi! Thank you 🙂
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.
Please make sure you are using the “UseNpgsql” extension in Startup instead of the default one for MS SQL.
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.
I also had to update the tables with the commands.
It is working now.
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
You can’t use package manager console you have to use dotnet CLI. Take a look at the documentation here https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/?tabs=dotnet-core-cli
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.
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!
Thank you 🙂
Comments are closed.