JDBCTokenStore sometimes stores "too many" access tokens

[a.e]

Hi,

we have deployed an API of ours that uses spring security oauth 1.0.0RC2 and it's JDBCTokenStore. This API is used by various clients (some via spring security oauth's OAuth2Resttemplate, others via other HTTPClients like the .NET one).

Regardless of what client uses the provided Token Endpoint somehow sooner or later the situation arises where there are multiple access tokens in the database for the same user (or the same client when client-identifier scheme is used). This leads to random Error 500 codes and our main line of action is to manually delete all access tokens and request tokens from the database.

Is this a known bug or do we somehow miss-use the token store here?

2012-10-08 08:48:12.400::WARN:  /oauth/token
org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 1, actual 4
        at org.springframework.dao.support.DataAccessUtils.requiredSingleResult(DataAccessUtils.java:74)
        at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:735)
        at org.springframework.security.oauth2.provider.token.JdbcTokenStore.getAccessToken(JdbcTokenStore.java:99)
        at org.springframework.security.oauth2.provider.token.DefaultTokenServices.createAccessToken(DefaultTokenServices.java:78)
        at org.springframework.security.oauth2.provider.token.AbstractTokenGranter.getAccessToken(AbstractTokenGranter.java:68)
        at org.springframework.security.oauth2.provider.token.AbstractTokenGranter.grant(AbstractTokenGranter.java:60)
        at org.springframework.security.oauth2.provider.CompositeTokenGranter.grant(CompositeTokenGranter.java:38)
        at org.springframework.security.oauth2.provider.endpoint.TokenEndpoint.getAccessToken(TokenEndpoint.java:93)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
        at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
        at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:502)
        at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
        at org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter.doFilterInternal(OpenEntityManagerInViewFilter.java:147)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
        at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1148)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:322)
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:116)
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
        at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
        at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
        at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
        at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
        at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:184)
        at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:155)
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
        at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1148)
        at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:387)
        at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
        at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
        at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
        at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:417)
        at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:230)
        at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
        at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
        at org.mortbay.jetty.Server.handle(Server.java:326)
        at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:534)
        at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:879)
        at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:747)
        at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)
        at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
        at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)
        at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:520)

On a side-note: Are there SQL-Scripts available to create the data scheme that is assumed by the token store? I manually analysed the default SQL-Statements and just created two tables that hold all the used columns but there are no keys or other constraints in use.

[Dave Syer]

I haven't actually used the JdbcTokenStore much, but I never saw that error before. I guess there might be a race condition? You could protect against it (at the cost of an error in a different place) by adding a uniqueness constraint in your database of course.

The unit tests have SQL scripts for creating the tables. You can pull those out of the source code, but they are not in published jars.

[a.e]

If i get this right this means that JDBCTokenStore does not care for synchronization. If that's the case what guarantees are made by spring security oauth regarding synchronization in the token stores? I guess to get this right someone would have to synchronize in the database. I did such things in the past by use of statements like

select ... for update

.

Can you tell me how the logical flow is that uses the TokenStore so that I may be able to try an synchronizing implementation?

[Dave Syer]

The only client of the TokenStore is the DefaultTokenServices. I guess it might be enough to declare the DefaultTokenServices to be transactional with a high isolation level - that would provide the database lock, and I think its interface ensures that concurrent accessors would just wait a bit longer and get the existing token.

[a.e]

If I understand this right Spring transactions can result in database locks? This would be nice. As to your suggestion I came up with this XML-Directives in the affected application context. Is that correct? Will it work?

<bean id="oauthTXManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="oauthDataSource"/>
    </bean>

    <tx:advice id="oauthTXAdvice" transaction-manager="oauthTXManager">
        <tx:attributes>
            <tx:method name="*" isolation="SERIALIZABLE"/>
        </tx:attributes>
    </tx:advice>

    <aop:config>
        <aop:pointcut id="oauthTokenServiceOperations"
                      expression="execution(* org.springframework.security.oauth2.provider.token.DefaultTokenServices.*(..))"/>
        <aop:advisor advice-ref="oauthTXAdvice" pointcut-ref="oauthTokenServiceOperations"/>
    </aop:config>

    <bean id="oauthTokenServices" class="org.springframework.security.oauth2.provider.token.DefaultTokenServices">
        <property name="tokenStore" ref="jdbcTokenStore"/>
        <property name="supportRefreshToken" value="true"/>
    </bean>

    <bean id="jdbcTokenStore" class="org.springframework.security.oauth2.provider.token.JdbcTokenStore">
        <constructor-arg ref="oauthDataSource"/>
    </bean>

[Dave Syer]

Yes, that should do it. Whether or not you get a lock depends on the RDBMS and driver (i.e. nothing to do with Spring).