Exception removing expired grants: Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone'

[Lederilon]

IdentityServer version

7.1.0

.NET version

net8.0

Description

Hi!
It seems that the TokenCleanup job failed for PostgreSQL

[14:00:44 ERR] Exception removing expired grants: Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array, range, or multirange. (Parameter 'value')
[14:00:44 ERR] Failed executing DbCommand (3ms) [Parameters=[@__First_Expiration_0='?' (DbType = DateTime), @__Last_Expiration_1='?' (DbType = DateTime), @__foundIds_2='?' (DbType = Object)], CommandType='Text', CommandTimeout='300']
DELETE FROM "PersistedGrants" AS p
WHERE p."Id" IN (
SELECT p0."Id"
FROM "PersistedGrants" AS p0
WHERE p0."Expiration" < now() AND p0."Expiration" >= @__First_Expiration_0 AND p0."Expiration" <= @__Last_Expiration_1 AND p0."Id" = ANY (@__foundIds_2)

We have:
options.EnableTokenCleanup = true;

Reproduction steps

No response

Expected behavior

No response

Logs

[14:00:44 ERR] Exception removing expired grants: Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array, range, or multirange. (Parameter 'value')
[14:00:44 ERR] Failed executing DbCommand (3ms) [Parameters=[@__First_Expiration_0='?' (DbType = DateTime), @__Last_Expiration_1='?' (DbType = DateTime), @__foundIds_2='?' (DbType = Object)], CommandType='Text', CommandTimeout='300']
DELETE FROM "PersistedGrants" AS p
WHERE p."Id" IN (
SELECT p0."Id"
FROM "PersistedGrants" AS p0
WHERE p0."Expiration" < now() AND p0."Expiration" >= @__First_Expiration_0 AND p0."Expiration" <= @__Last_Expiration_1 AND p0."Id" = ANY (@__foundIds_2)

Additional context

No response

[RolandGuijt]

Was this introduced after you upgraded to IdentityServer 7.1.0? If so, from which version?

[Lederilon]

We migrated from Identity 4.1.2

[RolandGuijt]

There was an important change regarding DateTime in the Entity Framework provider for PostgreSQL. And somehow the delete query that is being generated doesn't take that into account.
Can you please check if the NuGet package is up-to-date and if it is, can you try switching back to the old behavior?

public PersistedGrantDbContext(DbContextOptions<PersistedGrantDbContext> options)
    : base(options)
{
    AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
}

[Lederilon]

We use Npgsql.EntityFrameworkCore.PostgreSQL" Version="8.0.4 long ago before migrated into Duende and all queries were fine until that
Could
https://github.com/DuendeSoftware/products/blob/main/identity-server/src/EntityFramework.Storage/TokenCleanup/TokenCleanupService.cs#L171
be fixed to be generated properly?

[Lederilon]

Any plans to fix a delete query generation bug ?

[RolandGuijt]

In 2023 the LINQ queries that do the deletes were changed to use ExecuteDeleteAsync. That enhanced the efficiency of the service and it has been stable since that time. Apparently there's a problem with the PostgreSQL provider supporting that in combination with date/time fields and this is not something to fix in our code.

Other than the hint above you can also create your own version of the class and register it in DI using ITokenCleanupService. The essential methods are virtual. By overriding you will be able to experiment with different kinds of queries and see if these work with the PostgreSQL provider.

[Lederilon]

Do you have integration tests for PostgreSQL compatibility that cover TokenCleanupService?
What version of Npgsql did you test with?