Daylight Saving Time issue in JdbcOAuth2AuthorizationService when using PostgreSQL

[wheleph]

Describe the bug

If an OAuth2Authorization is created right before a DST transition and saved to Postgres, its token’s expiration timestamp can end up earlier than its issued-at timestamp. As a result, reconstructing the OAuth2Authorization from the database later fails assertion checks.

To Reproduce

Create oauth2_authorization in Postgres DB using the official script:

/*
IMPORTANT:
    If using PostgreSQL, update ALL columns defined with 'blob' to 'text',
    as PostgreSQL does not support the 'blob' data type.
*/
CREATE TABLE oauth2_authorization (
    id varchar(100) NOT NULL,
    registered_client_id varchar(100) NOT NULL,
    principal_name varchar(200) NOT NULL,
    authorization_grant_type varchar(100) NOT NULL,
    authorized_scopes varchar(1000) DEFAULT NULL,
    attributes blob DEFAULT NULL,
    state varchar(500) DEFAULT NULL,
    authorization_code_value blob DEFAULT NULL,
    authorization_code_issued_at timestamp DEFAULT NULL,
    authorization_code_expires_at timestamp DEFAULT NULL,
    authorization_code_metadata blob DEFAULT NULL,
    access_token_value blob DEFAULT NULL,
    access_token_issued_at timestamp DEFAULT NULL,
    access_token_expires_at timestamp DEFAULT NULL,
    access_token_metadata blob DEFAULT NULL,
    access_token_type varchar(100) DEFAULT NULL,
    access_token_scopes varchar(1000) DEFAULT NULL,
    oidc_id_token_value blob DEFAULT NULL,
    oidc_id_token_issued_at timestamp DEFAULT NULL,
    oidc_id_token_expires_at timestamp DEFAULT NULL,
    oidc_id_token_metadata blob DEFAULT NULL,
    refresh_token_value blob DEFAULT NULL,
    refresh_token_issued_at timestamp DEFAULT NULL,
    refresh_token_expires_at timestamp DEFAULT NULL,
    refresh_token_metadata blob DEFAULT NULL,
    user_code_value blob DEFAULT NULL,
    user_code_issued_at timestamp DEFAULT NULL,
    user_code_expires_at timestamp DEFAULT NULL,
    user_code_metadata blob DEFAULT NULL,
    device_code_value blob DEFAULT NULL,
    device_code_issued_at timestamp DEFAULT NULL,
    device_code_expires_at timestamp DEFAULT NULL,
    device_code_metadata blob DEFAULT NULL,
    PRIMARY KEY (id)
);

Let's say the server time zone is "Europe/Kyiv" configured via -Duser.timezone=Europe/Kyiv. This time zone has standard offset of +02:00, and DST (+03:00) is observed from March to October. For instance the next switch is going to happen on 2025-10-25 at 04:00, when the clocks move back to 03:00.

If access token TTL is 30 minutes and it's generated at 3:50 (local), then its expiration time is at 3:20 (local). Due to the way Postgres works with date/time types (more on that later), the record is stored with these naïve timestamps and we get the following exception during reconstruction:

2025-09-20T15:20:23.594+03:00 ERROR 72820 --- [nio-8080-exec-1] com.example.controller.TestController    : Error when find oauth2 authorization with id bf532958-242b-4076-ad30-81c35b48ffa5

java.lang.IllegalArgumentException: expiresAt must be after issuedAt
        at org.springframework.util.Assert.isTrue(Assert.java:116) ~[spring-core-6.2.11.jar:6.2.11]
        at org.springframework.security.oauth2.core.AbstractOAuth2Token.<init>(AbstractOAuth2Token.java:63) ~[spring-security-oauth2-core-6.5.5.jar:6.5.5]
        at org.springframework.security.oauth2.core.OAuth2AccessToken.<init>(OAuth2AccessToken.java:75) ~[spring-security-oauth2-core-6.5.5.jar:6.5.5]
        at org.springframework.security.oauth2.server.authorization.JdbcOAuth2AuthorizationService$OAuth2AuthorizationRowMapper.mapRow(JdbcOAuth2AuthorizationService.java:548) ~[spring-security-oauth2-authorization-server-1.5.2.jar:1.5.2]
        at org.springframework.security.oauth2.server.authorization.JdbcOAuth2AuthorizationService$OAuth2AuthorizationRowMapper.mapRow(JdbcOAuth2AuthorizationService.java:465) ~[spring-security-oauth2-authorization-server-1.5.2.jar:1.5.2]
        at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:94) ~[spring-jdbc-6.2.11.jar:6.2.11]
        at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:61) ~[spring-jdbc-6.2.11.jar:6.2.11]
        at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:733) ~[spring-jdbc-6.2.11.jar:6.2.11]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658) ~[spring-jdbc-6.2.11.jar:6.2.11]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:723) ~[spring-jdbc-6.2.11.jar:6.2.11]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:754) ~[spring-jdbc-6.2.11.jar:6.2.11]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:809) ~[spring-jdbc-6.2.11.jar:6.2.11]
        at org.springframework.security.oauth2.server.authorization.JdbcOAuth2AuthorizationService.findBy(JdbcOAuth2AuthorizationService.java:349) ~[spring-security-oauth2-authorization-server-1.5.2.jar:1.5.2]
        at org.springframework.security.oauth2.server.authorization.JdbcOAuth2AuthorizationService.findById(JdbcOAuth2AuthorizationService.java:296) ~[spring-security-oauth2-authorization-server-1.5.2.jar:1.5.2]
        at com.example.controller.TestController.storeAndReadToken(TestController.java:53) ~[classes/:na]

Expected behavior

OAuth2Authorization instances are stored and retrieved correctly around DST transitions.

Cause and possible fix

The issue is due to the fact that the official script declares the columns to be of type timestamp, which Postgres treats as timestamp without time zone. Quote from the official documentation:

The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior

Both AbstractOAuth2Token#issuedAt and AbstractOAuth2Token#expiresAt have type java.time.Instant. For persisting to the DB they are converted to java.sql.Timestamp in JdbcOAuth2AuthorizationService:

		private <T extends OAuth2Token> List<SqlParameterValue> toSqlParameterList(String tokenColumnName,
				String tokenMetadataColumnName, OAuth2Authorization.Token<T> token) {

			List<SqlParameterValue> parameters = new ArrayList<>();
			String tokenValue = null;
			Timestamp tokenIssuedAt = null;
			Timestamp tokenExpiresAt = null;
			String metadata = null;
			if (token != null) {
				tokenValue = token.getToken().getTokenValue();
				if (token.getToken().getIssuedAt() != null) {
					tokenIssuedAt = Timestamp.from(token.getToken().getIssuedAt());
				}
				if (token.getToken().getExpiresAt() != null) {
					tokenExpiresAt = Timestamp.from(token.getToken().getExpiresAt());
				}
				metadata = writeMap(token.getMetadata());
			}

			parameters.add(mapToSqlParameter(tokenColumnName, tokenValue));
			parameters.add(new SqlParameterValue(Types.TIMESTAMP, tokenIssuedAt));
			parameters.add(new SqlParameterValue(Types.TIMESTAMP, tokenExpiresAt));
			parameters.add(mapToSqlParameter(tokenMetadataColumnName, metadata));
			return parameters;
		}

Both java.time.Instant and java.sql.Timestamp represent a point in time and don't care about JVM time zone, however their string representation is different in different time zones.

java.sql.Timestamp is sent as a string in prepared statement. Here's the relevant portion from Postgres JDBC driver (org.postgresql.jdbc.TimestampUtils#toString(java.util.Calendar, java.sql.Timestamp, boolean)):

  public String toString(@Nullable Calendar cal, Timestamp x,
      boolean withTimeZone) {
      cal = setupCalendar(cal);
      long timeMillis = x.getTime();

      cal.setTimeInMillis(timeMillis);

      sbuf.setLength(0);

      appendDate(sbuf, cal);
      sbuf.append(' ');
      appendTime(sbuf, cal, nanos);
      if (withTimeZone) {
        appendTimeZone(sbuf, cal);
      }
      appendEra(sbuf, cal);

      return sbuf.toString();
    }

So in our example the issued-at timestamp becomes "2025-10-26 03:50:00+03" and expiration timestamp becomes "2025-10-26 03:20:00+02". At this point the invariant still holds.

However in case of timestamp without time zone, Postgres simply ignores time zone indication (docs):

In a value that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication. That is, the resulting value is derived from the date/time fields in the input string, and is not adjusted for time zone.

That's why the invariant becomes broken.

To fix the issue we can define the type to be timestamptz. In that case the time zone indication is honored. In fact this is the recommended approach:

Don't use the timestamp type to store timestamps, use timestamptz (also known as timestamp with time zone) instead.

I see that Postgres already has special treatment because the SQL script contains the remark about text vs blob, so we could extend it to also include the recommendation to use timestamptz instead of plain timestamp.

Sample

Sample: https://github.com/wheleph/sas-dst-postgres

[jgrandja]

@wheleph This is a very interesting issue you found. Would you be interested in submitting a PR for the fix?

[wheleph]

@wheleph This is a very interesting issue you found. Would you be interested in submitting a PR for the fix?

I do!

Do you think that the suggested approach with adding a comment in oauth2-authorization-schema.sql makes sense?

[jgrandja]

Yes. I think adding a comment is all that is needed as there is already precedent with blob -> text