Can't add a claim with ASP.NET Core and identity

Katjoek

I try to add a custom claim to an existing Identity user but I get an exception at run-time:

Npgsql.PostgresException: 23502: null value in column "Id" violates not-null constraint

Help!

What I did. I've created a simple web app on windows using the following command line

dotnet new mvc --auth Individual --framework netcoreapp1.1

I made changes found here to make the app use PostgreSQL as the database back-end. The created default webapp works fine. I can register as a new user, login, log out, etc...

Then I modified the Test method of the Home controller (I know the exceptions are ugly):

    [Authorize]
    public async Task<IActionResult> Test()
    {
        var user = await GetCurrentUserAsync();
        if (user == null) {
            _logger.LogWarning("User is null.");
            throw new Exception("Not logged in");
        }
        _logger.LogWarning("User: {0}, {1}", user.Email, user);

        var claim = new Claim("TestClaimType", "TestClaimValue");

        IdentityResult idRes = IdentityResult.Failed();
        if (_userManager.SupportsUserClaim) {
            idRes = await _userManager.AddClaimAsync(user, claim); <------- Adding the claim
        }
        _logger.LogWarning("Return from adding claim");

        if (idRes != IdentityResult.Success) {
            throw new Exception("Failed to add claim.");
        }

        return View();
    }

After logging in, I trigger the Test method and get the following logging (the PostgresException is near the end):

info: Microsoft.AspNetCore.Hosting.Internal.WebHost[1]
      Request starting HTTP/1.1 GET http://localhost:5000/Home/Test
info: Microsoft.AspNetCore.Authentication.Cookies.CookieAuthenticationMiddleware[3]
      HttpContext.User merged via AutomaticAuthentication from authenticationScheme: Identity.Application.
info: Microsoft.AspNetCore.Authorization.DefaultAuthorizationService[1]
      Authorization was successful for user: [email protected].
info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[1]
      Executing action method AlumniConnect.Controllers.HomeController.Test (AlumniConnect) with arguments ((null)) - ModelState is Valid
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (1ms) [Parameters=[@__get_Item_0='?'], CommandType='Text', CommandTimeout='30']
      SELECT "e"."Id", "e"."AccessFailedCount", "e"."ConcurrencyStamp", "e"."Email", "e"."EmailConfirmed", "e"."LockoutEnabled", "e"."LockoutEnd", "e"."NormalizedEmail", "e"."NormalizedUserName", "e"."PasswordHash", "e"."PhoneNumber", "e"."PhoneNumberConfirmed", "e"."SecurityStamp", "e"."TwoFactorEnabled", "e"."UserName"
      FROM "AspNetUsers" AS "e"
      WHERE "e"."Id" = @__get_Item_0
      LIMIT 1
warn: AlumniConnect.Controllers.HomeController[0]
      User: [email protected], [email protected]
warn: AlumniConnect.Controllers.HomeController[0]
      User: [email protected], [email protected]
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (4ms) [Parameters=[@__normalizedUserName_0='?'], CommandType='Text', CommandTimeout='30']
      SELECT "u"."Id", "u"."AccessFailedCount", "u"."ConcurrencyStamp", "u"."Email", "u"."EmailConfirmed", "u"."LockoutEnabled", "u"."LockoutEnd", "u"."NormalizedEmail", "u"."NormalizedUserName", "u"."PasswordHash", "u"."PhoneNumber", "u"."PhoneNumberConfirmed", "u"."SecurityStamp", "u"."TwoFactorEnabled", "u"."UserName"
      FROM "AspNetUsers" AS "u"
      WHERE "u"."NormalizedUserName" = @__normalizedUserName_0
      LIMIT 1
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (33ms) [Parameters=[@p0='?', @p1='?', @p2='?', @p17='?', @p3='?', @p4='?', @p18='?', @p5='?', @p6='?', @p7='?', @p8='?', @p9='?', @p10='?', @p11='?', @p12='?', @p13='?', @p14='?', @p15='?', @p16='?'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "AspNetUserClaims" ("ClaimType", "ClaimValue", "UserId")
      VALUES (@p0, @p1, @p2)
      RETURNING "Id";
      UPDATE "AspNetUsers" SET "AccessFailedCount" = @p3, "ConcurrencyStamp" = @p4, "Email" = @p5, "EmailConfirmed" = @p6, "LockoutEnabled" = @p7, "LockoutEnd" = @p8, "NormalizedEmail" = @p9, "NormalizedUserName" = @p10, "PasswordHash" = @p11, "PhoneNumber" = @p12, "PhoneNumberConfirmed" = @p13, "SecurityStamp" = @p14, "TwoFactorEnabled" = @p15, "UserName" = @p16
      WHERE "Id" = @p17 AND "ConcurrencyStamp" = @p18;
fail: Microsoft.EntityFrameworkCore.DbContext[1]
      An exception occurred in the database while saving changes.
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Npgsql.PostgresException: 23502: null value in column "Id" violates not-null constraint
         at Npgsql.NpgsqlConnector.<DoReadMessageAsync>d__6.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---

There's lots of logging more but it doesn't seem to add new information. I see the same exception mentioned multiple times throughout the log.

What can I do? Is this a PostgreSQL specific issue? Am I trying to add a claim in the wrong way?

Thanks!

rasmantuta

In the migration for Identity, all tables that have a generated Integer id have an annotation for adding auto generation of this id. This annotation is SQL Server specific, like .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn)

The fix for this is to add a Postgres specific annotation to the migration: .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SerialColumn). In older versions of Npgsql.EntityFrameworkCore.PostgreSQL it could be that you need to use .Annotation("Npgsql:ValueGeneratedOnAdd", true).

The part of the migration for creating the AspNetRoleClaims table will then look like:

            migrationBuilder.CreateTable(
            name: "AspNetRoleClaims",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn)
                    .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SerialColumn),
                ClaimType = table.Column<string>(nullable: true),
                ClaimValue = table.Column<string>(nullable: true),
                RoleId = table.Column<string>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_AspNetRoleClaims", x => x.Id);
                table.ForeignKey(
                    name: "FK_AspNetRoleClaims_AspNetRoles_RoleId",
                    column: x => x.RoleId,
                    principalTable: "AspNetRoles",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            });

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

ASP.Net Core MVC Identity - Add temporary (session) claim

Add persistent claim with Asp.Net Core Identity

ASP.NET Core Identity Role, Claim and User

Adding custom claim not working in asp.net core identity

IdentityServer4 can't add asp net core identity with custom user store

ASP.NET Core - Add role claim to User

Add UserClaim with Identity asp.net core

ASP.NET Identity not "saving" updated claim

Asp Net Identity - Claim vs Customized IdentityUser

How to update a claim in ASP.NET Identity?

Customize asp.net identity role claim

Upgraded to .net core 2.1 and now I can't serialize a claim

ASP.NET Core 3.1: User.Identity.Name is empty in API Controller, but claim name is present

I can't add view in ASP.NET Core MVC

How can i add a Identity Role in a ASP.NET Core Webapi

ASP.NET Core 3.0 Identity doesn't add any authentication data to my browser

Claim based authorization in ASP.NET Core

Asp.NET Core Claim based Authorization

.NET Core add Claim after AzuerAD Authentication

.net core mvc Mocking Identity and claims and test if user has claim

Add a new table into the ASP .NET Core MVC app with Identity

Add Collection to user object in ASP.NET Core with Identity

How to add multiple identity and multiple role in asp.net core

How to add new colum into Identity RoleClaims table (asp net core)

How to add Identity Services in ASP.NET Core 5

How to add Enum Properties in ASP.NET Core 6 Identity

asp.net identity claim fullname in view page

I can Authenticate with my JWT but my Name claim is not recognised in my ASP.NET Core application

ASP.NET Core 2.1 Identity couldn't find ApplicationUser