Ejabberd: Migrating Mnesia "passwd" table to MySQL "user" table

Question

I have an (old) ejabberd instance that still uses 'internal' as authentication method. I installed a shiny new server (including MySQL) and am planning to migrate to it ASAP. I would like to avoid using Mnesia as authentication DB from then on.

Since my users' passwords are still stored in the Mnesia-database, I need to import them into the (new) MySQL DB on the new server. I succeeded in dumping the 'passwd' table and it is filled with entries like this one:

{passwd,{<<"flowie">>,<<"server.com">>},

{scram,<<"pHHeHwc5yaarPAshse7Ijuygtre=">>,
      <<"4Qiv9ygiMLlzeZXUG6Bpyhygtgr=">>,

      <<"dylctQFXYGXemMii1Pswe==">>,4096}}

To be able to correctly import these entries into the MySQL DB I need to figure out which field corresponds to which in the MySQL 'users' table:

+----------------+--------------+------+-----+-------------------+-------+
| Field          | Type         | Null | Key | Default           | Extra |
+----------------+--------------+------+-----+-------------------+-------+
| username       | varchar(191) | NO   | PRI | NULL              |       |
| password       | text         | NO   |     | NULL              |       |
| serverkey      | varchar(64)  | NO   |     |                   |       |
| salt           | varchar(64)  | NO   |     |                   |       |
| iterationcount | int(11)      | NO   |     | 0                 |       |
| created_at     | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |
+----------------+--------------+------+-----+-------------------+-------+
6 rows in set (0.00 sec)

I obviously know what the 'username' field is (and I think I can guess what the 'iterationcount' would be), but I want to make sure I get the others in the right order.

In one phrase: in what order are the 'password', 'serverkey' and 'salt' fields stored in an ejabberd Mnesia DB ? Where can I find info about this ? In the code perhaps ?

Note for the aspiring hackers among you: I did change the values, using a random character generator ;)

Badlop Badlop · Accepted Answer · 2018-04-03T21:27:54

I configured ejabberd 18.03 with the option

auth_password_format: scram

and created an account. Its authentication information is stored like this in Mnesia:

{passwd,{<<"user1">>,<<"localhost">>},
    {scram,<<"Eu9adR8M5NPIBoVKK917UKJQTtE=">>,
           <<"0mRs0DKWvb8C0/fcVmTRP2elKOA=">>,
           <<"UclT113AyXYlUAZgv3q0vA==">>,4096}}

Later I exported Mnesia to a SQL file using the command:

ejabberdctl export2sql localhost /tmp/localhost.sql

and the resulting file contains this line:

INSERT INTO users(username, password, serverkey, salt, iterationcount)
VALUES ('user1',
        'Eu9adR8M5NPIBoVKK917UKJQTtE=',
        '0mRs0DKWvb8C0/fcVmTRP2elKOA=',
        'UclT113AyXYlUAZgv3q0vA==', 4096);

Ejabberd: Migrating Mnesia "passwd" table to MySQL "user" table

1 Answers