A couple of years ago I wrote a post about setting up Identity in .NET Core MVC application with MySQL. Now I am starting a new project, and this time I want to try using PostgreSQL.

.NET Core Identity PostgreSQL

It is also a good timing to prepare for the upcoming .NET 5, which is about to release next month, so I decided to do it with .NET 5.0 RC2 right away.

Environment

The previous post was written in times of .NET Core 2.0 and 2.1, but most of the things are pretty much the same (or even easier) now with .NET 5. Also, I’m doing everything on Mac OS, but yet again it will be all the same on other platforms too.

My environment:

$ sw_vers -productVersion
10.15.7

$ brew --version
Homebrew 2.5.6-61-g7f422af
Homebrew/homebrew-core (git revision e837e; last commit 2020-10-16)
Homebrew/homebrew-cask (git revision dc3b6; last commit 2020-10-16)

$ dotnet --info
.NET SDK (reflecting any global.json):
 Version:   5.0.100-rc.2.20479.15
 Commit:    da7dfa8840

Runtime Environment:
 OS Name:     Mac OS X
 OS Version:  10.15
 OS Platform: Darwin
 RID:         osx.10.15-x64
 Base Path:   /usr/local/share/dotnet/sdk/5.0.100-rc.2.20479.15/

Host (useful for support):
  Version: 5.0.0-rc.2.20475.5
  Commit:  c5a3f49c88

.NET SDKs installed:
  ...
  5.0.100-rc.2.20479.15 [/usr/local/share/dotnet/sdk]

.NET runtimes installed:
  ...
  Microsoft.AspNetCore.App 5.0.0-rc.2.20475.17 [/usr/local/share/dotnet/shared/Microsoft.AspNetCore.App]
  ...
  Microsoft.NETCore.App 5.0.0-rc.2.20475.5 [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]

PostgreSQL database

You can install PostgreSQL on Mac OS using Homebrew:

$ brew install postgresql
$ postgres --version
postgres (PostgreSQL) 13.0

Check if your user owns the folders where you’ll put the database. If not, switch to admin and make yourself the owner:

$ ls -l /usr/local/var
$ su admin
$ sudo chown -R yourname:staff /usr/local/var
$ exit

Initiate the database(s) folder, start PostgreSQL, login via psql and create a database for your application:

$ initdb --locale=C -E UTF-8 /usr/local/var/postgres
$ pg_ctl -D /usr/local/var/postgres start
$ psql postgres

postgres=# CREATE DATABASE SOME-DATABASE;
postgres=# \l
                         List of databases
   Name        | Owner    | Encoding | Collate | Ctype | Access privileges
---------------+----------+----------+---------+-------+-------------------
 SOME-DATABASE | yourname | UTF8     | C       | C     |
 postgres      | yourname | UTF8     | C       | C     |
 template0     | yourname | UTF8     | C       | C     | =c/yourname         +
               |          |          |         |       | yourname=CTc/yourname
 template1     | yourname | UTF8     | C       | C     | =c/yourname         +
               |          |          |         |       | yourname=CTc/yourname
(4 rows)

If you want to run it as a service (instead of starting it with pg_ctl), you can use Homebrew for that too:

$ brew services list
Name       Status  User Plist
ipfs       stopped
lighttpd   stopped
postgresql stopped
unbound    stopped

$ brew services start postgresql

Create a password for your user and find where the pg_hba.conf lives:

postgres=# \password
Enter new password:
Enter it again:

postgres=# SHOW hba_file;
              hba_file
-------------------------------------
 /usr/local/var/postgres/pg_hba.conf

postgres=# \q

Edit that file (/usr/local/var/postgres/pg_hba.conf) to disable logging-in without passwords:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

Restart Postgres, login back (this time it will ask you for password), connect to SOME-DATABASE and create an application user:

$ pg_ctl -D /usr/local/var/postgres restart # or brew services restart postgresql
$ psql postgres

postgres=# \c SOME-DATABASE
SOME-DATABASE=# CREATE USER birdperson WITH ENCRYPTED PASSWORD 'SOME-PASSWORD';
CREATE ROLE
SOME-DATABASE=# GRANT ALL PRIVILEGES ON DATABASE SOME-DATABASE TO birdperson;
GRANT
SOME-DATABASE=# \du
                                    List of roles
 Role name  |                         Attributes                         | Member of
------------+------------------------------------------------------------+-----------
 yourname   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 birdperson |                                                            | {}

The database is ready, let’s now create a .NET Core MVC project.

.NET Core MVC project

Create new MVC project with Individual authentication and install dotnet-ef tool:

$ mkdir SOME-PROJECT && cd $_
$ dotnet tool install --global dotnet-ef
$ dotnet tool update --global dotnet-ef --version 5.0.0-rc.2.20475.6
$ dotnet new mvc --auth Individual

By default new projects get SQLite database context, so you’ll need to remove default SQLite database file and packages and install PostgreSQL and Design packages

$ rm app.db
$ dotnet remove package Microsoft.EntityFrameworkCore.Sqlite
$ dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL --version 5.0.0-rc2
$ dotnet add package Microsoft.EntityFrameworkCore.Design --version 5.0.0-rc.2.20475.6

After .NET 5 is released, these specific -rc version suffixes won’t be needed.

Now your .csproj should look like this (after removing reference to app.db from it):

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>net5.0</TargetFramework>
    <UserSecretsId>aspnet-SOME-PROJECT-SOME-ID</UserSecretsId>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore" Version="5.0.0-rc.2.20475.17" />
    <PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="5.0.0-rc.2.20475.17" />
    <PackageReference Include="Microsoft.AspNetCore.Identity.UI" Version="5.0.0-rc.2.20475.17" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.0-rc.2.20475.6">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="5.0.0-rc.2.20475.6">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="5.0.0-rc2" />
  </ItemGroup>

</Project>

Change AddDbContext options in Startup.cs - replace UseSqlite with UseNpgsql:

services.AddDbContext<ApplicationDbContext>(options =>
  options.UseNpgsql(
      Configuration.GetConnectionString("DefaultConnection"))
);

Change the connection string in appsettings.json:

{
    "ConnectionStrings": {
      "DefaultConnection": "Server=localhost;Port=5432;Database=some;User Id=birdperson;Password=SOME-PASSWORD;"
    },
    "...": "..."
}

If you try to run the project right away, operations with the database (such as registering a new user) might fail with errors like this:

PostgresException: 42804: column "EmailConfirmed" is of type integer but expression is of type boolean
DbUpdateException: An error occurred while updating the entries. See the inner exception for details.

You might want to update PostgreSQL database with existing migration generated for SQLite, but that will fail:

$ dotnet ef database update
Method 'Create' in type 'Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitorFactory' from assembly 'Npgsql.EntityFrameworkCore.PostgreSQL, Version=3.1.4.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7' does not have an implementation.

So you’d want to remove the migration first, but that will fail too:

$ dotnet ef migrations remove
Build started...
Build succeeded.
The migration '00000000000000_CreateIdentitySchema' has already been applied to the database. Revert it and try again. If the migration has been applied to other databases, consider reverting its changes using a new migration instead.

And the right sequence of commands is the following:

$ dotnet ef database update 0
$ dotnet ef migrations remove
$ dotnet ef migrations add CreateIdentitySchema
$ dotnet ef database update

After that you can connect to the database and check that you have all the required Identity tables:

$ psql postgres
postgres=# \c SOME-DATABASE
SOME-DATABASE=# \d
                    List of relations
 Schema |          Name           |   Type   |   Owner
--------+-------------------------+----------+------------
 public | AspNetRoleClaims        | table    | birdperson
 public | AspNetRoleClaims_Id_seq | sequence | birdperson
 public | AspNetRoles             | table    | birdperson
 public | AspNetUserClaims        | table    | birdperson
 public | AspNetUserClaims_Id_seq | sequence | birdperson
 public | AspNetUserLogins        | table    | birdperson
 public | AspNetUserRoles         | table    | birdperson
 public | AspNetUserTokens        | table    | birdperson
 public | AspNetUsers             | table    | birdperson
 public | __EFMigrationsHistory   | table    | birdperson
(10 rows)

It’s done, you can now run your project and register your first user.

Scaffolding Identity code

You might want to customize default Identity views and/or modify its code, but by default your project doesn’t have these files, so first you’ll need to scaffold them.

Install required packages and the tool:

$ dotnet tool install -g dotnet-aspnet-codegenerator
$ dotnet tool update --global dotnet-aspnet-codegenerator --version 5.0.0-rc.2.20473.1
$ dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design --version 5.0.0-rc.2.20475.17
$ dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 5.0.0-rc.2.20475.6

Run the tool with -h parameter to see the help output and to check if there any problems with your project (if any, they will be listed in the end):

$ dotnet aspnet-codegenerator identity -h

To see the full list of files you can scaffold/generate:

$ dotnet aspnet-codegenerator identity --listFiles
Building project ...
Finding the generator 'identity'...
Running the generator 'identity'...
File List:
Account._StatusMessage
Account.AccessDenied
Account.ConfirmEmail
Account.ConfirmEmailChange
Account.ExternalLogin
Account.ForgotPassword
Account.ForgotPasswordConfirmation
Account.Lockout
Account.Login
Account.LoginWith2fa
Account.LoginWithRecoveryCode
Account.Logout
Account.Manage._Layout
Account.Manage._ManageNav
Account.Manage._StatusMessage
Account.Manage.ChangePassword
Account.Manage.DeletePersonalData
Account.Manage.Disable2fa
Account.Manage.DownloadPersonalData
Account.Manage.Email
Account.Manage.EnableAuthenticator
Account.Manage.ExternalLogins
Account.Manage.GenerateRecoveryCodes
Account.Manage.Index
Account.Manage.PersonalData
Account.Manage.ResetAuthenticator
Account.Manage.SetPassword
Account.Manage.ShowRecoveryCodes
Account.Manage.TwoFactorAuthentication
Account.Register
Account.RegisterConfirmation
Account.ResendEmailConfirmation
Account.ResetPassword
Account.ResetPasswordConfirmation
RunTime 00:00:07.77

So if you want to generate only AccessDenied, Register, Login and Logout pages, then:

$ dotnet aspnet-codegenerator identity -dc SOME-PROJECT.Data.ApplicationDbContext --files "Account.AccessDenied;Account.Register;Account.Login;Account.Logout"

That will generate the following files in your project:

Areas/
└── Identity
    ├── IdentityHostingStartup.cs
    └── Pages
        ├── Account
        │   ├── AccessDenied.cshtml
        │   ├── AccessDenied.cshtml.cs
        │   ├── Login.cshtml
        │   ├── Login.cshtml.cs
        │   ├── Logout.cshtml
        │   ├── Logout.cshtml.cs
        │   ├── Register.cshtml
        │   ├── Register.cshtml.cs
        │   └── _ViewImports.cshtml
        ├── _ValidationScriptsPartial.cshtml
        ├── _ViewImports.cshtml
        └── _ViewStart.cshtml

Why the fuck it’s not MVC but Razor Pages - because Microsoft just decided so. Good thing it still works though, so you can customize those any way you want.

If you would like to generate all the possible files, run that command without --files parameter (or with --useDefaultUI parameter).

Microsoft documentation also claims that after scaffolding you’d need to make some changes in Startup.cs, but that part I didn’t quite got, as everything works for me as it is.