OAuth Database Performance Tuning

This article is relevant for larger installations of Airlock IAM that use OAuth 2.0 or OIDC extensively and therefore have large tables with access and refresh tokens.

To improve performance in such a situation, this article explains how all OAuth 2.0 and OIDC tokens can be stored in a separate database table to profit from database query optimization.

Prerequisites

The instructions provided here are entirely optional. They have been prepared for version 8.1 of Airlock IAM. To use them, it is expected that a fully configured OAuth 2.0 authorization server or an OIDC Provider is already configured.

Note that some tasks require the knowledge and authorization of a database administrator.

Database configuration

The first step is to extend the database schema with new tables where all OAuth 2.0 and OIDC tokens will be stored.

  1. Create a duplicate of the database table token under a new name, e.g. oauth2_token.
  2. Create duplicates of all indices of the database table token for the new table.
  3. Create a duplicate of the database table token_assignment under a new name, e.g. oauth2_token_assignment.
  4. Create duplicates of all indices of the database table token for the new table.

IAM Configuration

These steps are to update all OAuth 2.0 and OIDC plugins with the new table names.

  1. Configuration of the Adminapp module
  2. Go to:
    Adminapp >> Users >> OAuth 2.0 Authorization Server
  3. For each authorization server in the Users OAuth 2.0 Authorization Server plugin:
    • Edit the Session Respository plugin
    • Change the Token Table Name to the new database table name, e.g. oauth2_token.
    • Change the Token Assignment Table Name to the new database table name, e.g. oauth2_token_assignment.
  1. Configuration of the Loginapp module
  2. Go to:
    Loginapp >> OAuth 2.0/OIDC Authorization Servers
  3. For the first authorization server edit the OAuth 2.0/OIDC Authorization Server type plugin.
  4. In property Token Data Provider create and edit a plugin of type Default Token Data Provider (e.g., with Identifier OAuth2 Token Data Provider).
    • In property Token Persister create and edit a plugin of type Database Token Persister. You may use the existing Database Token Persister as Parent configuration.
    • In property Token Table change the name to the OAuth token table, e.g., oauth2_token.
    • In property Token Assignement Table change the name to the OAuth token table, e.g., oauth2_token_assignment.
  5. In property Session Repository connect the OAuth 2.0 Session Repository plugin created during the Adminapp configuration.
  6. For each authorization server edit the OAuth 2.0/OIDC Authorization Server type plugin
      • In property Token Persister connect the Database Token Persister plugin created in Step 3 (e.g. with Identifier OAuth2 Token Data Provider)
      • In property Session Repository connect the OAuth 2.0 Session Repository plugin created during the Adminapp configuration.
  1. Token Consistency Change Listener
  2. Go to e.g.
    Adminapp >> Users >> User Data Source >> User Store >> Database User Persister
  3. In property User Change Event Listeners create and edit a plugin of type Token Consistency User Change Listener.
  4. In property Token Data Provider connect the previously created Database Token Persister (e.g., with Identifier OAuth2 Token Data Provider).
  5. In property Token Services create and edit a plugin of type Generic Token Service.
  6. In property Token Data Provider connect the previously created Database Token Persister (e.g., with Identifier OAuth2 Token Data Provider).
  7. In property Types to Handle make sure OAUTH2 is listed.
  8. Configure all remaining properties as required by your installation.
  1. Configuration Service Container
  2. Go to:
    Service Container >> Task Scheduler Config >> Service >> Tasks >> Task Schedule >> OAuth 2.0 Clean-up Task
  3. Change the Token Table Name to the new database table name, e.g., oauth2_token.
  4. Change the Token Assignment Table Name to the new database table name, e.g., oauth2_token_assignment.

Further Optimization

This setup has been designed to separate OAuth 2.0 and OIDC tokens from all other tokens. This allows database engines to optimize their query plans better.

An additional step for optimization is to review the indices on the two database tables and to remove unused indices. This will improve insert operations and optimize the overall database performance.