Doorkeeper: How to keep database clean

[chumakoff]

Should I do something with access_token table that grows infinitely ? Is there a way to automatically remove all expired or revoked tokens ?

[tute]

Doorkeeper keeps revoked tokens, you can delete them with a query like:

DELETE FROM oauth_access_tokens WHERE revoked_at IS NOT NULL AND revoked_at < NOW();

[jfcalvo]

I think that Doorkeeper should add a rake task for this.

[francesle]

Similarly for oauth_access_grants, can we safely delete revoked or expired access grants?

[jfcalvo]

I think that this issue should be reopened. Doorkeeper should implement some way to maintain the database clean (rake tasks or some other options) it's not very nice to know that you database will increase ad infinitum.

[tute]

I won't work on this (I've never seen it being a problem, and I believe YAGNI), but I'm not opposed to a simple rake task that clears the unneeded objects from the DB. I would merge it in.

[rossinkiwi]

I got around the problem of an endlessly growing _oauth_access_grants_ table by over-riding a method in _lib/doorkeeper/oauth/authorization_code_request.rb_. Here's what I did:

1. Create a file _config/initializers/doorkeeper_delete_grants.rb_
2. Insert the following:

Doorkeeper::OAuth::AuthorizationCodeRequest.module_eval do
# Override this method so that oauth_access_grants table does not grow continuously.
# Original behavior was to retain all the revoked oauth_access_grants, they now 
# get deleted after revoking.

    private

    def before_successful_response
      grant.revoke
      gapp = grant.application
      groid = grant.resource_owner_id
      gscopes = grant.scopes
      Doorkeeper::AccessGrant.find(grant.id).destroy
      #find_or_create_access_token(grant.application,
      #                            grant.resource_owner_id,
      #                            grant.scopes,
      #                            server)
      find_or_create_access_token(gapp,
                                  groid,
                                  gscopes,
                                  server)
    end
  end

[Charizard]

:+1: for a rake task.

[Ashkin]

+1 for a rake task.

Specific use-case: I regularly copy my production database over to staging for testing with real-world data. As my staging database has a limit of 10 million rows I need a way to reduce row count, and the biggest culprit of excess data is Doorkeeper.

If there was an official rake task, I wouldn't need to write my own and hope I didn't overlook something.

As a secondary benefit, it would take a good deal less time to perform my daily/weekly backups and the staging imports without this excess data, as the oauth tables account for 25%+ of the total import time.

[tute]

Specific use-case: I regularly copy my production database over to staging for testing with real-world data. As my staging database has a limit of 10 million rows I need a way to reduce row count, and the biggest culprit of excess data is Doorkeeper.

This is a use case that as a library maintainer I wish to discourage. Production credentials should live only in production.

If there was an official rake task, I wouldn't need to write my own and hope I didn't overlook something.

I think you are wise for preferring official code that you don't need to maintain. This task can be solved as a one-liner, and I already wrote it down for everyone who needs it in https://github.com/doorkeeper-gem/doorkeeper/issues/536#issuecomment-67897899.

As a secondary benefit, it would take a good deal less time to perform my daily/weekly backups and the staging imports without this excess data, as the oauth tables account for 25%+ of the total import time.

It sounds like you would benefit a lot from that one liner. I'd put it in a cron job if that were happening in my applications.

[Ashkin]

This is a use case that as a library maintainer I wish to discourage. Production credentials should live only in production.

I agree.

This task can be solved as a one-liner, and I already wrote it down for everyone who needs it in #536 (comment).

The one-liner referenced above only removes revoked access tokens; what about access grants? and for both of them: what about non-revoked records whose created date + expiry is in the past? I would like to get a maintainer's input before removing these, but (audit usage aside) it seems none of these records need to be kept. Deleting these would also reduce my database size by roughly 56% (~5.55 million records at present), so I am understandably interested in doing so.