Migrate IMAP users to regular database account

[simaoafonso-pwt]

Is your feature request related to a problem? Please describe.
Now that many services are dropping non-OAuth IMAP support, I want to migrate existing account to regular internal database accounts.

Describe the solution you'd like
I wanted some documentation on how to migrate \OCA\UserExternal accounts to internal "Database" accounts.

Running raw SQL on the database is acceptable, this is a one-shot change.

Describe alternatives you've considered
I saw some IMAP proxies, but those support a single username, from what I can tell.

[ervee]

Disclaimer: I have not tested this!

If you just disable the user_external plugin. Are all users then not regular users again? Perhaps logout/login the admin user, set a password for the users and done?

[simaoafonso-pwt]

Disclaimer: I have not tested this!

If you just disable the user_external plugin. Are all users then not regular users again? Perhaps logout/login the admin user, set a password for the users and done?

I would love if it would be that easy. I haven't tested it either, and I'm not willing to potentially break production to test this.
I would like some confirmation this works.

[ervee]

I would highly recommend to setup a test Nextcloud. You really don't need much for it.

That said. I was curious so I did it (in my test env.).

I started with the local account hmt2:

I logged in, created a contact and uploaded a file.

You can see when I edit the account with the admin UI I can't edit the password because it is an external account:

I then disabled the user_external plugin. Then all accounts from user_external are gone!

When I try to create a local account hmt2 it gives an error:

So no, it did not work :)

When I enable user_external again the user is back in the admin interface and I can login again and the user files and contacts still exists.

[simaoafonso-pwt]

So no, it did not work :)

Thank you very much for this, with screenshots too!

I peeked around and it seems the external users are stored in separate database tables, so this always needs some extra work.

• https://github.com/nextcloud/user_external/blob/master/lib/Base.php#L179
• https://github.com/nextcloud/server/blob/c4ac979edcb75b3b2323bac7919313509a9939b0/lib/private/User/Database.php#L61-L63

A solution that requires raw SQL queries to the database or some kid of API access is acceptable, I understand this is not a everyday request.

I cannot find and easy way to check the DB schema, to make sure the users and users_external tables are compatible, I'm almost sure they aren't...

[ervee]

I kind of have the same problem migrating users to LDAP. I found a forum post somewhere with detailed SQL statements to make such a migration work. If I can find it again I'll post it here.

[simaoafonso-pwt]

It seems this is easier than I though, as long as you keep the uid.

These are the important tables, pretty simple stuff:

select * from oc_users;

     uid     |         displayname          |                                              password                                               |  uid_lower
-------------+------------------------------+-----------------------------------------------------------------------------------------------------+-------------
...

select * from oc_users_external;

    backend     |       uid       |   displayname
----------------+-----------------+-----------------
...

---

• You need to generate a new password and hardcode it (the easiest way is create a new account with the new password, and just copy it, this not just a simple passwd hash). This query will also delete the external users:

begin transaction;
insert into oc_users
select uid, displayname, '3|$argon2id$v=19$m=65536,t=4,p=...' as password, lower(uid) as uid_lower from oc_users_external;
delete from oc_users_external;
commit;

Generating a new password per-user is left as an exercise for the reader.

• Once the users can login, you can disable the user_external app:

occ app:disable user_external

This seems to work well. The users will need to re-login, but it seems every setting remains as-is.

Watch this space for the next couple days/weeks, I'll let you know if something comes up.

[ervee]

That looks nice. The solution I'm trying for migrating to LDAP is this one: https://help.nextcloud.com/t/migrating-existing-nextcloud-user-account-to-ldap/176292/3
I think this is not a route to take for migrating to local users but I did want to post here :)

[Mannshoch]

I would vote for a click solution. Idea:

• Have a Button to switch an User: (at least to a regular user)
• Allow to enter a comment
• after tick this, user should be able to work with already logged-in user and devices
• If a browser or a capable device is open, popup a password change dialog with the comment I entered
• and allow a seamless further work

Cause:
In near future I have to switch several users back to regular user because our Mail provider will change and with that also how we work with mail and Nextcloud.