ejabberd 23.04

This new ejabberd 23.04 release includes many improvements and bug fixes, as well as some new features.

• Many SQL database improvements
• mod_mam support for XEP-0425: Message Moderation
• New mod_muc_rtbl, Real-Time Block List for MUC rooms
• Binaries use Erlang/OTP 25.3, and changes in containers

A more detailed explanation of these topics and other features:

Many improvements to SQL databases

There are many improvements in the area of SQL databases (see #3980 and #3982):

• Added support for migrating MySQL and MS SQL to new schema, fixed a long-standing bug, and many other improvements.
• Regarding MS SQL, there are schema fixes, added support for new schema and the corresponding schema migration, along with other minor improvements and bugfixes.
• The automated ejabberd tests now also run on updated schema databases, and support for running tests on MS SQL has been added.
• and other minor SQL schema inconsistencies, removed unnecessary indexes and changed PostgreSQL SERIAL columns to BIGSERIAL columns.

Please upgrade your existing SQL database, check the notes later in this document!

Added mod_mam support for XEP-0425: Message Moderation

XEP-0425: Message Moderation allows a Multi-User Chat (XEP-0045) moderator to moderate certain group chat messages, for example by removing them from the group chat history, as part of an effort to address and resolve issues such as message spam, inappropriate venue language, or revealing private personal information of others. It also allows moderators to correct a message on another user’s behalf, or flag a message as inappropriate, without having to retract it.

Clients that currently support this XEP are Gajim, Converse.js, Monocles, and have read-only support Poezio and XMPP Web.

New mod_muc_rtbl module

This new module implements Real-Time Block List for MUC rooms. It works by monitoring remote pubsub nodes according to the specification described in xmppbl.org.

captcha_url option now accepts auto value

In recent ejabberd releases, captcha_cmd got support for macros (in ejabberd 22.10) and support for using modules (in ejabberd 23.01).

Now captcha_url gets an improvement: if set to auto, it tries to detect the URL automatically, taking into account the ejabberd configuration. This is now the default. This should be good enough in most cases, but manually setting the URL may be necessary when using port forwarding or very specific setups.

Erlang/OTP 19.3 is deprecated

This is the last ejabberd release with support for Erlang/OTP 19.3. If you have not already done so, please upgrade to Erlang/OTP 20.0 or newer before the next ejabberd release. See the ejabberd 22.10 release announcement for more details.

About the binary packages provided for ejabberd:

• The binary installers and container images now use Erlang/OTP 25.3 and Elixir 1.14.3.
• The mix, ecs and ejabberd container images now use Alpine 3.17.
• The ejabberd container image now supports an alternative build method, useful to work around a problem in QEMU and Erlang 25 when building the image for the arm64 architecture.

Erlang node name in ecs container image

The ecs container image is built using the files from docker-ejabberd/ecs and published in docker.io/ejabberd/ecs. This image generally gets only minimal fixes, no major or breaking changes, but in this release it got one change that requires administrator intervention.

The Erlang node name is now fixed to ejabberd@localhost by default, instead of being variable based on the container hostname. If you previously allowed ejabberd to choose its node name (which was random), it will now create a new mnesia database instead of using the previous one:

$ docker exec -it ejabberd ls /home/ejabberd/database/
ejabberd@1ca968a0301a
ejabberd@localhost
...

A simple solution is to create a container that provides ERLANG_NODE_ARG with the old erlang node name, for example:

docker run ... -e ERLANG_NODE_ARG=ejabberd@1ca968a0301a

or in docker-compose.yml

version: '3.7'
services:
  main:
    image: ejabberd/ecs
    environment:
      - ERLANG_NODE_ARG=ejabberd@1ca968a0301a

Another solution is to change the mnesia node name in the mnesia spool files.

Other improvements to the ecs container image

In addition to the previously mentioned change to the default erlang node name, the ecs container image has received other improvements:

• For each commit to the docker-ejabberd repository that affects ecs and mix container images, those images are uploaded as artifacts and are available for download in the corresponding runs.
• When a new release is tagged in the docker-ejabberd repository, the image is automatically published to ghcr.io/processone/ecs, in addition to being manually published to the Docker Hub.
• There are new sections in the ecs README file: Clustering and Clustering Example.

Documentation Improvements

In addition to the usual improvements and fixes, some sections of the ejabberd documentation have been improved:

• Database Configuration of Microsoft SQL Server
• ejabberd test suites
• CAPTCHA

Acknowledgments

We would like to thank the following people for their contributions to the source code, documentation, and translation for this release:

• Stu Tomilson, many improvements to SQL
• Saarko, container improvements and installer updates
• Silvério Santos, for updating the Portuguese translation
• Blake Miller

And also to all the people who help solve doubts and problems in the ejabberd chatroom and issue tracker.

Updating SQL Databases

These notes allow you to apply the SQL database schema improvements in this ejabberd release to your existing SQL database. Please consider which database you are using and whether it is the default or the new schema.

PostgreSQL new schema:

Fixes a long-standing bug in the new schema on PostgreSQL. The fix for all existing affected installations is the same:

ALTER TABLE vcard_search DROP CONSTRAINT vcard_search_pkey;
ALTER TABLE vcard_search ADD PRIMARY KEY (server_host, lusername);

PosgreSQL default or new schema:

To convert columns to allow up to 2 billion rows in these tables. This conversion requires full table rebuilds and will take a long time if the tables already have many rows. Optional: This is not necessary if the tables will never grow large.

ALTER TABLE archive ALTER COLUMN id TYPE BIGINT;
ALTER TABLE privacy_list ALTER COLUMN id TYPE BIGINT;
ALTER TABLE pubsub_node ALTER COLUMN nodeid TYPE BIGINT;
ALTER TABLE pubsub_state ALTER COLUMN stateid TYPE BIGINT;
ALTER TABLE spool ALTER COLUMN seq TYPE BIGINT;

PostgreSQL or SQLite default schema:

DROP INDEX i_rosteru_username;
DROP INDEX i_sr_user_jid;
DROP INDEX i_privacy_list_username;
DROP INDEX i_private_storage_username;
DROP INDEX i_muc_online_users_us;
DROP INDEX i_route_domain;
DROP INDEX i_mix_participant_chan_serv;
DROP INDEX i_mix_subscription_chan_serv_ud;
DROP INDEX i_mix_subscription_chan_serv;
DROP INDEX i_mix_pam_us;

PostgreSQL or SQLite new schema:

DROP INDEX i_rosteru_sh_username;
DROP INDEX i_sr_user_sh_jid;
DROP INDEX i_privacy_list_sh_username;
DROP INDEX i_private_storage_sh_username;
DROP INDEX i_muc_online_users_us;
DROP INDEX i_route_domain;
DROP INDEX i_mix_participant_chan_serv;
DROP INDEX i_mix_subscription_chan_serv_ud;
DROP INDEX i_mix_subscription_chan_serv;
DROP INDEX i_mix_pam_us;

And now add index that might be missing

In PostgreSQL:

CREATE INDEX i_push_session_sh_username_timestamp ON push_session USING btree (server_host, username, timestamp);

In SQLite:

CREATE INDEX i_push_session_sh_username_timestamp ON push_session (server_host, username, timestamp);

MySQL default schema:

ALTER TABLE rosterusers DROP INDEX i_rosteru_username;
ALTER TABLE sr_user DROP INDEX i_sr_user_jid;
ALTER TABLE privacy_list DROP INDEX i_privacy_list_username;
ALTER TABLE private_storage DROP INDEX i_private_storage_username;
ALTER TABLE muc_online_users DROP INDEX i_muc_online_users_us;
ALTER TABLE route DROP INDEX i_route_domain;
ALTER TABLE mix_participant DROP INDEX i_mix_participant_chan_serv;
ALTER TABLE mix_participant DROP INDEX i_mix_subscription_chan_serv_ud;
ALTER TABLE mix_participant DROP INDEX i_mix_subscription_chan_serv;
ALTER TABLE mix_pam DROP INDEX i_mix_pam_u;

MySQL new schema:

ALTER TABLE rosterusers DROP INDEX i_rosteru_sh_username;
ALTER TABLE sr_user DROP INDEX i_sr_user_sh_jid;
ALTER TABLE privacy_list DROP INDEX i_privacy_list_sh_username;
ALTER TABLE private_storage DROP INDEX i_private_storage_sh_username;
ALTER TABLE muc_online_users DROP INDEX i_muc_online_users_us;
ALTER TABLE route DROP INDEX i_route_domain;
ALTER TABLE mix_participant DROP INDEX i_mix_participant_chan_serv;
ALTER TABLE mix_participant DROP INDEX i_mix_subscription_chan_serv_ud;
ALTER TABLE mix_participant DROP INDEX i_mix_subscription_chan_serv;
ALTER TABLE mix_pam DROP INDEX i_mix_pam_us;

Add index that might be missing:

CREATE INDEX i_push_session_sh_username_timestamp ON push_session (server_host, username(191), timestamp);

MS SQL

DROP INDEX [rosterusers_username] ON [rosterusers];
DROP INDEX [sr_user_jid] ON [sr_user];
DROP INDEX [privacy_list_username] ON [privacy_list];
DROP INDEX [private_storage_username] ON [private_storage];
DROP INDEX [muc_online_users_us] ON [muc_online_users];
DROP INDEX [route_domain] ON [route];
go

MS SQL schema was missing some tables added in earlier versions of ejabberd:

CREATE TABLE [dbo].[mix_channel] (
    [channel] [varchar] (250) NOT NULL,
    [service] [varchar] (250) NOT NULL,
    [username] [varchar] (250) NOT NULL,
    [domain] [varchar] (250) NOT NULL,
    [jid] [varchar] (250) NOT NULL,
    [hidden] [smallint] NOT NULL,
    [hmac_key] [text] NOT NULL,
    [created_at] [datetime] NOT NULL DEFAULT GETDATE()
) TEXTIMAGE_ON [PRIMARY];

CREATE UNIQUE CLUSTERED INDEX [mix_channel] ON [mix_channel] (channel, service)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

CREATE INDEX [mix_channel_serv] ON [mix_channel] (service)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

CREATE TABLE [dbo].[mix_participant] (
    [channel] [varchar] (250) NOT NULL,
    [service] [varchar] (250) NOT NULL,
    [username] [varchar] (250) NOT NULL,
    [domain] [varchar] (250) NOT NULL,
    [jid] [varchar] (250) NOT NULL,
    [id] [text] NOT NULL,
    [nick] [text] NOT NULL,
    [created_at] [datetime] NOT NULL DEFAULT GETDATE()
) TEXTIMAGE_ON [PRIMARY];

CREATE UNIQUE INDEX [mix_participant] ON [mix_participant] (channel, service, username, domain)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

CREATE INDEX [mix_participant_chan_serv] ON [mix_participant] (channel, service)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

CREATE TABLE [dbo].[mix_subscription] (
    [channel] [varchar] (250) NOT NULL,
    [service] [varchar] (250) NOT NULL,
    [username] [varchar] (250) NOT NULL,
    [domain] [varchar] (250) NOT NULL,
    [node] [varchar] (250) NOT NULL,
    [jid] [varchar] (250) NOT NULL
);

CREATE UNIQUE INDEX [mix_subscription] ON [mix_subscription] (channel, service, username, domain, node)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

CREATE INDEX [mix_subscription_chan_serv_ud] ON [mix_subscription] (channel, service, username, domain)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

CREATE INDEX [mix_subscription_chan_serv_node] ON [mix_subscription] (channel, service, node)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

CREATE INDEX [mix_subscription_chan_serv] ON [mix_subscription] (channel, service)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

CREATE TABLE [dbo].[mix_pam] (
    [username] [varchar] (250) NOT NULL,
    [channel] [varchar] (250) NOT NULL,
    [service] [varchar] (250) NOT NULL,
    [id] [text] NOT NULL,
    [created_at] [datetime] NOT NULL DEFAULT GETDATE()
) TEXTIMAGE_ON [PRIMARY];

CREATE UNIQUE CLUSTERED INDEX [mix_pam] ON [mix_pam] (username, channel, service)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

go

MS SQL also had some incompatible column types:

ALTER TABLE [dbo].[muc_online_room] ALTER COLUMN [node] VARCHAR (250);
ALTER TABLE [dbo].[muc_online_room] ALTER COLUMN [pid] VARCHAR (100);
ALTER TABLE [dbo].[muc_online_users] ALTER COLUMN [node] VARCHAR (250);
ALTER TABLE [dbo].[pubsub_node_option] ALTER COLUMN [name] VARCHAR (250);
ALTER TABLE [dbo].[pubsub_node_option] ALTER COLUMN [val] VARCHAR (250);
ALTER TABLE [dbo].[pubsub_node] ALTER COLUMN [plugin] VARCHAR (32);
go

… and mqtt_pub table was incorrectly defined in old schema:

ALTER TABLE [dbo].[mqtt_pub] DROP CONSTRAINT [i_mqtt_topic_server];
ALTER TABLE [dbo].[mqtt_pub] DROP COLUMN [server_host];
ALTER TABLE [dbo].[mqtt_pub] ALTER COLUMN [resource] VARCHAR (250);
ALTER TABLE [dbo].[mqtt_pub] ALTER COLUMN [topic] VARCHAR (250);
ALTER TABLE [dbo].[mqtt_pub] ALTER COLUMN [username] VARCHAR (250);
CREATE UNIQUE CLUSTERED INDEX [dbo].[mqtt_topic] ON [mqtt_pub] (topic)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
go

… and sr_group index/PK was inconsistent with other DBs:

ALTER TABLE [dbo].[sr_group] DROP CONSTRAINT [sr_group_PRIMARY];
CREATE UNIQUE CLUSTERED INDEX [sr_group_name] ON [sr_group] ([name])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
go

ChangeLog

General

• New s2s_out_bounce_packet hook
• Re-allow anonymous connection for connection without client certificates (#3985)
• Stop ejabberd_system_monitor before stopping node
• captcha_url option now accepts auto value, and it’s the default
• mod_mam: Add support for XEP-0425: Message Moderation
• mod_mam_sql: Fix problem with results of mam queries using rsm with max and before
• mod_muc_rtbl: New module for Real-Time Block List for MUC rooms (#4017)
• mod_roster: Set roster name from XEP-0172, or the stored one (#1611)
• mod_roster: Preliminary support to store extra elements in subscription request (#840)
• mod_pubsub: Pubsub xdata fields max_item/item_expira/children_max use max not infinity
• mod_vcard_xupdate: Invalidate vcard_xupdate cache on all nodes when vcard is updated

Admin

• ext_mod: Improve support for loading *.so files from ext_mod dependencies
• Improve output in gen_html_doc_for_commands command
• Fix ejabberdctl output formatting (#3979)
• Log HTTP handler exceptions

MUC

• New command get_room_history
• Persist none role for outcasts
• Try to populate room history from mam when unhibernating
• Make mod_muc_room:set_opts process persistent flag first
• Allow passing affiliations and subscribers to create_room_with_opts command
• Store state in db in mod_muc:create_room()
• Make subscribers members by default

SQL schemas

• Fix a long standing bug in new schema migration
• update_sql command: Many improvements in new schema migration
• update_sql command: Add support to migrate MySQL too
• Change PostgreSQL SERIAL to BIGSERIAL columns
• Fix minor SQL schema inconsistencies
• Remove unnecessary indexes
• New SQL schema migrate fix

MS SQL

• MS SQL schema fixes
• Add new schema for MS SQL
• Add MS SQL support for new schema migration
• Minor MS SQL improvements
• Fix MS SQL error caused by ORDER BY in subquery

SQL Tests

• Add support for running tests on MS SQL
• Add ability to run tests on upgraded DB
• Un-deprecate ejabberd_config:set_option/2
• Use python3 to run extauth.py for tests
• Correct README for creating test docker MS SQL DB
• Fix TSQLlint warnings in MSSQL test script

Testing

• Fix Shellcheck warnings in shell scripts
• Fix Remark-lint warnings
• Fix Prospector and Pylint warnings in test extauth.py
• Stop testing ejabberd with Erlang/OTP 19.3, as Github Actions no longer supports ubuntu-18.04
• Test only with oldest OTP supported (20.0), newest stable (25.3) and bleeding edge (26.0-rc2)
• Upload Common Test logs as artifact in case of failure

ecs container image

• Update Alpine to 3.17 to get Erlang/OTP 25 and Elixir 1.14
• Add tini as runtime init
• Set ERLANG_NODE fixed to ejabberd@localhost
• Upload images as artifacts to Github Actions
• Publish tag images automatically to ghcr.io

ejabberd container image

• Update Alpine to 3.17 to get Erlang/OTP 25 and Elixir 1.14
• Add METHOD to build container using packages (#3983)
• Add tini as runtime init
• Detect runtime dependencies automatically
• Remove unused Mix stuff: ejabberd script and static COOKIE
• Copy captcha scripts to /opt/ejabberd-*/lib like the installers
• Expose only HOME volume, it contains all the required subdirs
• ejabberdctl: Don’t use .../releases/COOKIE, it’s no longer included

Installers

• make-binaries: Bump versions, e.g. erlang/otp to 25.3
• make-binaries: Fix building with erlang/otp 25.x
• make-packages: Fix for installers workflow, which didn’t find lynx

Full Changelog

https://github.com/processone/ejabberd/compare/23.01…23.04

ejabberd 23.04 download & feedback

As usual, the release is tagged in the git source repository on GitHub.

The source package and installers are available on the ejabberd Downloads page. To verify the *.asc signature files, see How to verify the integrity of ProcessOne downloads.

For convenience, there are alternative download locations such as the ejabberd DEB/RPM Packages Repository and the GitHub Release / Tags.

The ecs container image is available in docker.io/ejabberd/ecs and ghcr.io/processone/ecs. The alternative ejabberd container image is available in ghcr.io/processone/ejabberd.

If you think you’ve found a bug, please search or file a bug report at GitHub Issues.