OAuth2 Client - JdbcOAuth2AuthorizedClientService - Issue with PostgreSQL

[joshdcollins]

Describe the bug
I receive an exception from the Postgres driver due to type mismatches when using the default functionality of the JdbcOauth2AuthorizedClientService.

To Reproduce

1. Point Springboot to a Postgres database - in my case it is a 12.2 database using the driver 42.2.12.
2. Create the oauth2_authorized_client as per de8b558#diff-b1664e05db02c677aa71d089dea1168b -- note that Postgres no longer supports the BLOB datatype. As such, I adapted the schema from BLOB to BYTEA in Postgres.
3. Configure the OAuth2-Client
4. Note an exception from setObject(int parameterIndex, Object in, int targetSqlType, int scale) in PgPreparedStatement - line 650. The logic from lines 643-650, when a Sql.Types.BLOB is provided, Postgres expects the value to be either a java.sql.Blob or an InputStream.

Expected behavior
JdbcOAuth2AuthorizedClientService works with Postgres.

** Other Notes **
I was able to work around this by using setAuthorizedClientParametersMapper and providing my own implementation that used Types.BINARY instead of Types.BLOB. But I did not test this on other RDBMS for broader compatibility.

[jgrandja]

@joshdcollins Is the BYTEA specific to PostgreSQL? This is the first time I've heard of this datatype.

FYI, 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.

I was able to work around this by using setAuthorizedClientParametersMapper and providing my own implementation that used Types.BINARY instead of Types.BLOB

Take a look at JdbcOAuth2AuthorizedClientServiceTests.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.

If the BYTEA datatype is non-standard, then we will likely not provide any changes here since you can customize the implementation to make it work.

[joshdcollins]

Thanks @jgrandja I'm in alignment with closing and supporting as-is

[markhobson]

In case this helps anyone in future: once you've updated the schema, the following custom JDBC parameters mapper works for PostgreSQL:

public class PostgreSqlOAuth2AuthorizedClientParametersMapper extends OAuth2AuthorizedClientParametersMapper {
  @Override
  public List<SqlParameterValue> apply(OAuth2AuthorizedClientHolder authorizedClientHolder) {
    return super.apply(authorizedClientHolder).stream()
      .map(parameter -> parameter.getSqlType() == Types.BLOB
        ? new SqlParameterValue(Types.BINARY, parameter.getValue()) : parameter)
      .collect(toList());
  }
}

Set it on your OAuth2AuthorizedClientService bean as follows:

@Bean
public OAuth2AuthorizedClientService oauth2AuthorizedClientService(JdbcOperations jdbcOperations,
  ClientRegistrationRepository clientRegistrationRepository) {
  JdbcOAuth2AuthorizedClientService authorizedClientService = new JdbcOAuth2AuthorizedClientService(
    jdbcOperations, clientRegistrationRepository);
  authorizedClientService.setAuthorizedClientParametersMapper(oauth2AuthorizedClientParametersMapper());
  return authorizedClientService;
}

public Function<OAuth2AuthorizedClientHolder, List<SqlParameterValue>> oauth2AuthorizedClientParametersMapper() {
  return new PostgreSqlOAuth2AuthorizedClientParametersMapper();
}

[santhosh1215]

Postgres has alternatives if not using a BLOB and its better the framework uses ORM specifications (JPA) so that most of the databases are supported.

[oobukhov-intellective]

there's one more related issue - timestamps are saved as NON UTC, so that clients from different timezones faced expired tokens (however jwt token itself contained right timestamp) ;
So we fixed it this about year ago and it has been working perfectly:

public class PostgreSqlOAuth2AuthorizedClientParametersMapper extends JdbcOAuth2AuthorizedClientService.OAuth2AuthorizedClientParametersMapper {
    @Override
    public List<SqlParameterValue> apply(JdbcOAuth2AuthorizedClientService.OAuth2AuthorizedClientHolder holder) {
        return super.apply(holder).stream()
                .map(parameter -> {
                    if (parameter.getSqlType() == Types.BLOB) {
                        return new SqlParameterValue(Types.BINARY, parameter.getValue());
                    } else if (parameter.getSqlType() == Types.TIMESTAMP) {
                        // saving as UTC stamp!
                        Object value = parameter.getValue();
                        Timestamp timestampValue = (Timestamp) value;
                        if (timestampValue != null) {
                            Instant instant = timestampValue.toInstant();
                            LocalDateTime dateTimeUtc = LocalDateTime.ofInstant(instant, ZoneOffset.UTC);
                            Timestamp timestampUtc = Timestamp.valueOf(dateTimeUtc);
                            return new SqlParameterValue(Types.TIMESTAMP, timestampUtc);
                        }
                    }
                    return parameter;
                })
                .collect(Collectors.toList());
    }
}

and another one:

public class UtcOAuth2AuthorizedClientRowMapper
        extends JdbcOAuth2AuthorizedClientService.OAuth2AuthorizedClientRowMapper {

    public UtcOAuth2AuthorizedClientRowMapper(ClientRegistrationRepository clientRegistrationRepository) {
        super(clientRegistrationRepository);
    }

    @Override
    public OAuth2AuthorizedClient mapRow(ResultSet rs, int i) throws SQLException {
        ResultSet rsWrapper = (ResultSet) Proxy.newProxyInstance(
                getClass().getClassLoader(),
                new Class[]{ResultSet.class},
                (proxy, method, args) -> {
                    if (method.getName().equals("getTimestamp") && args.length == 1) {
                        // getting as UTC stamp!
                        Calendar cal = Calendar.getInstance();
                        cal.setTimeZone(TimeZone.getTimeZone("UTC"));
                        Object theOnlyArg = args[0];
                        if (theOnlyArg.getClass() == String.class) {
                            return rs.getTimestamp((String) theOnlyArg, cal);
                        } else if (theOnlyArg.getClass() == Integer.class) {
                            return rs.getTimestamp((Integer) theOnlyArg, cal);
                        }
                    }
                    return method.invoke(rs, args);
                });
        return super.mapRow(rsWrapper, i);
    }
}

all together:

    @Bean
    OAuth2AuthorizedClientService authorizedClientService(DataSource dataSource,
                                                          ClientRegistrationRepository clientRegRepo) {
        logger.info(" - JDBC: creating JdbcOAuth2AuthorizedClientService");
        JdbcTemplate jdbcOps = new JdbcTemplate(dataSource);
        JdbcOAuth2AuthorizedClientService clientService = new JdbcOAuth2AuthorizedClientService(jdbcOps, clientRegRepo);
        clientService.setAuthorizedClientParametersMapper(new PostgreSqlOAuth2AuthorizedClientParametersMapper());
        clientService.setAuthorizedClientRowMapper(new UtcOAuth2AuthorizedClientRowMapper(clientRegRepo));
        return clientService;
    }

[Kunpero]

Hi, @jgrandja. I noticed that the problem with the blob type for different JDBC drivers is quite a common issue. Maybe it would be a good idea to add a constructor with blobType alongside the default one, to make types of accessToken and refreshToken dynamic. I can implement a solution in a pull request.