processone/ejabberd 23.04 on GitHub

Release notes copied from the original ejabberd 23.04 announcement post:

This new ejabberd 23.04 release includes many improvements and bug fixes, and also a few new features.

Many improvements in SQL databases
mod_mam supports 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 those topics and other features:

Many improvements in SQL databases

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

Added support to migrate 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 to new schema, and the corresponding schema migration, along other minor improvements and bugfixes.
The automated ejabberd testing now also runs tests on upgraded schema databases, and supports for running tests on MS SQL
And also fixed other minor SQL schema inconsistencies, removed unnecessary indexes and changed PostgreSQL SERIAL to BIGSERIAL columns.

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

`mod_mam` supports XEP-0425: Message Moderation

XEP-0425: Message Moderation allows a Multi-User Chat (XEP-0045) moderator to moderate certain groupchat messages by, for example, retracting them from the groupchat history as part of an effort to address and remedy issues such as message spam, indecent language for the venue or exposing private third-party personal information. It also allows the moderators to correct a message on another user's behalf, or flag a message as inappropriate without requiring that it be retracted.

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

New `mod_muc_rtbl`

This new module implements Real-Time Block List for MUC rooms. It works by observing remote pubsub node conforming with 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 to use modules (in ejabberd 23.01).

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

Erlang/OTP 19.3 is discouraged

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

Regarding 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 alternate build method, useful to bypass a problem in QEMU and Erlang 25 when building the image for 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 in general gets only minimal fixes, no major or breaking changes, but in this release it got a change that will require the administrator intervention.

The Erlang node name is now by default fixed to ejabberd@localhost, instead of being variably set by the container host name. If you previously allowed ejabberd to decide its node name (which was random), then 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 the container providing 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 in the `ecs` container image

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

For every commit in the docker-ejabberd repository relevant to ecs and mix container images, those images are uploaded as artifacts, and available to download in the corresponding runs.
When a new version is tagged in the docker-ejabberd repository, the image is automatically published in ghcr.io/processone/ecs, in addition to the manual publication in Docker Hub.
There are new sections in ecs README file: Clustering and Clustering Example.

Documentation improvements

In addition to the normal improvements and fixes, two sections in the ejabberd Documentation are improved:

Database Configuration -> Microsoft SQL Server
ejabberd Test Suites
CAPTCHA

Acknowledgments

We would like to thank the contributions to the source code, documentation, and translation provided for this release by:

Stu Tomilson, many improvements in SQL
Saarko, improvements in the containers, and updated the installers
Silvério Santos for updating the Portuguese translation
Blake Miller

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

SQL databases update

Those notes allow to apply the improvements in the SQL database schemas from this ejabberd release to your existing SQL database. Please take into account what database you use, and whether it is the default or the new schema.

PostgreSQL new schema:

Fix a long standing bug in new schema on PostgreSQL. The fix for any existing impacted 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 will require full table rebuilds, and will take a long time if tables already have lots of rows. Optional: this is not necessary if the tables are never likely to 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 v25.x
make-packages: Fix for installers workflow, which didn't find lynx

Full Changelog

23.01...23.04

ejabberd 23.04 download & feedback

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

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

For convenience, there are alternative download locations like 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 consider that you've found a bug, please search or fill a bug report on GitHub Issues.

processone/ejabberd 23.04 on GitHub

Many improvements in SQL databases

mod_mam supports XEP-0425: Message Moderation

New mod_muc_rtbl

captcha_url option now accepts auto value

Erlang/OTP 19.3 is discouraged

Erlang node name in ecs container image

Other improvements in the ecs container image

Documentation improvements

Acknowledgments

SQL databases update

PostgreSQL new schema:

PosgreSQL default or new schema:

PostgreSQL or SQLite default schema:

PostgreSQL or SQLite new schema:

MySQL default schema:

MySQL new schema:

MS SQL

ChangeLog

General

Admin

MUC

SQL schemas

MS SQL

SQL Tests

Testing

ecs container image

ejabberd container image

Installers

Full Changelog

ejabberd 23.04 download & feedback

processone/ejabberd 23.04
on GitHub

`mod_mam` supports XEP-0425: Message Moderation

New `mod_muc_rtbl`

`captcha_url` option now accepts `auto` value

Erlang node name in `ecs` container image

Other improvements in the `ecs` container image

`ecs` container image

`ejabberd` container image