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

Question

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: mark@mark.com.
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: mark@mark.com, mark@mark.com
warn: AlumniConnect.Controllers.HomeController[0]
      User: mark@mark.com, mark@mark.com
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!

Npgsql.PostgresException: 23502: null value in column "Id" violates not-null constraint. Seems like Id on [dbo].[AspNetUserClaims] is not being set. Can you check the Id on user object? — Chirdeep Tomar, Apr 04 '17 at 16:20
I have no way of checking this. The Claim class does not have an ID property. I assumed that since the UserManager is from the Identity library, it would take care of providing an ID. Could it be that the code expects an ID to be auto-generated and that this is not the case? Hmm, the 'RETURNING "Id"' seems to suggest something like that. — Katjoek, Apr 04 '17 at 18:25
I am assuming the Id property on the user object is not set, although the object is not null. Can you stick a break point in your test and inspect the user object. — Chirdeep Tomar, Apr 04 '17 at 18:33
Sorry. That is not possible. The "idRes = await _userManager.AddClaimAsync(user, claim);" throws the exception and does not allow inspection after adding the claim. My suspicion was right. The AspNetUserClaims table does not have an auto-incrementing Id column. I'll create an answer myself. — Katjoek, Apr 04 '17 at 18:47

score 4 · Accepted Answer · answered Apr 27 '17 at 07:15

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);
            });

That's a way better solution than mine. Thanks. – Katjoek Apr 27 '17 at 14:48 — Katjoek, Apr 27 '17 at 14:48

score 0 · Answer 2 · edited May 23 '17 at 11:54

0

It appears that when the Claim is added to the database, the 'RETURNING "Id"' clause of the SQL statement suggests the ID is returned. However, the table does not have an auto incrementing ID column.

I verified this by following the instructions from Adding 'serial' to existing column in Postgres.

The problem is now of course that this should have been taken care of automatically...

edited May 23 '17 at 11:54

Community

1
1

answered Apr 04 '17 at 18:52

Katjoek

361
1
3
11

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

2 Answers2