JdbcOAuth2AuthorizationService should support clob and text datatype for token columns

[ghost]

Few issues were logged for JdbcOAuth2AuthorizationService as it does not work out of the box with PostgreSQL. Although customization is possible, we should change the schema from blob to varchar OR another standard SQL datatype that will work by default with PostgreSQL and most other databases

[muthu3107]

👍🏻

Kindly address this issue, have tries customizing it, but not able to get the result as expected, tried with all suggested solutions.

Changing from blob to VARCHAR would make up for most standard sql databases.

[ghost]

hi @jgrandja. Would you prefer to not use the LobHandler at all? I think we should still use it and write the values using setClobAsString and getClobAsString to read the data. If wrapasLob is false (which is by default) it will delegate to getString and setString methods (from PreparedStatement). If it's set to true, it will try to set a clob.

In this way, the schema can be changed to use something else than varchar if needed.

Draft PR: 491

[jgrandja]

@ovidiupopa91 It doesn't look like Postgres supports CLOB? If wrapasLob is false, will it work for Postgres?

I don't mind keeping LobHandler, I just want to ensure the solution works for Postgres by default.

[ghost]

@jgrandja I did check and it works by default with Postgress when token values are defined as varchar(1000) in the oauth2-authorization-schema.
The reason why I think it's a good idea to keep the LobHandler is that it will make life easier in extending the JdbcOauth2AuthozarionService if a DB that supports clob is used.
The only thing that needs to be done is to set wrapAsLob to true and it will try to insert a clob

[ghost]

By the way, I also noticed that the registration_access_token cannot be deserialized when calling the client configuration endpoint, because the client.read scope is stored in a SingletonSet. And there's no mixin for that.

Should I open a new issue for this or should I do it within this issue / PR?

[jgrandja]

@ovidiupopa91

the registration_access_token cannot be deserialized when calling the client configuration endpoint

Please open a new issue for this fix. Thanks for catching that!

Just a heads up that I'm on PTO Nov 19-24 (inclusive) and have a couple of priority issues to resolve this week. I'll try to review this week but it may be when I return from PTO.

[ghost]

495 raised to track the issue @jgrandja.
I will open a PR to fix it.

[Blackmail92]

A small question.. You say that it should work out of the box, but when I use the jdbc client repo and authorization service, I get an exception telling that a table is not present, and I need to create that first manually.. Should I define some missing property to force the app to create the tables automatically?