Primary key for Identity passkeys table too large

[davidkarlsson]

Is there an existing issue for this?

• [x] I have searched the existing issues

Describe the bug

ASP.NET Core Identity adds a new table for passkeys in .NET 10 called AspNetUserPasskeys by default and the primary key for this table is set to have a max length of 1024. This results in a warning when creating the table in SQL Server because the maximum key length for clustered indexes is 900 bytes:

Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK_AspNetUserPasskeys' has maximum length of 1024 bytes. For some combination of large values, the insert/update operation will fail.

Expected Behavior

I'm not sure what the best solution would be but I expected the primary key to have a smaller max length than the maximum so that inserts/updates can't fail for large passkeys. So if the CredentialId can't be smaller than 1024 bytes because of the WebAuthn spec then perhaps another column should've been used as the primary key?

Steps To Reproduce

Configure EF Core with SqlServer and add Identity with IdentitySchemaVersions.Version3 and the initial migration will then add the AspNetUserPasskeys table with a primary key that's too large:

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection") ?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found.");
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(connectionString));
builder.Services.AddDatabaseDeveloperPageExceptionFilter();

builder.Services.AddDefaultIdentity<IdentityUser>(options => {
    options.SignIn.RequireConfirmedAccount = true;
    options.Stores.SchemaVersion = IdentitySchemaVersions.Version3;
})
    .AddEntityFrameworkStores<ApplicationDbContext>();

Exceptions (if any)

No response

.NET Version

10.0.100

Anything else?

No response

[MackinnonBuck]

@roji, are you familiar with this 900 byte limit for clustered indexes? Is this a real issue? Do you know of any workarounds we can apply?

[Tornhoof]

SQL Server limitation: https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver17 (900 for clustered Indexes, 1700 for non-clustered). Likely other DB have similar limitations.

One of the common workarounds is to use a computed column as the primary key instead, either via a hashed value or a shortened value, eg just sha256 the id as the computed column.

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-computed-column-definition-transact-sql?view=sql-server-ver17

[roji]

@MackinnonBuck yeah, SQL Server has some restrictions about sizes of indexed columns.

It's common to have synthetic (or surrogate) primary keys, meaning that they store information just to provide a unique identifier (random GUID or autoincrement int/bigint); these values don't actually have any meaning for the actual record - they're just they're as a key. Where the record already has a 100% unique, short identifier (like a social security number), those can be used instead ("natural PK"), but that tends to be more the exception than the common case.

I'm happy to try to think about this more, but can you provide a bit more context about what the schema here, what's being stored as the PK, etc.? Maybe the schema needs to be reviewed to switch to e.g. a GUID PK. A link to the problematic code could help too.

Likely other DB have similar limitations

FWIW I'm not aware of such limitations in PostgreSQL or SQLite. There may be in others, but I'm not sure - this could be a SQL Server restriction. But in general, indexing (and keying) over potentially big values should be avoided when possible for performance reasons.

[Tornhoof]

@roji Oracle has some limitations, depending on the page size internally, especially in older versions.

Mysql has similar limitations as SQL Server https://dev.mysql.com/doc/refman/9.5/en/innodb-limits.html I expect similar/same for MariaDB.

I'm not aware of any limitations in PostgreSQL either, but you know a lot more about PostgreSQL than I do.

[roji]

@Tornhoof thanks, that's useful. In any case, even regardless of specific database restrictions, it's usually wise to not have big primary keys for efficiency reasons.

[MackinnonBuck]

Thanks, @roji.

I'm happy to try to think about this more, but can you provide a bit more context about what the schema here, what's being stored as the PK, etc.? Maybe the schema needs to be reviewed to switch to e.g. a GUID PK. A link to the problematic code could help too.

The entity type is IdentityUserPasskey, which has a <= 1023 byte CredentialId, the associated user ID, plus a JSON column of type IdentityPasskeyData. The EF Core schema configuration can be found here.

The IdentityUserPasskey type is modeled after "Credential Record" as described in the WebAuthn spec. The rationale for using the credential ID as the PK was that we need to be able to retrieve a credential from the authenticator-provided credential ID:

• When verifying that the ID of a new credential is not already registered
• When retrieving the credential record for an existing credential during authentication

So the credential ID was a convenient property to make the primary key, but it doesn't have to work that way. We could consider switching to a GUID PK and make the credential ID a unique non-clustered index.

[roji]

So the credential ID was a convenient property to make the primary key, but it doesn't have to work that way. We could consider switching to a GUID PK and make the credential ID a unique non-clustered index.

Yeah, I think that'd be the thing to do here. FWIW a byte[] primary key is also a bit unusual to see, and may create problems on some databases. A regular column with a non-clustered index sounds good here.