search

processone / ejabberd

Robust, Ubiquitous and Massively Scalable Messaging Platform (XMPP, MQTT, SIP Server)
https://www.process-one.net/en/ejabberd/
Other
6k stars 1.5k forks source link

PostgreSQL permission denied #4131

Closed nandlab closed 6 months ago

nandlab commented 6 months ago

Environment

Compilation and Installation Commands

./autogen.sh
./configure --enable-user=ejabberd --enable-group=ejabberd --enable-pgsql
make -j4
sudo make install

PostgreSQL Setup

I used this tutorial as a reference: https://landchad.net/ejabberd/

sudo apt install postgresql erlang-p1-pgsql
sudo -i
# Enter root password
su postgres
createuser --pwprompt ejabberd
# Enter a password for postgres user ejabberd
psql -c 'CREATE DATABASE ejabberd OWNER ejabberd;'
psql -c 'grant all privileges on database ejabberd to ejabberd;'
curl -s 'https://raw.githubusercontent.com/processone/ejabberd/master/sql/pg.sql' | psql ejabberd
psql -c 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to ejabberd;'
psql -c 'GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public to ejabberd;'
psql -c 'GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public to ejabberd;'

Configuration

Show file ejabberd.yml ```yaml include_config_file: /var/www/html/ipa.yml: # 'SELF_IPV4A' # 'SELF_IPV6A' allow_only: - "define_macro" hosts: - zauber.dynv6.net sql_type: pgsql sql_server: "localhost" sql_port: 5432 sql_database: "ejabberd" sql_username: "ejabberd" sql_password: 'XXX' sql_prepared_statements: true auth_method: [sql] default_db: sql loglevel: info certfiles: - /etc/letsencrypt/live/zauber.dynv6.net/fullchain.pem - /etc/letsencrypt/live/zauber.dynv6.net/privkey.pem listen: - port: 5222 ip: "::" module: ejabberd_c2s max_stanza_size: 262144 shaper: c2s_shaper access: c2s starttls_required: true - port: 5223 ip: "::" module: ejabberd_c2s max_stanza_size: 262144 shaper: c2s_shaper access: c2s tls: true - port: 5269 ip: "::" module: ejabberd_s2s_in max_stanza_size: 524288 shaper: s2s_shaper - port: 5443 ip: "::" module: ejabberd_http tls: true request_handlers: /admin: ejabberd_web_admin /api: mod_http_api /bosh: mod_bosh /captcha: ejabberd_captcha /upload: mod_http_upload /ws: ejabberd_http_ws - port: 5280 ip: "::" module: ejabberd_http request_handlers: /admin: ejabberd_web_admin - port: 3478 ip: "::" transport: udp module: ejabberd_stun use_turn: true turn_ipv4_address: 'SELF_IPV4A' - port: 1883 ip: "::" module: mod_mqtt backlog: 1000 acme: auto: false s2s_use_starttls: optional captcha_cmd: /usr/local/lib/ejabberd-23.10.34/priv/bin/captcha.sh captcha_url: auto acl: admin: user: admin1@zauber.dynv6.net local: user_regexp: "" loopback: ip: - 127.0.0.0/8 - ::1/128 access_rules: local: allow: local c2s: deny: blocked allow: all announce: allow: admin configure: allow: admin muc_create: allow: local pubsub_createnode: allow: local trusted_network: allow: loopback api_permissions: "console commands": from: - ejabberd_ctl who: all what: "*" "admin access": who: access: allow: - acl: loopback - acl: admin oauth: scope: "ejabberd:admin" access: allow: - acl: loopback - acl: admin what: - "*" - "!stop" - "!start" "public commands": who: ip: 127.0.0.1/8 what: - status - connected_users_number shaper: normal: rate: 3000 burst_size: 20000 fast: 100000 shaper_rules: max_user_sessions: 10 max_user_offline_messages: 5000: admin 100: all c2s_shaper: none: admin normal: all s2s_shaper: fast modules: mod_adhoc: {} mod_admin_extra: {} mod_announce: access: announce mod_avatar: {} mod_blocking: {} mod_bosh: {} mod_caps: {} mod_carboncopy: {} mod_client_state: {} mod_configure: {} mod_disco: {} mod_fail2ban: {} mod_http_api: {} mod_http_upload: put_url: https://@HOST@:5443/upload custom_headers: "Access-Control-Allow-Origin": "https://@HOST@" "Access-Control-Allow-Methods": "GET,HEAD,PUT,OPTIONS" "Access-Control-Allow-Headers": "Content-Type" mod_last: {} mod_mam: db_type: sql assume_mam_usage: true default: always mod_mqtt: {} mod_muc: access: - allow access_admin: - allow: admin access_create: muc_create access_persistent: muc_create access_mam: - allow default_room_options: mam: true mod_muc_admin: {} mod_offline: access_max_user_messages: max_user_offline_messages mod_ping: {} mod_privacy: {} mod_private: {} mod_proxy65: access: local max_connections: 5 mod_pubsub: access_createnode: pubsub_createnode plugins: - flat - pep force_node_config: storage:bookmarks: access_model: whitelist mod_push: {} mod_push_keepalive: {} mod_register: captcha_protected: true mod_roster: versioning: true mod_s2s_dialback: {} mod_shared_roster: {} mod_stream_mgmt: resend_on_timeout: if_offline mod_stun_disco: {} mod_vcard: {} mod_vcard_xupdate: {} mod_version: show_os: false ```

Errors from error.log

2023-12-25 15:13:45.398383+01:00 [error] <0.451.0>@ejabberd_sql:check_error/2:1374 SQL query 'Q80627668' at {mod_muc_sql,{292
,23}} failed: [{severity,'ERROR'},
        {86,<<"ERROR">>},
        {code,<<"42501">>},
        {message,
        <<"permission denied for table muc_room">>},
        {file,<<"aclchk.c">>},
        {line,3650},
        {routine,
        <<"aclcheck_error">>}]
        2023-12-25 15:13:45.642271+01:00 [error] <0.452.0>@ejabberd_sql:check_error/2:1374 SQL query 'Q65246440' at {mod_mqtt_sql,{136,10}} failed: [{severity,'ERROR'},
        {86,<<"ERROR">>},
        {code,<<"42501">>},
        {message,
        <<"permission denied for table mqtt_pub">>},
        {file,
        <<"aclchk.c">>},
        {line,3650},
        {routine,
        <<"aclcheck_error">>}]
2023-12-25 15:13:45.643213+01:00 [critical] <0.269.0>@gen_mod:start_module/4:184 Failed to start module mod_mqtt: 
{error,{db_failure,{child,undefined,'mod_mqtt_zauber.dynv6.net',
  {gen_server,start_link,
              [{local,'mod_mqtt_zauber.dynv6.net'},
               mod_mqtt,
               [<<"zauber.dynv6.net">>,
                #{access_publish => [],
                  access_subscribe => [],
                  cache_life_time => 3600000,
                  cache_missed => true,
                  cache_size => 1000,db_type => sql,
                  match_retained_limit => 1000,
                  max_queue => 5000,
                  max_topic_aliases => 100,
                  max_topic_depth => 8,
                  queue_type => ram,
                  ram_db_type => mnesia,
                  session_expiry => 300000,
                  use_cache => true}],
               []]},
  transient,false,60000,worker,
  [mod_mqtt]}}}
2023-12-25 15:13:45.643676+01:00 [critical] <0.269.0>@gen_mod:maybe_halt_ejabberd/0:283 ejabberd initialization was aborted because a module start failed.

Bug description

ejabberd does not start because of PostgreSQL database permission errors.

badlop commented 6 months ago

Right, it seems there is some problem in your method to create the database and grant permissions to the account, or in the information you specified in the ejabberd configuration file.

You can see how the database creation is done in the CI workflow

https://github.com/processone/ejabberd/blob/a57bdfffb715abd1334045bf7b33c113c1b46ca0/.github/workflows/ci.yml#L83-L94

with that setup, the configuration options would be:

sql_port: 5432
sql_type: pgsql
sql_server: "localhost"
sql_database: "ejabberd_test"
sql_username: "ejabberd_test"
sql_password: "ejabberd_test"
auth_method: sql
default_db: sql

Another minor suggestions:

make -j4

I think j4 is almost useless nowadays

sudo apt install erlang-p1-pgsql

No need to install that library, as ejabberd downloads it with many other erlang libraries automatically.

curl -s 'https://raw.githubusercontent.com/processone/ejabberd/master/sql/pg.sql' | psql ejabberd

As you are using ejabberd 23.10 or newer, you can enable this new option in ejabberd.yml, and ejabberd will take care to create the database schema (the tables, indexes, etc):

update_sql_schema: true

See the related blogpost https://www.process-one.net/blog/automatic-schema-update-in-ejabberd/

With that option, you just need to create the database, create the user, and grant privileges to the database to that user; no need to import the database schema.

nandlab commented 6 months ago

Thank you for the fast reply!