Hydra: Need way to housekeep records in DB

[22vincetsang]

There are lots of records in Hydra DB that get accumulated over time and are never cleaned up.
The flush API (as I tested with 1.0) can only clean up access tokens as stated in documentation, but not records in at least these tables:

• [ ] hydra_oauth2_authentication_request
• [ ] hydra_oauth2_authentication_request_handled
• [ ] hydra_oauth2_authentication_session
• [ ] hydra_oauth2_code
• [ ] hydra_oauth2_consent_request
• [ ] hydra_oauth2_consent_request_handled
• [ ] hydra_oauth2_pkce
• [ ] hydra_oauth2_refresh

We need at minimum an API to flush all obsolete records, if not an automatic housekeeping mechanism which I understand isn't in place today for access tokens neither.

[aeneasr]

Yes, so I checked and flush currently only clears up access tokens. Since this has not ever become an issue even in large deployments so far, I'm pushing the urgency back a bit.

[aeneasr]

PKCE is removed when it's being used, but it's still possible that old, dead entries are around if the authorize code is not used.

[22vincetsang]

Thanks, that's helpful - and I just verified the normal path won't create a record there. So it should be safe to clean up these pkce records then (still don't yet understand where they came from as the auth code should always be consumed immediately by our mobile app/spa... maybe there's some error case I'm not aware of...).

Looking forward to this enhancement so other DB tables can be handled too. It just seems not that trivial by looking at the other tables to identify which records are safe to delete so I dare not do so...

Thanks again!

[pereslava]

Hello. Any progress with it?
I was in milestone v1.3.0, I'm running v1.4.6 now and still no API.

As described here it is safe to cleanup the tables manually. But PR was not merged

[aeneasr]

Contributions are welcomed, I think the threads and comments and existing PRs shed light on what needs to be done!

[pereslava]

@aeneasr
Lets discuss what do you mean here:

All hydra_oauth2_(authentication|consent)_request that do not have a result for hydra_oauth2_(authentication|consent)_request_handled.challenge = hydra_oauth2_(authentication|consent)_request.challenge and where hydra_oauth2_(authentication|consent)_request.requested_at is older than a certain threshold.

That says to flush all not handled and obsolete requests.
Then you say:
All hydra_oauth2_(authentication|consent)_request where one result exists for hydra_oauth2_(authentication|consent)_request_handled.challenge = hydra_oauth2_(authentication|consent)_request.challenge and where hydra_oauth2_(authentication|consent)_request_handled.was_used=false and hydra_oauth2_(authentication|consent)_request.requested_at is older than a certain threshold.

I'm confused here the first statement will delete all records including was_used=false.
I guess the first statement is dangerous?

All hydra_oauth2_logout_request where accepted=false, rejected=false, was_used=false and (this field needs to be added) requested_at is older than a certain threshold.

That understood, deleting all not used and obsolete requests.

Also the logout request does not have ttl currently. Is this parameter shall be added, or ttl.login_consent_request can be used?

On other side what a reason to keep handled, used and obsolete requests? Is it removed at some flow?
Same for hydra_oauth2_(authentication|consent)_request_handled is it safe to flash those tables?

[aeneasr]

Keep in mind, this comment is from 2019. The code has changed since then!

Maybe let's start with that: What tables accumulate lots in your system? If we know those, we will know how to clean them.

[pereslava]

The tables i most care about:

• hydra_oauth2_(authentication|consent)_request
• hydra_oauth2_(authentication|consent)_request_handled
• hydra_oauth2_logout_request

Question is can we flush hydra_oauth2_(authentication|consent)_request_handled as well?

[aeneasr]

How much data has accumulated for those? Are they growing fast? And if so, how fast?

[pereslava]

@aeneasr
I'll check it again later, and hopefully will summarize the questions.

[purmac]

Hi @aeneasr,

We are facing the similar issue in our production database that

hydra_oauth2_(authentication|consent)_request
hydra_oauth2_(authentication|consent)_request_handled

tables grow quite quickly. At the rate of 9000 new records per day.
Is it safe to delete an arbitrary range said anything before 2020-01-01 in above tables if we need a quick purge to reduce the rows counts?

[aeneasr]

See #1987 @purmac

[purmac]

Hi @aeneasr

We are going to update to latest version of Hydra which can solve our long query problem. We still want to tidy up some tables to keep the size in a cost margin. As you mentioned above, the latest version had some code change. Is the clean up instruction in https://github.com/ory/hydra/issues/1574 still relevant or you could provide me an update instruction on how to safely clean up those table? We will only only do the simple clean up that remove records older than a certain time.

[aeneasr]

Clean up should now be more or less automatic for most flows. We also introduced better foreign key checks since then. There might still be stale data but it definitely shouldn't be too much. If you do find leaky tables in the latest version, please do report here :)

[purmac]

@aeneasr Let me follow up on that. The newer version of hydra has cleanup function build-in and we shouldn't worry about too much in the future. How about the existing aged data in the current table? Is the new version of hydra will clean the old data as well?

[aberasarte]

We are interested in this feature as well. The tables I'm seeing growing the most at the moment are hydra_oauth2_access and hydra_oauth2_refresh. We can cleanup the former using the flush endpoint but as the documentation states, this endpoint doesn't remove stale refresh tokens:

Refresh tokens can not be flushed as they are deleted automatically when performing the refresh flow.

When using the PKCE flow from a SPA, we store the refresh_token in memory for security reasons, so when the user closes the browser, the last received refresh_token isn't going to be used nor removed ever. In this closed PR @aeneasr said the following:

Removing refresh tokens will cause the refresh token chain to fail. It's important to keep track of all issued refresh tokens and access codes to be able to properly revoke them and their children later on. I believe this is true for OIDC as well.

I don't get this, once the refresh token has expired, it can't be used for refreshing any access_token; therefore, it should be safe to remove it, right? Could you clarify this?

[aeneasr]

I don't get this, once the refresh token has expired, it can't be used for refreshing any access_token; therefore, it should be safe to remove it, right? Could you clarify this?

From a technical standpoint yes, from a security and audit standpoint probably no!

When using the PKCE flow from a SPA, we store the refresh_token in memory for security reasons, so when the user closes the browser, the last received refresh_token isn't going to be used nor removed ever.

Yeah in that case those tokens really accumulate which makes sense why your tables are growing. Are any of those tokens expired or have you set an indefinite expiry?

If they have an expiry, the flush endpoint should get an option to allow you to remove those.

[aberasarte]

Thanks for your reply.

Are any of those tokens expired or have you set an indefinite expiry?

Yes, most of them are expired. Our refresh tokens are valid for 90 days.

[aeneasr]

Ok, in that case I think there should be an option in the flush endpoint to allow flushing the token tables as well!

By the way, which version are you using again?

[aberasarte]

By the way, which version are you using again?

1.6.0 in production. I've been using 1.7.0 for local tests as well.

One last question, it's somehow related to the cleaning issue but if you think that it is completely off-topic I'd ask it on the forum. I was thinking of revoking the access and refresh token every time a user logs out of the SPA or closes the browser. This way, we would avoid having a lot of unused tokens in the DB waiting to be expired and cleaned by the flush process. Do you think this is a good way of handling this kind of scenario?

[aeneasr]

Please check out: https://www.ory.sh/hydra/docs/concepts/before-oauth2#access-and-refresh-tokens-are-not-sessions

[aberasarte]

Please check out: https://www.ory.sh/hydra/docs/concepts/before-oauth2#access-and-refresh-tokens-are-not-sessions

I know that tokens are not sessions but I think it's fair (even recommendable) to revoke a token once you know it is not going to be used any longer.

[aeneasr]

You can revoke tokens using the OAuth2 Token Revocation API, which is supported in ORY Hydra!

[glerchundi]

From a technical standpoint yes, from a security and audit standpoint probably no!

IMO technically would be appropriate to do the housekeeping for refresh tokens if they're already expired. From a security standpoint I can't imagine how this could violate or reduce the security level in any way. Last but not least, audit could & should probably be solved in a different way like outputting the pruned refresh tokens (as they're already useless this is not kind of a problem!) to syslog/stdout like Vault does with audit devices.

WDYT?

[aeneasr]

Sorry for the late reply. You're obviously correct, auditing should be solved in a different way. We're still looking for a good idea how to do audit trails, do you know any system that could help?

[cburmeister]

We're also seeing the same behavior reported by @aberasarte where our hydra_oauth2_access and hydra_oauth2_refresh tables seem to be expanding at an abnormal rate. Here's the size of the tables in our MySQL database:

+----------+------------------------------------------------+------------+
| Database | Table                                          | Size in MB |
+----------+------------------------------------------------+------------+
| hydra    | hydra_oauth2_access                            |   18251.05 |
| hydra    | hydra_oauth2_refresh                           |   18106.61 |
| hydra    | hydra_oauth2_oidc                              |   17323.31 |
| hydra    | hydra_oauth2_code                              |   16122.09 |
| hydra    | hydra_oauth2_consent_request                   |    9094.28 |
| hydra    | hydra_oauth2_authentication_request            |    8942.58 |
| hydra    | hydra_oauth2_consent_request_handled           |    2748.58 |
| hydra    | hydra_oauth2_logout_request                    |    1465.50 |
| hydra    | hydra_oauth2_authentication_request_handled    |     712.34 |
| hydra    | hydra_oauth2_authentication_session            |     658.64 |
| hydra    | hydra_oauth2_pkce                              |     465.31 |
| hydra    | hydra_jwk                                      |       0.06 |
| hydra    | hydra_oauth2_obfuscated_authentication_session |       0.03 |
| hydra    | hydra_client                                   |       0.03 |
| hydra    | schema_migration                               |       0.02 |
| hydra    | hydra_oauth2_jti_blacklist                     |       0.02 |
+----------+------------------------------------------------+------------+
16 rows in set (0.02 sec)

This is for roughly about 5 million users, two web applications and two mobile applications over a two month period.

I'm going to try and flush expired access tokens but that would barely make a dent (as far as I understand) in terms of storage size. I'm concerned that the rate the database is growing is not sustainable.

Are there any sort of details about "large scale" deployments of Hydra? Is this unusual what we're seeing?

Edit: here's a graph of the database storage size:

[aeneasr]

I recommend setting a TTL on your refresh tokens. Also make sure that you don't have abusive clients which constantly issue new tokens!

[aeneasr]

Also be aware when flushing on a live system (if it is the first time) as - depending on DB - this might cause latency issues in the DB or even lock tables.

[cburmeister]

We're not setting TTL_REFRESH_TOKEN so the default TTL should be 720 hours (30 days).

Are you suggesting we lower this?

make sure that you don't have abusive clients which constantly issue new tokens

Could you expand on this some more? With TTL defaults, our largest web application is setting remember_for to 2 weeks for both login and consent sessions and using prompt=none for silent token refresh after the received id_token expires.

[cburmeister]

FWIW there are ~ 5 million rows in the hydra_oauth2_access table which makes sense, based on the size of our user base.

[aeneasr]

We're not setting TTL_REFRESH_TOKEN so the default TTL should be 720 hours (30 days).

That should be ok. My gut feeling says that you would free up definitely some tokens with hydra token flush if you never ran it before.

Could you expand on this some more?

Hard to say without any insights into your database or application. My guess was a client_credentials grant which is constantly being used.

[cburmeister]

What other information could I provide to help debug? All clients use authorization_code and refresh_token grant types.

[aeneasr]

I suggest to simply run hydra token flush and see what happens! Maybe load a backup into another mysql install and run it against that to help avoid any unexpected downtimes.

If that doesn't decrease DB size significantly it means that hydra is already pretty much cleaning up everything which in turn means that there isn't a lot you can do to fix the current DB size. Given that you have 5m actives it is expected to require some storage for all the session stuff. Unless the DB size is increasing exponentially over time I think you will be good.

We are planning to refactor the storage implementation even further and reduce the amount of tables required (e.g. remove duplication from refresh and access token session). Also remove duplication from login and consent requests. This will probably half the amount of storage, maybe even a bit more - we have to see.

[aeneasr]

As far as I can tell from the graph (not sure which graph shows what) it looks like the DB size is pretty much constant over the past 4 months?

[cburmeister]

Ah my apologies -- the blue line is total storage capacity and the purple line is free storage.

You can see we added 50GB on 11/19.

[aeneasr]

Ah I understand - that makes more sense now! Ok so my suggestions is what I wrote in the comment above. I'm happy to help you through the process and figure out any obstacles so that you can use ORY Hydra in your environment without issues. I'm sure we can figure out all storage issues :)

[cburmeister]

We appreciate the help @aeneasr !

I cloned our db into a separate RDS instance to test out the token flush command, will report back with the results.

[aeneasr]

Cool, let us know how it goes!

[aberasarte]

I recommend setting a TTL on your refresh tokens. Also make sure that you don't have abusive clients which constantly issue new tokens!

Even though they decrease the TTL for their refresh tokens, the flush is not going to remove the expired refresh tokens, right?
Is it ok to continue the work started in https://github.com/ory/hydra/pull/1639? I don't know if @genchilu is still interested in working on that, I could give it a go otherwise.

[aeneasr]

Even though they decrease the TTL for their refresh tokens, the flush is not going to remove the expired refresh tokens, right?

I don't understand? That is the exact purpose of these settings and commands!

[aberasarte]

According to the documentation:

_Refresh tokens can not be flushed as they are deleted automatically when performing the refresh flow._

That means that unused refresh tokens are going to be stored in the database forever. Am I missing something?

[aeneasr]

The information is not fully correct. If refresh tokens reach their TTL they will be removed from the database.

[maciej-kapusta]

Are you sure about the removal? I have the refresh token TTL set to 720h and a bunch of refresh tokens from April in the database.

Even when the tokens get removed a lot of other heavy records are kept indefinitely (requests, challenges and so on). It would be good to have setting showing how long we need to store all the authentication requests, consents etc.

[aeneasr]

Please provide more infos! Your version and also exemplary rows which you would expect to be deleted would be helpful to work on these things.

[maciej-kapusta]

I am running hydra v1.8.5 upgraded from v1.3.2. The upgrade was very smooth. My refresh token TTL is explicitly set to 720h. Access and id_tokens have 1h TTL, also explicitly stated in the config.

We mostly authenticate by with a username and password. One unique thing is, that since this is a test environment there are many unique subjects, with only one or two sessions. Basically we create fresh users for tests. We only have a limited set of clients.

The tables mentioned in the ticket seem to accumulate values indefinitely, and it would be nice to have some limit of how long we want the data to be kept.

Let me know if you need some particular stats about the setup.

[aeneasr]

Could you provide some exemplary rows (e.g. from the refresh token table) that you would expect to be deleted and why you would expect them to be deleted? That would be very helpful!

[maciej-kapusta]

I am not sure what format I should use. Hope it is readable. I do admit that our session data is bloated.

Here is a sample row from the hydra_oauth2_refresh table. I want it gone because it is older than 720 hours and I no longer have any use for it.

Basically in most of the tables have a column similar to requested_at and if it becomes too old I don't see any reason to store it, other than maybe some sort of audit.

signature,request_id,requested_at,client_id,scope,granted_scope,form_data,session_data,subject,active,requested_audience,granted_audience,challenge_id
vpSHGRs-j6qskaBq9dUrUm535-UZCHRKkSb2COa9qz0,af7d7f9e11404f69ab40913d570c3522,2020-05-31 14:43:23.774568,portal_client,openid|offline,openid|offline,"","{ "idToken": { "Claims": { "JTI": "", "Issuer": "https://auth.dev.thecompany.com/", "Subject": "qap-0/[email protected]", "Audience": null, "Nonce": "", "ExpiresAt": "0001-01-01T00:00:00Z", "IssuedAt": "2020-05-31T14:43:23.239473067Z", "RequestedAt": "2020-05-31T14:43:21.218338Z", "AuthTime": "2020-05-31T14:43:23.063813Z", "AccessTokenHash": "", "AuthenticationContextClassReference": "", "CodeHash": "", "Extra": { "sid": "931589b9-95cd-47bd-8953-90dfe015c9c6" } }, "Headers": { "Extra": { "kid": "public:c2318549-764a-4166-aa80-389b17d7a3b3" } }, "ExpiresAt": { "access_token": "2020-05-31T15:43:24Z", "authorize_code": "2020-05-31T14:53:23.239644368Z", "refresh_token": "2020-06-30T14:43:24Z" }, "Username": "", "Subject": "qap-0/[email protected]" }, "extra": { "authorities": [ "ROLE_FEATURE_1_CREATE", "ROLE_FEATURE_1_DELETE", "ROLE_FEATURE_1_READ", "ROLE_FEATURE_1_UPDATE", "ROLE_FEATURE_2_CREATE", "ROLE_FEATURE_2_DELETE", "ROLE_FEATURE_2_READ", "ROLE_FEATURE_2_UPDATE" ], "permissions": { "FEATURE_1": [ "CREATE", "READ", "UPDATE", "DELETE" ], "FEATURE_2": [ "CREATE", "READ", "UPDATE", "DELETE" ] }, "user": { "email": "[email protected]", "firstName": "Practice", "id": "0a64206a-7178-16e5-8171-789eaabc0b42", "language": "en", "lastName": "Regular user", "organization": { "businessName": "QA Practice", "country": "BRN", "name": "qap-0" } }, "user_name": "qap-0/[email protected]" }, "KID": "public:d9075098-531e-4514-b265-7dda604588c9", "ClientID": "portal_client", "ConsentChallenge": "af7d7f9e11404f69ab40913d570c3522" }",qap-0/[email protected],true,"","",af7d7f9e11404f69ab40913d570c3522

[aeneasr]

Thank you, this helps a ton! It looks like refresh tokens are indeed not cleaned up properly, only access tokens. Also removing old refresh tokens would be possible by adding the refresh token table here: https://github.com/ory/hydra/blob/master/persistence/sql/persister_oauth2.go#L355

Anyone up for the challenge? :)

[cburmeister]

Wanted to give an update -- we've manually removed all expired access tokens from the database (Saved us 18G of storage!) and now have the token flush command executing in a cronjob every hour. This is working perfect but we're still concerned about the size of the rest of the tables, here's a recent snapshot:

+------------------------------------------------------+-------+--------+-------+------------+---------+
| CONCAT(table_schema, '.', table_name)                | rows  | DATA   | idx   | total_size | idxfrac |
+------------------------------------------------------+-------+--------+-------+------------+---------+
| hydra.hydra_oauth2_oidc                              | 7.34M | 19.77G | 3.06G | 22.82G     |    0.15 |
| hydra.hydra_oauth2_refresh                           | 6.97M | 18.55G | 4.02G | 22.57G     |    0.22 |
| hydra.hydra_oauth2_code                              | 6.58M | 19.22G | 2.00G | 21.22G     |    0.10 |
| hydra.hydra_oauth2_consent_request                   | 7.95M | 6.53G  | 4.96G | 11.50G     |    0.76 |
| hydra.hydra_oauth2_authentication_request            | 9.40M | 7.51G  | 3.91G | 11.41G     |    0.52 |
| hydra.hydra_oauth2_consent_request_handled           | 7.62M | 3.24G  | 0.00G | 3.24G      |    0.00 |
| hydra.hydra_oauth2_logout_request                    | 0.44M | 1.59G  | 0.13G | 1.72G      |    0.08 |
| hydra.hydra_oauth2_authentication_request_handled    | 7.75M | 0.90G  | 0.00G | 0.90G      |    0.00 |
| hydra.hydra_oauth2_authentication_session            | 4.48M | 0.45G  | 0.32G | 0.77G      |    0.71 |
| hydra.hydra_oauth2_pkce                              | 0.19M | 0.54G  | 0.06G | 0.60G      |    0.11 |
| hydra.hydra_oauth2_access                            | 0.03M | 0.19G  | 0.05G | 0.24G      |    0.27 |
| hydra.hydra_jwk                                      | 0.00M | 0.00G  | 0.00G | 0.00G      |    0.33 |
| hydra.hydra_oauth2_obfuscated_authentication_session | 0.00M | 0.00G  | 0.00G | 0.00G      |    1.00 |
| hydra.hydra_client                                   | 0.00M | 0.00G  | 0.00G | 0.00G      |    1.00 |
| hydra.hydra_oauth2_jti_blacklist                     | 0.00M | 0.00G  | 0.00G | 0.00G      |    0.00 |
| hydra.schema_migration                               | 0.00M | 0.00G  | 0.00G | 0.00G      |    0.00 |
+------------------------------------------------------+-------+--------+-------+------------+---------+
16 rows in set (0.02 sec)

Is it safe to do the following?

• Remove rows from hydra.hydra_oauth2_oidc where requested_at < TTL_ID_TOKEN
• Remove rows from hydra.hydra_oauth2_refresh where requested_at < TTL_REFRESH_TOKEN
• Remove rows from hydra.hydra_oauth2_codewhere requested_at < TTL_AUTH_CODE
• Remove rows from hydra.hydra_oauth2_consent_request where requested_at < TTL_LOGIN_CONSENT_REQUEST
• Remove rows from hydra.hydra_oauth2_authentication_request where requested_at < TTL_LOGIN_CONSENT_REQUEST

[aeneasr]

Great to hear that you are making progress!

No it is not safe to do that. The TTL of login and consent requests need to stay in the database for as long as the user has an active login session or has granted the application access. Removing those will invalidate all the issued tokens linked to that login/consent request.

For refresh tokens I think this should be ok. For OIDC it is NOT ok to do that. Removing the code could potentially also invalidate all the other tokens, I have to check.

[cburmeister]

@aeneasr okay I'll go ahead and remove rows from hydra_oauth2_refresh that are older than our configured TTL, please let us know if we can clean up any rows in hydra_oauth2_code.

[maciej-kapusta]

Great to hear that you are making progress!

No it is not safe to do that. The TTL of login and consent requests need to stay in the database for as long as the user has an active login session or has granted the application access. Removing those will invalidate all the issued tokens linked to that login/consent request.

For refresh tokens I think this should be ok. For OIDC it is NOT ok to do that. Removing the code could potentially also invalidate all the other tokens, I have to check.

What about the following:
Remove rows from hydra.hydra_oauth2_oidc where requested_at < TTL_REFRESH_TOKEN
Remove rows from hydra.hydra_oauth2_refresh where requested_at < TTL_REFRESH_TOKEN
Remove rows from hydra.hydra_oauth2_codewhere requested_at < TTL_REFRESH_TOKEN
Remove rows from hydra.hydra_oauth2_consent_request where requested_at < TTL_REFRESH_TOKEN
Remove rows from hydra.hydra_oauth2_authentication_request where requested_at < TTL_REFRESH_TOKEN

Basically what I am asking is what is the latest date that we can safely remove the entries from these tables?

[aeneasr]

No, these queries will break several flows for your users! We will have to be a bit smarter about it - e.g. delete consent requests when new ones are issued or something along those lines!

[maciej-kapusta]

But once the refresh token is expired, what can they do beside starting a fresh login flow? Is there anything read from the database when a fresh login flow starts?

[aeneasr]

Yes, we keep granted consent and ongoing auth sessions in the store. Also, requested_at is not updated when you use a refresh token (which issues a new refresh token with the TTL_REFRESH_TOKEN set).

[maciej-kapusta]

Let's say that we don't use consent. What is the worst case scenario when I deleted all the data that is older than the refresh token which is also expired?

[aeneasr]

Tokens that are valid are invalidated and users need to perform login + consent again to re-auth the applications. This will break apps that rely on refresh token flows and can lead to significant blowback from your users.

[maciej-kapusta]

But aren't all the tokens with requested_at earlier than their now - TTL already expired?

[LAMASE]

With few million users per day, the number of records can be a problem for me.
We started the Oauth server one year ago for a really small portion of our users, and we found that almost all the records we have today can probably be deleted (old authentication_requests, never handled).
I'm following the discussion.