Introduction

When you use WSO2 Identity Server with OAuth functionalities, issued access tokens are likely to be accumulated in the database, due to expiration, inactivation or revocation. In order to address this issue, the server ships with token clean-up methods, that allows you to keep your deployment free from growing token tables and decreasing performance on token flows due to data growth.

There are two main methods we can use for token cleanup as,

1. Token cleanup feature.
   • When a new token request comes or a revocation request comes, matching old token will be removed from the database.
2. Token cleanup stored procedure.
   • Instead of using the feature, you can manually set up a stored procedure to clean the tokens.

In production environments, we usually recommend using the stored procedure, as the token clean up feature might take a little bit of performance, as it cleans the token on token requests. With a stored procedure, you can schedule it to run on a non-peek time which will not affect the server performance at all. This particular article explains how you can setup token cleanup in AWS RDS database.

Steps

1. I’d advise you to make a copy of your current WSO2 IS database and try these steps first just to be safe.
2. Select the matching token cleanup script according to your database, from here. I use mysql-tokencleanup.sql, as my database is MySQL.
3. Get the script into an SQL file and edit the first line with your database name.

   • USE YOUR_DB_NAME; 

4. There are other parameters as well that you can tweak according to your requirements.
5. Now feed this SQL into your RDS database my login. For MySQL, I can use

   • source /home/ubuntu/token-cleanup.sql

6. Now we can try to run it first to make sure the procedure works. for MySQL, the command is,

   • call WSO2_TOKEN_CLEANUP_SP();

7. When the stored procedure runs, you’ll be able to see following some logs on your DB client
8. Next step is scheduling this stored procedure to run, maybe once a day, depending on your requirements. This is where we need some adjustments as when we’re using AWS RDS. We need superuser privileges to schedule an event in most databases including MySQL and AWS RDS usually doesn’t provide that.
9. What we can do is, edit the following parameters in the RDS parameter group so that it allows us to schedule events without root privileges. To do that, log into your AWS console and navigate to RDS section.
10. Select your database from the list and click on the Configurations tab.
11. Scroll down and you’ll see Parameter group listed there. Click on that. If you use a default DB Parameter Group, we won’t be able to edit that. In that case, you can copy the default parameter group and create your own. Read this for more information.
12. Once you go into DB Parameter Group page, you can search for parameters using the search option.
13. Click on Edit Parameters button to edit the values. Then edit the following parameters.
    • event_scheduler – ON
    • log_bin_trust_function_creators – 1
14.  Click Save Changes.
15. Now go back to your RDS configurations and you’ll see the DB Parameter Group is in applying state. It’ll take a couple of minutes to apply. Once applied, it’ll change to In-sync state.
16. Now you should be able to schedule the stored procedure. Following is a sample scheduler for MySQL, which runs the procedure for once per day, starting from no

USE `IDENTITY_DB`;
DROP EVENT IF EXISTS `cleanup_tokens_event`;
CREATE EVENT `cleanup_tokens_event`
    ON SCHEDULE
        EVERY 1 DAY
        STARTS CURRENT_TIMESTAMP
    ON COMPLETION 
        PRESERVE
    DO
        CALL `WSO2_TOKEN_CLEANUP_SP`();

Hope this helps! Let me know if you have any questions.

Cheers!