Authorization server can't run on PostgresSQL out of the box

[vladimir-cirkovic]

Expected Behavior
Plug and Play postgres database.

Current Behavior
JdbcOAuth2AuthorizationService doesn't allow changing of authorizationRowMapper, authorizationParametersMapper to support usage of databases which doesn't have blob type. When those mappers are changed, objectMapper return following issue
The class with org.springframework.security.ldap.userdetails.LdapUserDetailsImpl and name of org.springframework.security.ldap.userdetails.LdapUserDetailsImpl is not in the allowlist. If you believe this class is safe to deserialize, please provide an explicit mapping using Jackson annotations or by providing a Mixin. If the serialization is only done by a trusted source, you can also enable default typing. See https://github.com/spring-projects/spring-security/issues/4370

Context
This issue is related to postgres database, just I see that auth server can't be used easily with one of the most popular open source database.

[vladimir-cirkovic]

Here is my draft code

Changed SQL table
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, attributes varchar(4000) DEFAULT NULL, state varchar(500) DEFAULT NULL, authorization_code_value varchar(2000) DEFAULT NULL, authorization_code_issued_at timestamp DEFAULT NULL, authorization_code_expires_at timestamp DEFAULT NULL, authorization_code_metadata varchar(2000) DEFAULT NULL, access_token_value varchar(2000) DEFAULT NULL, access_token_issued_at timestamp DEFAULT NULL, access_token_expires_at timestamp DEFAULT NULL, access_token_metadata varchar(2000) DEFAULT NULL, access_token_type varchar(100) DEFAULT NULL, access_token_scopes varchar(1000) DEFAULT NULL, oidc_id_token_value varchar(2000) DEFAULT NULL, oidc_id_token_issued_at timestamp DEFAULT NULL, oidc_id_token_expires_at timestamp DEFAULT NULL, oidc_id_token_metadata varchar(2000) DEFAULT NULL, refresh_token_value varchar(2000) DEFAULT NULL, refresh_token_issued_at timestamp DEFAULT NULL, refresh_token_expires_at timestamp DEFAULT NULL, refresh_token_metadata varchar(2000) DEFAULT NULL, PRIMARY KEY (id) );

Impl for PostgresJdbcAuthorizationService

public class PostgresJdbcAuthorizationService extends JdbcOAuth2AuthorizationService
{
private final JdbcOperations jdbcOperations;

	public PostgresJdbcAuthorizationService(JdbcOperations jdbcOperations, RegisteredClientRepository registeredClientRepository)
	{
		super(jdbcOperations, registeredClientRepository);
		this.setAuthorizationRowMapper(new PostgresJdbcAuthorizationService.OAuth2AuthorizationRowMapper(registeredClientRepository));
		this.setAuthorizationParametersMapper(new PostgresJdbcAuthorizationService.OAuth2AuthorizationParametersMapper());
		this.jdbcOperations = jdbcOperations;
	}

	private static final String COLUMN_NAMES = "id, "
		+ "registered_client_id, "
		+ "principal_name, "
		+ "authorization_grant_type, "
		+ "attributes, "
		+ "state, "
		+ "authorization_code_value, "
		+ "authorization_code_issued_at, "
		+ "authorization_code_expires_at,"
		+ "authorization_code_metadata,"
		+ "access_token_value,"
		+ "access_token_issued_at,"
		+ "access_token_expires_at,"
		+ "access_token_metadata,"
		+ "access_token_type,"
		+ "access_token_scopes,"
		+ "oidc_id_token_value,"
		+ "oidc_id_token_issued_at,"
		+ "oidc_id_token_expires_at,"
		+ "oidc_id_token_metadata,"
		+ "refresh_token_value,"
		+ "refresh_token_issued_at,"
		+ "refresh_token_expires_at,"
		+ "refresh_token_metadata";

	private static final String TABLE_NAME = "oauth2_authorization";

	private static final String PK_FILTER = "id = ?";
	private static final String UNKNOWN_TOKEN_TYPE_FILTER = "state = ? OR authorization_code_value = ? OR " +
		"access_token_value = ? OR refresh_token_value = ?";

	private static final String STATE_FILTER = "state = ?";
	private static final String AUTHORIZATION_CODE_FILTER = "authorization_code_value = ?";
	private static final String ACCESS_TOKEN_FILTER = "access_token_value = ?";
	private static final String REFRESH_TOKEN_FILTER = "refresh_token_value = ?";

	private static final String LOAD_AUTHORIZATION_SQL = "SELECT " + COLUMN_NAMES
		+ " FROM " + TABLE_NAME
		+ " WHERE ";

	@Nullable
	@Override
	public OAuth2Authorization findByToken(String token, @Nullable OAuth2TokenType tokenType)
	{
		Assert.hasText(token, "token cannot be empty");
		List<SqlParameterValue> parameters = new ArrayList<>();
		if (tokenType == null)
		{
			parameters.add(new SqlParameterValue(Types.VARCHAR, token));
			parameters.add(new SqlParameterValue(Types.VARCHAR, token));
			parameters.add(new SqlParameterValue(Types.VARCHAR, token));
			parameters.add(new SqlParameterValue(Types.VARCHAR, token));
			return findBy(UNKNOWN_TOKEN_TYPE_FILTER, parameters);
		}
		else if (OAuth2ParameterNames.STATE.equals(tokenType.getValue()))
		{
			parameters.add(new SqlParameterValue(Types.VARCHAR, token));
			return findBy(STATE_FILTER, parameters);
		}
		else if (OAuth2ParameterNames.CODE.equals(tokenType.getValue()))
		{
			parameters.add(new SqlParameterValue(Types.VARCHAR, token));
			return findBy(AUTHORIZATION_CODE_FILTER, parameters);
		}
		else if (OAuth2TokenType.ACCESS_TOKEN.equals(tokenType))
		{
			parameters.add(new SqlParameterValue(Types.VARCHAR, token));
			return findBy(ACCESS_TOKEN_FILTER, parameters);
		}
		else if (OAuth2TokenType.REFRESH_TOKEN.equals(tokenType))
		{
			parameters.add(new SqlParameterValue(Types.VARCHAR, token));
			return findBy(REFRESH_TOKEN_FILTER, parameters);
		}
		return null;
	}

	private OAuth2Authorization findBy(String filter, List<SqlParameterValue> parameters)
	{
		PreparedStatementSetter pss = new ArgumentPreparedStatementSetter(parameters.toArray());
		List<OAuth2Authorization> result = this.jdbcOperations.query(LOAD_AUTHORIZATION_SQL + filter, pss, this.getAuthorizationRowMapper());
		return !result.isEmpty() ? result.get(0) : null;
	}

	/**
	 * The default {@link RowMapper} that maps the current row in
	 * {@code java.sql.ResultSet} to {@link OAuth2Authorization}.
	 */
	public static class OAuth2AuthorizationRowMapper extends JdbcOAuth2AuthorizationService.OAuth2AuthorizationRowMapper
	{
		private final RegisteredClientRepository registeredClientRepository;

		public OAuth2AuthorizationRowMapper(RegisteredClientRepository registeredClientRepository)
		{
			super(registeredClientRepository);
			Assert.notNull(registeredClientRepository, "registeredClientRepository cannot be null");
			this.registeredClientRepository = registeredClientRepository;

			getObjectMapper().addMixIn(LdapUserDetailsImpl.class, LdapUserDetailsMixIn.class);
		}

		@Override
		@SuppressWarnings("unchecked")
		public OAuth2Authorization mapRow(ResultSet rs, int rowNum) throws SQLException
		{
			String registeredClientId = rs.getString("registered_client_id");
			RegisteredClient registeredClient = this.registeredClientRepository.findById(registeredClientId);
			if (registeredClient == null)
			{
				throw new DataRetrievalFailureException(
					"The RegisteredClient with id '" + registeredClientId + "' was not found in the RegisteredClientRepository.");
			}

			OAuth2Authorization.Builder builder = OAuth2Authorization.withRegisteredClient(registeredClient);
			String id = rs.getString("id");
			String principalName = rs.getString("principal_name");
			String authorizationGrantType = rs.getString("authorization_grant_type");
			Map<String, Object> attributes = parseMap(rs.getString("attributes"));

			builder.id(id)
				.principalName(principalName)
				.authorizationGrantType(new AuthorizationGrantType(authorizationGrantType))
				.attributes((attrs) -> attrs.putAll(attributes));

			String state = rs.getString("state");
			if (StringUtils.hasText(state))
			{
				builder.attribute(OAuth2ParameterNames.STATE, state);
			}

			String tokenValue;
			Instant tokenIssuedAt;
			Instant tokenExpiresAt;
			String authorizationCodeValue = rs.getString("authorization_code_value");

			if (authorizationCodeValue != null)
			{
				tokenValue = authorizationCodeValue;
				tokenIssuedAt = rs.getTimestamp("authorization_code_issued_at").toInstant();
				tokenExpiresAt = rs.getTimestamp("authorization_code_expires_at").toInstant();
				Map<String, Object> authorizationCodeMetadata = parseMap(rs.getString("authorization_code_metadata"));

				OAuth2AuthorizationCode authorizationCode = new OAuth2AuthorizationCode(
					tokenValue, tokenIssuedAt, tokenExpiresAt);
				builder.token(authorizationCode, (metadata) -> metadata.putAll(authorizationCodeMetadata));
			}

			String accessTokenValue = rs.getString("access_token_value");
			if (accessTokenValue != null)
			{
				tokenValue = accessTokenValue;
				tokenIssuedAt = rs.getTimestamp("access_token_issued_at").toInstant();
				tokenExpiresAt = rs.getTimestamp("access_token_expires_at").toInstant();
				Map<String, Object> accessTokenMetadata = parseMap(rs.getString("access_token_metadata"));
				OAuth2AccessToken.TokenType tokenType = null;
				if (OAuth2AccessToken.TokenType.BEARER.getValue().equalsIgnoreCase(rs.getString("access_token_type")))
				{
					tokenType = OAuth2AccessToken.TokenType.BEARER;
				}

				Set<String> scopes = Collections.emptySet();
				String accessTokenScopes = rs.getString("access_token_scopes");
				if (accessTokenScopes != null)
				{
					scopes = StringUtils.commaDelimitedListToSet(accessTokenScopes);
				}
				OAuth2AccessToken accessToken = new OAuth2AccessToken(tokenType, tokenValue, tokenIssuedAt, tokenExpiresAt, scopes);
				builder.token(accessToken, (metadata) -> metadata.putAll(accessTokenMetadata));
			}

			String oidcIdTokenValue = rs.getString("oidc_id_token_value");
			if (oidcIdTokenValue != null)
			{
				tokenValue = oidcIdTokenValue;
				tokenIssuedAt = rs.getTimestamp("oidc_id_token_issued_at").toInstant();
				tokenExpiresAt = rs.getTimestamp("oidc_id_token_expires_at").toInstant();
				Map<String, Object> oidcTokenMetadata = parseMap(rs.getString("oidc_id_token_metadata"));

				OidcIdToken oidcToken = new OidcIdToken(
					tokenValue, tokenIssuedAt, tokenExpiresAt, (Map<String, Object>) oidcTokenMetadata.get(OAuth2Authorization.Token.CLAIMS_METADATA_NAME));
				builder.token(oidcToken, (metadata) -> metadata.putAll(oidcTokenMetadata));
			}

			String refreshTokenValue = rs.getString("refresh_token_value");
			if (refreshTokenValue != null)
			{
				tokenValue = refreshTokenValue;
				tokenIssuedAt = rs.getTimestamp("refresh_token_issued_at").toInstant();
				tokenExpiresAt = null;
				Timestamp refreshTokenExpiresAt = rs.getTimestamp("refresh_token_expires_at");
				if (refreshTokenExpiresAt != null)
				{
					tokenExpiresAt = refreshTokenExpiresAt.toInstant();
				}
				Map<String, Object> refreshTokenMetadata = parseMap(rs.getString("refresh_token_metadata"));

				OAuth2RefreshToken refreshToken = new OAuth2RefreshToken(
					tokenValue, tokenIssuedAt, tokenExpiresAt);
				builder.token(refreshToken, (metadata) -> metadata.putAll(refreshTokenMetadata));
			}
			return builder.build();
		}


		private Map<String, Object> parseMap(String data)
		{
			try
			{
				return getObjectMapper().readValue(data, new TypeReference<Map<String, Object>>()
				{
				});
			}
			catch (Exception ex)
			{
				throw new IllegalArgumentException(ex.getMessage(), ex);
			}
		}

	}

	/**
	 * The default {@code Function} that maps {@link OAuth2Authorization} to a
	 * {@code List} of {@link SqlParameterValue}.
	 */
	public static class OAuth2AuthorizationParametersMapper extends JdbcOAuth2AuthorizationService.OAuth2AuthorizationParametersMapper
	{
		@Override
		public List<SqlParameterValue> apply(OAuth2Authorization authorization)
		{
			List<SqlParameterValue> parameters = new ArrayList<>();
			parameters.add(new SqlParameterValue(Types.VARCHAR, authorization.getId()));
			parameters.add(new SqlParameterValue(Types.VARCHAR, authorization.getRegisteredClientId()));
			parameters.add(new SqlParameterValue(Types.VARCHAR, authorization.getPrincipalName()));
			parameters.add(new SqlParameterValue(Types.VARCHAR, authorization.getAuthorizationGrantType().getValue()));

			String attributes = writeMap(authorization.getAttributes());
			parameters.add(new SqlParameterValue(Types.VARCHAR, attributes));

			String state = null;
			String authorizationState = authorization.getAttribute(OAuth2ParameterNames.STATE);
			if (StringUtils.hasText(authorizationState))
			{
				state = authorizationState;
			}
			parameters.add(new SqlParameterValue(Types.VARCHAR, state));

			OAuth2Authorization.Token<OAuth2AuthorizationCode> authorizationCode =
				authorization.getToken(OAuth2AuthorizationCode.class);
			List<SqlParameterValue> authorizationCodeSqlParameters = toSqlParameterList(authorizationCode);
			parameters.addAll(authorizationCodeSqlParameters);

			OAuth2Authorization.Token<OAuth2AccessToken> accessToken =
				authorization.getToken(OAuth2AccessToken.class);
			List<SqlParameterValue> accessTokenSqlParameters = toSqlParameterList(accessToken);
			parameters.addAll(accessTokenSqlParameters);
			String accessTokenType = null;
			String accessTokenScopes = null;
			if (accessToken != null)
			{
				accessTokenType = accessToken.getToken().getTokenType().getValue();
				if (!CollectionUtils.isEmpty(accessToken.getToken().getScopes()))
				{
					accessTokenScopes = StringUtils.collectionToDelimitedString(accessToken.getToken().getScopes(), ",");
				}
			}
			parameters.add(new SqlParameterValue(Types.VARCHAR, accessTokenType));
			parameters.add(new SqlParameterValue(Types.VARCHAR, accessTokenScopes));

			OAuth2Authorization.Token<OidcIdToken> oidcIdToken = authorization.getToken(OidcIdToken.class);
			List<SqlParameterValue> oidcIdTokenSqlParameters = toSqlParameterList(oidcIdToken);
			parameters.addAll(oidcIdTokenSqlParameters);

			OAuth2Authorization.Token<OAuth2RefreshToken> refreshToken = authorization.getRefreshToken();
			List<SqlParameterValue> refreshTokenSqlParameters = toSqlParameterList(refreshToken);
			parameters.addAll(refreshTokenSqlParameters);
			return parameters;
		}

		private <T extends AbstractOAuth2Token> List<SqlParameterValue> toSqlParameterList(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(new SqlParameterValue(Types.VARCHAR, tokenValue));
			parameters.add(new SqlParameterValue(Types.TIMESTAMP, tokenIssuedAt));
			parameters.add(new SqlParameterValue(Types.TIMESTAMP, tokenExpiresAt));
			parameters.add(new SqlParameterValue(Types.VARCHAR, metadata));
			return parameters;
		}

		private String writeMap(Map<String, Object> data)
		{
			try
			{
				return getObjectMapper().writeValueAsString(data);
			}
			catch (Exception ex)
			{
				throw new IllegalArgumentException(ex.getMessage(), ex);
			}
		}

	}

This is workaround for using Postgres and VARCHAR instead of BLOB.

[ghost]

hi. This is the default behavior. JdbcOAuth2AuthorizationService is aligned with JdbcOAuth2AuthorizedClientService (in spring-security-oauth2-client) as they both use blob for storing token credentials.

See comment,

It is very difficult to provide an implementation that works out of the box for all databases. This implementation strives to use standard sql datatypes and is a simplified JDBC implementation. However, it is designed to be customizable so user's can provide customizations for database vendors that deviate from the standard sql types.

The configuration can be customized as mentioned in this comment which is similar with your proposed solution.

[vladimir-cirkovic]

Thanks, I saw mentioned issues, this one is enhancement which I think may be useful to have it.

There is workaround for this, just maybe to consider adding the better way to override behavior, this way I had to take a lot of code from JdbcOAuth2AuthorizationService in order to have working solution.

[jimreader]

The ObjectMapper issue can be resolved by creating a mixin for LdapUserDetailsImpl and then setting it on the ObjectMapper in the OAuth2AuthorizationService as described in this comment

[hannsl]

Technically the only thing that would need to be done is to use TYPES.BINARY instead of TYPES.BLOB. This is already possible for the save method, but the findBy methods don't provide a hook for us to substitute BINARY for BLOB. Please consider providing some form of hook/setting to allow us to to do so.

[sjohnr]

Hi @vladimir-cirkovic, thanks for your interest in the project. As @ovidiupopa91 mentioned, our current approach is to provide a standard sql implementation using JDBC, which is also consistent with the corresponding component in Spring Security. We will however take your feedback under advisement, so thanks for providing it.

You may also be interested in implementing this using Spring Data. See this gist if you're interested in trying that approach: JpaOAuth2AuthorizationService

I'm going to close this for now, but we will keep an eye on this issue as we move forward.

[santhosh1215]

Congratulations to the core team for bringing out the Authorization server out !!! Even when we try to change the BLOB to the other supported datatypes like BYTEA / varchar, since the type conversion was strict, its not allowing to use other BLOB equivalent data types and its better the framework uses ORM specifications (JPA) so that most of the databases are supported.
Request the team to please use JPA and not spring-jdbc which makes most of the developers life easy while development. Something like the below and make it more generic.

https://gist.github.com/sjohnr/463448fc00cf6059dd2892aed1e63d3c

Please provide some best practices on top of the releases!!!

Problem Statement:
@nullable
@OverRide
public OAuth2Authorization findByToken(String token, @nullable OAuth2TokenType tokenType) {
Assert.hasText(token, "token cannot be empty");
List parameters = new ArrayList<>();
if (tokenType == null) {
parameters.add(new SqlParameterValue(Types.VARCHAR, token));
parameters.add(new SqlParameterValue(Types.BLOB, token.getBytes(StandardCharsets.UTF_8)));
parameters.add(new SqlParameterValue(Types.BLOB, token.getBytes(StandardCharsets.UTF_8)));
parameters.add(new SqlParameterValue(Types.BLOB, token.getBytes(StandardCharsets.UTF_8)));
return findBy(UNKNOWN_TOKEN_TYPE_FILTER, parameters);
} else if (OAuth2ParameterNames.STATE.equals(tokenType.getValue())) {
parameters.add(new SqlParameterValue(Types.VARCHAR, token));
return findBy(STATE_FILTER, parameters);
} else if (OAuth2ParameterNames.CODE.equals(tokenType.getValue())) {
parameters.add(new SqlParameterValue(Types.BLOB, token.getBytes(StandardCharsets.UTF_8)));
return findBy(AUTHORIZATION_CODE_FILTER, parameters);
} else if (OAuth2TokenType.ACCESS_TOKEN.equals(tokenType)) {
parameters.add(new SqlParameterValue(Types.BLOB, token.getBytes(StandardCharsets.UTF_8)));
return findBy(ACCESS_TOKEN_FILTER, parameters);
} else if (OAuth2TokenType.REFRESH_TOKEN.equals(tokenType)) {
parameters.add(new SqlParameterValue(Types.BLOB, token.getBytes(StandardCharsets.UTF_8)));
return findBy(REFRESH_TOKEN_FILTER, parameters);
}
return null;
}
Highlighted Bold once is actually causing the cast exceptions even if we try to change the datatype at table level.

If we can have the Types as "VARCHAR", then it would be easy or use JPA instead of spring-jdbc as part of the framework since there are not much entities to configure!!!!
Thanks you

[jgrandja]

@santhosh1215 Please see gh-444