JdbcOAuth2AuthorizationService in postgresql cannot casting type `blob`

[phuongdpham]

Caused by: org.postgresql.util.PSQLException: Cannot cast an instance of [B to type Types.BLOB

When I set datasource to Postgres, then

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.authorizationRowMapper); return !result.isEmpty() ? result.get(0) : null; }
throw exeption abount casting Types.BLOB to bytea in postgres table oauth2_authorization column access_token_value

Thanks,

[xialugui]

There is no type BLOB in Postgres,try bytea:

[phuongdpham]

Yep, I declared type of that column is bytea but in code of JdbcOAuth2AuthorizationService method public OAuth2Authorization findByToken(String token, @Nullable OAuth2TokenType tokenType) are bind type java.sql.Types.BLOB to column ACCESS_TOKEN. Then, the jdbc produce exception above.

[sjohnr]

@phuongdpham, as mentioned in this 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.

Take a look at JdbcOAuth2AuthorizationServiceTests.tableDefinitionWhenCustomThenAbleToOverride(), which provides a test on how to override the table definition altogether. There are tests in that class that demonstrate how to customize further.

You may also find this comment helpful, as the same technique would apply to the OAuth2AuthorizationParametersMapper.

Lastly, you may be interested in this gist which implements a JpaOAuth2AuthorizationService.

I'm going to close this for now, but if you are still having issues after you have tried out the proposed solutions we can re-open and discuss further.

[phuongdpham]

Oh nice, thanks @sjohnr and @xialugui.

I'm very interesting about OAuth2 but rarely example in github. Can you @sjohnr give me a completed simple example about OAuth2 authorization server, resource server and client? Thank you so much.

[sjohnr]

Hi @phuongdpham. We have OAuth 2.0 samples in the spring-security-samples repo. Additionally, some team members presented at SpringOne this year and produced a full sample with all three roles represented. There's a link to a video on that page as well.

Hope that helps!

[phuongdpham]

Hi @sjohnr, thank you so much. That really helps me a lot 👯