oc_authtoken table grows by ~100MB/day without bound

[LHurst-UoB]

Steps to reproduce

1. Install NextCloud
2. Use it with a variety of clients
3. Note database grows in size - examine origin of growth and see oc_authtoken is the culpret.

Expected behaviour

Tell us what should happen

NextCloud should curate the database table itself out-of-the-box.

Actual behaviour

Tell us what happens instead

Database grows continuously in size until administrator manually deletes records directly from the database or filesystem runs out of space and database falls over.

Server configuration

Operating system:

Debian 10.2

Web server:

nginx 1.14.2

Database:

Postgres 11

PHP version:

PHP 7.3

Nextcloud version: (see Nextcloud admin page)

15

Updated from an older Nextcloud/ownCloud or fresh install:

Updated from 14

Where did you install Nextcloud from:

https://download.nextcloud.com/server/releases/latest-15.tar.bz2

Signing status:

Signing status

Login as admin user into your Nextcloud and access 
http://example.com/index.php/settings/integrity/failed 
paste the results here.

No errors have been found.

List of activated apps:

App list

If you have access to your command line run e.g.:
sudo -u www-data php occ app:list
from within your Nextcloud installation folder

$ php occ app:list
Enabled:

• accessibility: 1.1.0
• activity: 2.8.2
• cloud_federation_api: 0.1.0
• comments: 1.5.0
• dav: 1.8.1
• federatedfilesharing: 1.5.0
• federation: 1.5.0
• files: 1.10.0
• files_pdfviewer: 1.4.0
• files_sharing: 1.7.0
• files_texteditor: 2.7.0
• files_trashbin: 1.5.0
• files_versions: 1.8.0
• files_videoplayer: 1.4.0
• firstrunwizard: 2.4.0
• gallery: 18.2.0
• logreader: 2.0.0
• lookup_server_connector: 1.3.0
• nextcloud_announcements: 1.4.0
• notifications: 2.3.0
• oauth2: 1.3.0
• password_policy: 1.5.0
• provisioning_api: 1.5.0
• serverinfo: 1.5.0
• sharebymail: 1.5.0
• support: 1.0.0
• survey_client: 1.3.0
• systemtags: 1.5.0
• theming: 1.6.0
• twofactor_backupcodes: 1.4.1
• updatenotification: 1.5.0
• workflowengine: 1.5.0
  Disabled:
• admin_audit
• encryption
• files_external
• user_ldap

Nextcloud configuration:

Config report

If you have access to your command line run e.g.:
sudo -u www-data php occ config:list system
from within your Nextcloud installation folder

or 

Insert your config.php content here. 
Make sure to remove all sensitive content such as passwords. (e.g. database password, passwordsalt, secret, smtp password, …)

{
"system": {
"passwordsalt": "REMOVED SENSITIVE VALUE",
"secret": "REMOVED SENSITIVE VALUE",
"trusted_domains": [
"localhost",
"cloud.entek.org.uk"
],
"datadirectory": "REMOVED SENSITIVE VALUE",
"dbtype": "pgsql",
"version": "15.0.7.0",
"overwrite.cli.url": "http://localhost",
"dbname": "REMOVED SENSITIVE VALUE",
"dbhost": "REMOVED SENSITIVE VALUE",
"dbport": "",
"dbtableprefix": "oc_",
"dbuser": "REMOVED SENSITIVE VALUE",
"installed": true,
"instanceid": "REMOVED SENSITIVE VALUE",
"mail_smtpmode": "sendmail",
"mail_sendmailmode": "smtp",
"mail_from_address": "REMOVED SENSITIVE VALUE",
"mail_domain": "REMOVED SENSITIVE VALUE"
}
}

Are you using external storage, if yes which one: local/smb/sftp/...

local

Are you using encryption: yes/no

not within nextcloud (https terminated at webserver)

Are you using an external user-backend, if yes which one: LDAP/ActiveDirectory/Webdav/...

No.

Client configuration

Browser:

Firefox 72.0.1

Operating system:

macOS 10.15.2

Logs

Web server error log

Web server error log

Insert your webserver log here

No.

Nextcloud log (data/nextcloud.log)

Nextcloud log

Insert your Nextcloud log here

No. (it's 1.2MB!)

Browser log

Browser log

Insert your browser log here, this could for example include:

a) The javascript console log
b) The network log
c) ...

No.

[kesselb]

Thanks for reporting back a problem 👍

1. If you check nextcloud log. Is there anything interesting? We don't need the complete log.

2. Usually the auth tokens table is big if clients are not using app password. Do you use username and password with the clients?

3. I think there is a job to cleanup this table. How do you run nextcloud cron (ajax, webcron or system cron)?

4. Nextcloud 15 is EOL. Please upgrade if possible to a newer version.

[LHurst-UoB]

Hi,

Thanks for reviewing this so quickly.

1. No, nothing obvious I could see.

2. Yes, I only have username/password login available (there's only 1 user, me, and I've got no other authentication mechanism setup).

3. I was relying on AJAX but I've just read up in the manual on the other options, I'll setup system cron to do it. Thanks for the pointer in the right direction.

4. I will try. NextCloud releases very often and does not support versions for very long (which makes it difficult to advocate for at work).

[kesselb]

Yes, I only have username/password login available (there's only 1 user, me, and I've got no other authentication mechanism setup).

https://docs.nextcloud.com/server/stable/user_manual/session_management.html#managing-devices

[LHurst-UoB]

Thanks, the manual doesn't explain how to use the device passwords - are they just used where I put my usual password?

Will they enforce 1 session/device if setup?

Looking at the sessions there 100s from "Linux" - my only Linux system is a headless box using the nextcloudcmd client to sync. Is this indicating the the client doesn't close the session correctly on completion of the sync, thus removing the session? I will check if there's a newer client for my system.

[kesselb]

are they just used where I put my usual password?

Yes.

Will they enforce 1 session/device if setup?

I think so.

Is this indicating the the client doesn't close the session correctly on completion of the sync, thus removing the session? I will check if there's a newer client for my system.

Each time you login with username and password a new session is created if session cookies are not preserved. That's probably a issue with nextcloudcmd. A background job should cleanup expired sessions. Sometimes that does not work well with ajax background mode.

[loz-hurst]

are they just used where I put my usual password?

Yes.

Great, thanks.

Will they enforce 1 session/device if setup?

I think so.

I’ve set this up now, it does look like that’s the case in practice :)

Is this indicating the the client doesn't close the session correctly on completion of the sync, thus removing the session? I will check if there's a newer client for my system.

Each time you login with username and password a new session is created if session cookies are not preserved. That's probably a issue with nextcloudcmd. A background job should cleanup expired sessions. Sometimes that does not work well with ajax background mode.

What defines an expired session? I see an expires column in the database but it’s null for every single row (I’ve got about 8,000 of them). Does next-cloud delete the auth token when I explicitly logout, or does it just rely on it expiring and getting cleaned up? If it gets removed then I agree, this seems like an issue with Nextcloudcmd not cleanly closing the session when it finishes and I’ll but them with an issue instead.

Thank you for your patience, I (clearly) don’t know anything about the internals of NextCloud.

[loz-hurst]

(Sorry, this is my personal account the other is my work one - I’m on my own device right now)

[LHurst-UoB]

After a couple of weeks, switching to a device password with the client seems to have solved the problem (there's now 6 entries in the oc_authtoken table instead of 10,000s).

I've been digging through the source and found the main logout controller method (https://github.com/nextcloud/server/blob/master/core/Controller/LoginController.php) but I've not figured out how it works with the client - I presume there's an API for that but I'm struggling to understand how the API works in the server code?

I'm trying to tie this to the commandline client code so I can figure out why it's not clearing the sessions (I presume not calling a logout) with user password authentication and submit a patch to fix it. I want to confirm there is an API endpoint to call to do a logout that will clear the auth token at this end.

[kesselb]

There is no api endpoint to logout. I would try to post the logout route but a valid CSRF token is required for that. Also make sure to validate this problem with Nextcloud 17 or 18.

[LHurst-UoB]

Yes, I upgraded to 18 on 1st Feb (when I reported this issue). It's reproducible with versions 16, 17 and 18.

Would you accept a pull to logout via a post to the web UI into the desktop client? It feels like a bit of a hack to get around what is both an annoyance (the database growing continuously) and a security risk (10,000s valid auth tokens staying alive beyond their usefulness once nextcloudcmd exits).

It seems odd not to have a mechanism to explicitly expire auth tokens once the client has finished it's job, if one is spawned every time it's run (I presume) through the API.

[LHurst-UoB]

Looking at the clients source (I'm a C++ guy, the PHP's a bit confusing to me), Account needs a functioning logout that's called at the end of main() (if a successful login ocured) to terminate the session it started. But that's going to need server support to be able to do the logout, which I guess is what we are discussing currently.

[kesselb]

cc @rullzer @misch7

[rullzer]

You should not try to trigger a logout form the client.

Like @kesselb says you should use apptokens for your clients. If you setup new clients they will do this automatically.

The tokens created if you use your real password are all temporary tokens. So if your cron runs properly with system cron they should get cleaned up after a day. So at most the temp token is 24 hours longer in the system. Which is fine.

[rullzer]

4. I will try.  NextCloud releases very often and does not support versions for very long (which makes it difficult to advocate for at work).

We do offer a subscription which includes longer support for versions

[loz-hurst]

If you setup new clients they will do this automatically.

That's weird because I've still not managed to make apptokens work with the Windows client - it rejects the token as not being valid so I have to use my password which then seems to require me to re-authenticate every 5-15 minutes once I turn on 2FA.

So at most the temp token is 24 hours longer in the system. Which is fine.

I strongly disagree - when there's of the order to 10,000s of valid tokens sitting in the database that creates a vast (and entirely unnecessary, if you allowed the client to terminate its session properly on exit) attack surface.

[rullzer]

@loz-hurst let me explain a bit more.

If you login with your normal username and password. Then the token never gets to the client. It will just login with username and password. Hence there is no real attack surface. Unless somebody manages to hijack your session. Which would be troublesome anyways.

Then regarding tokens. Automatically generated apptokens are 72 alphanum characters long.
And 62^72 is roughly 1.127 * 10^129

So even if you had 100k apptokens in your DB. That still leaves such a search space so huge that nobody an search it in 24h.

There are plans to have the client terminate an apptoken (for example when they remove an account). But this is just to cleanup the list.
The problem here is that the client in this case doesn't have the apptoken. But just a session and the normal username and password. And the apptoken should not be invalidated after each termination. Or you have to relogin again.

That's weird because I've still not managed to make apptokens work with the Windows client - it rejects the token as not being valid so I have to use my password which then seems to require me to re-authenticate every 5-15 minutes once I turn on 2FA.

That is weird. If you just add a new account in the client you should get a browser window to login. Log there in with your normal username+password and do the 2fa. Then your client will obtain a proper app password.

[loz-hurst]

Then the token never gets to the client.

Oh, I get it now - the session is attached to the authtoken, not the other way around!

And the apptoken should not be invalidated after each termination. Or you have to relogin again.

Yes, but with the nextcloudcmd client that has to happen anyway afaict? I've got this client configured with an app password instead of an account one but it still (I presume) goes through the same start -> login -> act -> terminate cycle every-time it's run? I understand that now it's using an app password, NextCloud can infer there should only ever be one session active for that credential and recycle the authtoken with the new session but I'm still not understanding why nextcloudcmd isn't permitted to terminate its session cleanly, so it doesn't show as an active session in Settings -> security or pollute the authtoken table, but instead resulting in a brand new authtoken every-time it is run. Or is there a better way to sync, than running nextcloudcmd periodically, on a headless box?

That is weird. If you just add a new account in the client you should get a browser window to login. Log there in with your normal username+password and do the 2fa. Then your client will obtain a proper app password.

It's a weird machine from work that I have limited control over (and an ancient version of IE as the default browser). I'll try re-installing the client and see if that fixes it.

[rullzer]

Yes, but with the nextcloudcmd client that has to happen anyway afaict?

no it should not. When you use an apppassword this should not use create new entries there.
The only thing that it will create is a session. but those time out after 24 minutes if not reused anyway.

I'll double check the nextcloudcmd but it should work just as well with apptokens.

[loz-hurst]

24 minutes

Don't you mean 24 hours? (60 * 60 * 24 - from DefaultTokenProvider.php)

I still don't understand from a design point of view (I now understand the technical implementation reasons) why nexcloudcmd does not end it's session on exit. If I logout from the client or web session everything seems to be neatly tidied up, the session no longer appears on the "Devices & sessions" list in "Security" but every-time I run nextcloudcmd a new sessions is created that stays there as an active one until cron invalidates it after 24 hours. As nextcloudcmd cannot reuse sessions, why are you so resistant to properly closing them (as I propose) when the program exits?

[skjnldsv]

Duplicate of #12377 ?

[ghost]

This issue has been automatically marked as stale because it has not had recent activity and seems to be missing some essential information. It will be closed if no further activity occurs. Thank you for your contributions.

[loz-hurst]

I don't think this is a duplicate of #12377 - the issue here is not about repeated authentications from the user perspective but that each time nextcloudcmd is run with password authentication it adds a new session that appears in both the auth table and the UI until they expire after 24 hours. With even a single user and moderate use this ends up filling both until the database runs out of disk space and the whole thing falls over.

[piiskop]

As of 2022, the issue is still there. i just had to reboot the server machine and clear it from binary logs that took 63 GB and all of them created by nextcloud. That space filled up within less than a week.

[SchoolGuy]

This issue is hitting me as well. Not with ~100MB/day but it is a growing problem for my users.

[piiskop]

i'm not sure whether this issue is connected to the data loss with ONLYOFFICE. A while ago, i somehow regulated the binary logs and ever since they don't grow disturbingly. And today, i discovered that all the data in a spreadsheets file that was entered ever since is lost although it was present yesterday.

[heartbleeded]

This problem persists. I use nextcloudcmd for every minute to sync and so that oc_authtoken grows and finally corrupted its index. It costs me an hour to recover from the backup...

[nachtgeist]

Confirmed present on 25.0.2, please re-open this issue (or #27603)

[kartoffelheinz]

Problem still occuring to some extent. Not as bad as before, but most definitely not solved.
Still seems this has been closed prematurely.

[vector090]

Problem persists too.
Nextcloud server 25.0.5.1