
Database Configuration
Supported storages
ejabberd
uses its internal Mnesia database by default. However, it is
possible to use a relational database, key-value storage or an LDAP
server to store persistent, long-living data. ejabberd
is very
flexible: you can configure different authentication methods for
different virtual hosts, you can configure different authentication
mechanisms for the same virtual host (fallback), you can set different
storage systems for modules, and so forth.
The following databases are supported by ejabberd
:
MySQL
. Check the tutorial Using ejabberd with MySQLAny
ODBC
compatible databaseRedis
(only for transient data)
Please check LDAP Configuration section for documentation about using LDAP.
Database Schema
When using external database backend, ejabberd does not create schema and tables by itself. If you plan to use MySQL, PostgreSQL, MS SQL or SQLite, you must create the schema before you run ejabberd.
If installing ejabberd from sources, you will find sql script for your backend in the installation directory. By default:
/usr/local/lib/ejabberd/priv/sql
If installing ejabberd from Process-One installer, the init scripts are located in the ejabberd's installation path under
<base>/lib/ejabberd*/priv/sql
If using MySQL or PostgreSQL, you can choose between the default or the new schemas.
See ejabberd SQL Database Schema for details on database schemas.
Virtual Hosting
Important note about virtual hosting: if you define several domains in ejabberd.yml (see section Host Names), you probably want that each virtual host uses a different configuration of database, authentication and storage, so that usernames do not conflict and mix between different virtual hosts. For that purpose, the options described in the next sections must be set inside a host_config for each vhost (see section Virtual Hosting). For example:
host_config:
public.example.org:
sql_type: pgsql
sql_server: localhost
sql_database: database-public-example-org
sql_username: ejabberd
sql_password: password
auth_method: [sql]
Default database
You can simplify the configuration by setting the default database.
This can be done with the
default_db
top-level option:
default_db: mnesia|sql
: This will define the default database for a module lacking db_type
option or if auth_method
option is not set.
Relational Databases
Default and New Schemas
There are two database schemas available in ejabberd: the default schema is preferable when serving one massive domain, the new schema is preferable when serving many small domains.
The default schema stores only one XMPP domain in the database. The XMPP domain is not stored as this is the same for all the accounts, and this saves space in massive deployments. However, to handle several domains, you have to setup one database per domain and configure each one independently using host_config, so in that case you may prefer the new schema.
The new schema stores the XMPP domain in a new column server_host
in the database entries,
so it allows to handle several XMPP domains in a single ejabberd database.
Using this schema is preferable when serving several XMPP domains
and changing domains from time to time.
However, if you have only one massive domain, you may prefer to use the default schema.
To use the new schema, edit the ejabberd configuration file and enable new_sql_schema top-level option:
new_sql_schema: true
Now, when creating the new database, remember to use the proper SQL schema!
For example, if you are using MySQL and choose the default schema, use mysql.sql
.
If you are using PostgreSQL and need the new schema, use pg.new.sql
.
If you already have a MySQL or PostgreSQL database with the default schema and contents, you can upgrade it to the new schema:
MySQL: Edit the file
sql/mysql.old-to.new.sql
which is included with ejabberd, fill DEFAULT_HOST in the first line, and import that SQL file in your database. Then enable thenew_sql_schema
option in the ejabberd configuration, and restart ejabberd.PostgreSQL: First enable
new_sql_schema
and mod_admin_update_sql in your ejabberd configuration:new_sql_schema: true modules: mod_admin_update_sql: {}
then restart ejabberd, and finally execute the update_sql command:
ejabberdctl update_sql
SQL Options
The actual database access is defined in the options with sql_
prefix. The values are used to define if we want to use ODBC, or one of
the two native interface available, PostgreSQL or MySQL.
To configure SQL there are several top-level options:
- sql_type
- sql_server
- sql_port
- sql_database
- sql_username
- sql_password
- sql_ssl
- sql_ssl_verify
- sql_ssl_cafile
- sql_ssl_certfile
- sql_pool_size
- sql_keepalive_interval
- sql_odbc_driver
- sql_start_interval
- sql_prepared_statements
Example of plain ODBC connection:
sql_server: "DSN=database;UID=ejabberd;PWD=password"
Example of MySQL connection:
sql_type: mysql
sql_server: server.company.com
sql_port: 3306 # the default
sql_database: mydb
sql_username: user1
sql_password: "**********"
sql_pool_size: 5
SQL Authentication
You can authenticate users against an SQL database, see the option auth_method
in the Authentication section.
To store the passwords in SCRAM format instead of plaintext, see the SCRAM section.
SQL with SSL Connection
It's possible to setup SSL encrypted connections to PostgreSQL, MySQL and MsSQL
by enabling the sql_ssl option in
ejabberd's configuration file: sql_ssl: true
Please notice that ejabberd verifies the certificate presented by the SQL server against the CA certificate list. For that reason, if your SQL server uses a self-signed certificate, you need to setup sql_ssl_verify and sql_ssl_cafile, for example:
sql_ssl: true
sql_ssl_verify: false
sql_ssl_cafile: "/path/to/sql_server_cacert.pem"
This tells ejabberd to ignore problems from not matching any CA certificate from default list, and instead try to verify using the specified CA certificate.
SQL Storage
Several ejabberd
modules
have options called db_type
, and can store their tables
in an SQL database instead of internal.
In this sense, if you defined your database access using the
SQL Options,
you can configure a module to use your database
by adding the option db_type: sql
to that module.
Alternatively, if you want all modules to use your SQL database when possible, you may prefer to set SQL as your default database.
Redis
Redis
is an advanced key-value cache and store. You can
use it to store transient data, such as records for C2S (client) sessions.
There are several options available:
redis_server: String
: A hostname of the Redis server. The default is localhost
.
redis_port: Port
: The port where the Redis server is accepting connections. The default
is 6379.
redis_password: String
: The password to the Redis server. The default is an empty string,
i.e. no password.
redis_db: N
: Redis database number. The default is 0.
redis_connect_timeout: N
: A number of seconds to wait for the connection to be established to the Redis
server. The default is 1 second.
Example configuration:
redis_server: redis.server.com
redis_db: 1
Microsoft SQL Server
For now, MS SQL is only supported in Unix-like OS'es. You need to have
unixODBC
installed on your machine, and your Erlang/OTP
must be compiled with ODBC support.
Also, in some cases you need to add machine name to sql_username
, especially
when you have sql_server
defined as an IP address, e.g.:
sql_type: mssql
sql_server: 1.2.3.4
...
sql_username: user1@host
By default, ejabberd will use the FreeTDS
driver.
You need to have the driver file libtdsodbc.so
installed in your library PATH
on your system.
If the FreeTDS driver is not installed in a standard location, or if you want to use another ODBC driver, you can specify the path to the driver using the sql_odbc_driver option, available in ejabberd 20.12 or later. For example, if you want to use Microsoft ODBC Driver 17 for SQL Server:
sql_odbc_driver: "/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1"
Note that if you use a Microsoft driver, you may have to use an IP address
instead of a host name for the sql_server
option.
If hostname (or IP address) is specified in sql_server
option, ejabberd will
connect using a an ODBC DSN connection string constructed with:
- SERVER=sql_server
- DATABASE=sql_database
- UID=sql_username
- PWD=sql_password
- PORT=sql_port
- ENCRYPTION=required (only if sql_ssl is true)
- CLIENT_CHARSET=UTF-8
As of ejabberd 23.xx it is possible to use different connection options by
putting a full ODBC connection string in sql_server
(e.g.
DSN=database;UID=ejabberd;PWD=password
). The DSN must be configured in
existing system or user odbc.ini file, where it can be configured as desired,
using a driver from system odbcinst.ini. The sql_odbc_driver
option will have no effect in this case.
If specifying an ODBC connection string, an ODBC connection string must also be specified for any other hosts using MS SQL DB, otherwise the auto-generated ODBC configuration will interfere.