🗺️
Store Jitsi login user information in MySQL
Hello everyone.
This article was frequently accessed from overseas, so I will translate it into English and repost it.
I hope it helps.
Today's topic
User authentication of Jitsi of the OSS web conferencing system is a command operation, and it is necessary to restart the system after inputting the user addition command.
This requires the system to be restarted during a time when no one is using it, which is usually inconvenient.
In order to solve this, I thought that it could be solved by storing the logged-in user information in the DB and operating it separately. (unconfirmed)
To confirm the above, store the Jitsi login user information in MySQL.
As you can see from the above, it seems that you can connect to MySQL with the function of Prosody, which is a component of Jitsi.
The feeling I saw on Google, the Japanese people who are chasing this information, the premonition that it is the first rider as of 2021/12/31. It's a fever.
docker-Edit compose file
Setting to approach the Docker container config from the host OS
[root@meet docker-jitsi-meet]# vi docker-compose.yml
...
# XMPP server
prosody:
image: jitsi/prosody:stable-6726-1
restart: ${RESTART_POLICY}
expose:
- '5222'
- '5347'
- '5280'
volumes:
- ${CONFIG}/prosody/config:/config:Z
+ - ./prosody/rootfs/defaults:/defaults:Z
- ${CONFIG}/prosody/prosody-plugins-custom:/prosody-plugins-custom:Z
...
reboot
[root@meet docker-jitsi-meet]# docker-compose down
[root@meet docker-jitsi-meet]# docker-compose up -d
Editing prosody.cfg.lua
Set the authentication information to connect to MySQL.
- For 10.0.10.XX, set the internal IP of EC2.
- By adding sql_manage_tables = true, TABLE will be created automatically.
[root@meet docker-jitsi-meet]# vi ./prosody/rootfs/defaults/prosody.cfg.lua
--storage = "sql" -- Default is "internal" (Debian: "sql" requires one of the
+storage = "sql"
-- lua-dbi-sqlite3, lua-dbi-mysql or lua-dbi-postgresql packages to work)
-- For the "sql" backend, you can uncomment *one* of the below to configure:
--sql = { driver = "SQLite3", database = "prosody.sqlite" } -- Default. 'database' is the filename.
--sql = { driver = "MySQL", database = "prosody", username = "prosody", password = "secret", host = "localhost" }
+sql = { driver = "MySQL", database = "prosody", username = "prosody", password = "prosody_password", host = "10.0.10.XX", port = 3306, sql_manage_tables = true }
--sql = { driver = "PostgreSQL", database = "prosody", username = "prosody", password = "secret", host = "localhost" }
Introducing the LUADBI module
Prosody included in Jitsi does not include the driver lua-dbi-mysql that connects to the DB, so install it.
[root@meet docker-jitsi-meet]# docker-compose exec prosody /bin/bash
root@d5137c42747c:/# apt update
Get:1 http://security.debian.org/debian-security bullseye-security InRelease [44.1 kB]
Get:2 http://deb.debian.org/debian bullseye InRelease [116 kB]
...
Building dependency tree... Done
Reading state information... Done
5 packages can be upgraded. Run 'apt list --upgradable' to see them.
root@d5137c42747c:/# apt install lua-dbi-mysql
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
libmariadb3 lua-dbi-common mariadb-common mysql-common
...
Do you want to continue? [Y/n] y
...
Setting up libmariadb3:amd64 (1:10.5.12-0+deb11u1) ...
Setting up lua-dbi-mysql:amd64 (0.7.2-2) ...
Processing triggers for libc-bin (2.31-13+deb11u2) ...
root@d5137c42747c:/#
reboot
Please be careful as it is a little different.
[root@meet docker-jitsi-meet]# docker-compose stop
[root@meet docker-jitsi-meet]# docker-compose start
DB connection check
If successful. TABLE and initial data are created in the DB.
[root@meet mysql]# docker-compose exec db mysql -uprosody -p
Enter password:[password]
...
mysql> use prosody
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables; --If all goes well, TABLE will be created automatically.
+-------------------+
| Tables_in_prosody |
+-------------------+
| prosody |
| prosodyarchive |
+-------------------+
2 rows in set (0.00 sec)
mysql> select * from prosody; --If all goes well, it contains the default information.
+---------------------+----------+----------+------------------+--------+-----------------------------------------------+
| host | user | store | key | type | value |
+---------------------+----------+----------+------------------+--------+-----------------------------------------------+
| auth.meet.jitsi | focus | accounts | server_key | string | 7c3022********************************ec |
| auth.meet.jitsi | focus | accounts | stored_key | string | ec1d7f********************************0c |
| auth.meet.jitsi | focus | accounts | iteration_count | number | 4096 |
| auth.meet.jitsi | focus | accounts | salt | string | 12c031********************************97 |
| auth.meet.jitsi | focus | roster | | json | {"__hash":[false,{"pending":{},"version":2}]} |
| auth.meet.jitsi | focus | roster | focus.meet.jitsi | json | {"subscription":"from","groups":{}} |
| auth.meet.jitsi | jvb | accounts | stored_key | string | 5482fb********************************15 |
| auth.meet.jitsi | jvb | accounts | server_key | string | 03ddde********************************6c |
| auth.meet.jitsi | jvb | accounts | salt | string | f354b2********************************19 |
| auth.meet.jitsi | jvb | accounts | iteration_count | number | 4096 |
| auth.meet.jitsi | jibri | accounts | server_key | string | c9006f********************************d2 |
| auth.meet.jitsi | jibri | accounts | iteration_count | number | 4096 |
| auth.meet.jitsi | jibri | accounts | salt | string | b375a********************************3a |
| auth.meet.jitsi | jibri | accounts | stored_key | string | d3c4f********************************0f |
| recorder.meet.jitsi | recorder | accounts | iteration_count | number | 4096 |
| recorder.meet.jitsi | recorder | accounts | server_key | string | ede410********************************5d |
| recorder.meet.jitsi | recorder | accounts | stored_key | string | c5cf7e6********************************cd |
| recorder.meet.jitsi | recorder | accounts | salt | string | fb4a********************************50 |
| auth.meet.jitsi | jigasi | accounts | server_key | string | 6d26********************************ca |
| auth.meet.jitsi | jigasi | accounts | iteration_count | number | 4096 |
| auth.meet.jitsi | jigasi | accounts | stored_key | string | 356c9********************************61 |
| auth.meet.jitsi | jigasi | accounts | salt | string | 81ec97********************************45 |
+---------------------+----------+----------+------------------+--------+-----------------------------------------------+
22 rows in set (0.01 sec)
mysql> exit
Bye
User created
By executing the command, the added user will be stored in the DB.
[root@meet mysql]# cd ../docker-jitsi-meet/
[root@meet docker-jitsi-meet]# docker-compose exec prosody prosodyctl --config /config/prosody.cfg.lua register user001 meet.jitsi user001zxcvb
Confirmation after user creation
Check if the user added by the command is stored.
[root@meet docker-jitsi-meet]# cd ../mysql/
[root@meet mysql]# docker-compose exec db mysql -uprosody -p
Enter password:
...
mysql> use prosody
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from prosody;
+---------------------+----------+----------+------------------+--------+-----------------------------------------------+
| host | user | store | key | type | value |
+---------------------+----------+----------+------------------+--------+-----------------------------------------------+
| auth.meet.jitsi | focus | accounts | server_key | string | 7c3022********************************ec |
| auth.meet.jitsi | focus | accounts | stored_key | string | ec1d7f********************************0c |
| auth.meet.jitsi | focus | accounts | iteration_count | number | 4096 |
| auth.meet.jitsi | focus | accounts | salt | string | 12c031********************************97 |
| auth.meet.jitsi | focus | roster | | json | {"__hash":[false,{"pending":{},"version":2}]} |
| auth.meet.jitsi | focus | roster | focus.meet.jitsi | json | {"subscription":"from","groups":{}} |
| auth.meet.jitsi | jvb | accounts | stored_key | string | 5482fb********************************15 |
| auth.meet.jitsi | jvb | accounts | server_key | string | 03ddde********************************6c |
| auth.meet.jitsi | jvb | accounts | salt | string | f354b2********************************19 |
| auth.meet.jitsi | jvb | accounts | iteration_count | number | 4096 |
| auth.meet.jitsi | jibri | accounts | server_key | string | c9006f********************************d2 |
| auth.meet.jitsi | jibri | accounts | iteration_count | number | 4096 |
| auth.meet.jitsi | jibri | accounts | salt | string | b375a********************************3a |
| auth.meet.jitsi | jibri | accounts | stored_key | string | d3c4f********************************0f |
| recorder.meet.jitsi | recorder | accounts | iteration_count | number | 4096 |
| recorder.meet.jitsi | recorder | accounts | server_key | string | ede410********************************5d |
| recorder.meet.jitsi | recorder | accounts | stored_key | string | c5cf7e6********************************cd |
| recorder.meet.jitsi | recorder | accounts | salt | string | fb4a********************************50 |
| auth.meet.jitsi | jigasi | accounts | server_key | string | 6d26********************************ca |
| auth.meet.jitsi | jigasi | accounts | iteration_count | number | 4096 |
| auth.meet.jitsi | jigasi | accounts | stored_key | string | 356c9********************************61 |
| auth.meet.jitsi | jigasi | accounts | salt | string | 81ec97********************************45 |
| meet.jitsi | user001 | accounts | iteration_count | number | 4096 |
| meet.jitsi | user001 | accounts | server_key | string | 01451********************************9c |
| meet.jitsi | user001 | accounts | salt | string | 0ee24********************************d8 |
| meet.jitsi | user001 | accounts | stored_key | string | 563d7********************************4b |
+---------------------+----------+----------+------------------+--------+-----------------------------------------------+
26 rows in set (0.00 sec)
mysql>
Jitsi connection check
If all goes well, you will be connected from your browser.
Future tasks
We need to consider and implement how to install lua-dbi-mysql in docker-compose.
Also, when adding a user to the DB, the command is troublesome, so I would like to create some kind of GUI. (I want to make it with a web framework that is popular.)
Time required this time
I'm ashamed to say that I didn't notice the easy mistake of making a mistake in the PATH in the volunes setting of docker-compose, and I lost several hours, so it took about 8 hours after all.
- There is also a way to escape from reality and watch the railgun. T. Kihara is not allowed.
- If multiple people do it, I think that mistakes in assumptions will decrease.
Discussion