Token Cleanup with Postgres 9.4 in WSO2 Identity Server

Please find the quick steps below to clean up tokens which are stored in a postgres 9.4.10 Identity database.

1. Clone the repository https://github.com/wso2/carbon-identity-framework.git

git clone https://github.com/wso2/carbon-identity-framework.git

If you want to check against a wum pack clone it from the below link

git clone https://github.com/wso2-support/carbon-identity-framework.git

2. Checkout to the necessary branch

git checkout support-5.17.5

3. Navigate to the below location

wso2/carbon-identity-framework

4. If the database which has the table idn_oauth2_access_token is created under a postgres db schema (which is not public ) you need to add the below line 42 (below the BEGIN section ) to the .sql script pointing for your schema.

SET search_path TO myschema;

myschema should be the name of your schema
SET search_path TO wso2ci_qa2;

(step 4 is not necessary if you haven't created any schemas and all your tables in the postgres db are under default public schema)

5. Now connect to the database from db viewer

6. From the drop down select the schema as wso2ci_qa2 before executing the .sql script

7. Copy and paste the sql script with the new line added and execute it.

8. Now get the below script to your local machine

wso2/carbon-identity-framework

Change the below parameters according to your connecting db details and save the shell file.

username=’postgres’
password=’wso2carbon’
database=’testdb’
host=’localhost’
schema=’wso2ci_qa2'

9. check its permission

ls -l

change the permission as below

chmod 777 token-cleanup-runner.sh

ls -l

OUTPUT

• rw-rw-r — 1 shanika shanika 4797 21 15:55 postgresql-tokencleanup-restore.sql
  -rwxrwxrwx 1 shanika shanika 15770 24 14:28 postgresql-token-cleanup.sql
  -rwxrwxrwx 1 shanika shanika 2135 24 13:24 token-cleanup-runner.sh

10. Now execute the script with belwo command

./token-cleanup-runner.sh

OUTPUT

NOTICE: WSO2_TOKEN_CLEANUP_SP STARTED …. !
NOTICE:

NOTICE: CALCULATING PURGING RECORDS ON TABLE idn_oauth2_access_token…. !
NOTICE: TOTAL RECODES ON idn_oauth2_access_token TABLE BEFORE DELETE : 184
NOTICE: TOTAL RECODES ON idn_oauth2_access_token SHOULD BE DELETED : 0
NOTICE: TOTAL RECODES ON idn_oauth2_access_token SHOULD BE RETAIN : 184

NOTICE: SLEEPING …
NOTICE: PROCEED WITH NEW CHUNK TABLE idn_oauth2_access_token_chnuk
NOTICE: idn_oauth2_access_token DELETE FINISHED HENCE NEW CHUNK CREATED WITH 0 AND ITS LESS THAN THE CHECK COUNT DEFINED : 500

NOTICE: CALCULATING PURGING RECORDS ON TABLE idn_oauth2_authorization_code…. !
NOTICE: TOTAL RECODES ON idn_oauth2_authorization_code TABLE BEFORE DELETE : 1359
NOTICE: TOTAL RECODES ON idn_oauth2_authorization_code SHOULD BE DELETED : 1306
NOTICE: TOTAL RECODES ON idn_oauth2_authorization_code SHOULD BE RETAIN : 53

NOTICE: SLEEPING …
NOTICE: PROCEED WITH NEW CHUNK TABLE idn_oauth2_authorization_code_chnuk
NOTICE: CHUNK TABLE idn_oauth2_authorization_code_chnuk CREATED WITH :1306
NOTICE: BATCH DELETE START ON TABLE idn_oauth2_authorization_code WITH :1306
NOTICE: BATCH DELETE FINISHED ON TABLEidn_oauth2_authorization_code WITH :1306
NOTICE: DELETED BATCH ON TABLE idn_oauth2_authorization_code_chnuk WITH :1306

NOTICE: SLEEPING …
NOTICE: DROPPING TABLE idn_oauth2_authorization_code_chnuk HENCE COUNT IS : 0
NOTICE: PROCEED WITH NEW CHUNK TABLE idn_oauth2_authorization_code_chnuk
NOTICE: idn_oauth2_authorization_code DELETE FINISHED HENCE NEW CHUNK CREATED WITH 0 AND ITS LESS THAN THE CHECK COUNT DEFINED : 500

WSO2 TOKEN_CLEANUP SCRIPT EXECUTION COMPLETED !