Zabbix-server won't install with postgres-15

[badfiles]

After initial schema applied server won't start

169095:20230327:132255.794 Starting Zabbix Server. Zabbix 6.4.0 (revision 5b2736b6027).
...
169095:20230327:132255.808 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  relation "users" does not exist
LINE 1: select userid from users limit 1
                           ^
 [select userid from users limit 1]
169095:20230327:132255.808 cannot use database "zabbix": database is not a Zabbix database

[markuman]

when you've set that you've applied the initial schema but zabbix said that zabbix datase is noch a Zabbix database (and the users table is not found), then it sound like zabbix is connected to a wrong database or the initialization happen on a wrong database.

[badfiles]

the role applied, I did nothing by hand

[BGmot]

Please provide all the variables you passed to the role.

[badfiles]

    database_type: postgresql
    database_type_long: postgresql
    zabbix_server_dbname: zabbix
    zabbix_server_dbuser: zabbix
    zabbix_server_dbpassword: ****

    zabbix_server_allowunsupporteddbversions: 1
    zabbix_server_cachesize: 128M
    zabbix_server_housekeepingfrequency: 12
    zabbix_server_maxhousekeeperdelete: 0
    zabbix_server_starthttppollers: 2
    zabbix_server_startjavapollers: 0
    zabbix_server_valuecachesize: 64M
    zabbix_web_memory_limit: 256M

    zabbix_timezone: "{{ default_timezone }}"
    zabbix_database_creation: false
    zabbix_database_sqlload: True

    zabbix_websrv: nginx
    zabbix_php_install: false
    zabbix_nginx_tls: true
    zabbix_nginx_redirect: true
    zabbix_nginx_tls_crt: "/etc/letsencrypt/live/{{ ansible_fqdn }}/fullchain.pem"
    zabbix_nginx_tls_key: "/etc/letsencrypt/live/{{ ansible_fqdn }}/privkey.pem"
    zabbix_nginx_tls_dhparam: /etc/pki/tls/dhparams.pem
    zabbix_nginx_tls_session_timeout: 1d
    zabbix_nginx_tls_session_cache: shared:MySSL:10m
    zabbix_nginx_tls_session_tickets: "on"
    zabbix_nginx_tls_protocols: TLSv1.2 TLSv1.3
    zabbix_nginx_tls_ciphers: ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384

[BGmot]

The role will not create database

zabbix_database_creation: zabbix_database_creation

what are you trying to achieve? and how? what is your scenario?

[pyrodie18]

Also we have not tested 6.4 yet

[pyrodie18]

Also we have not tested 6.4 yet

Nevermind, I missed #937

[badfiles]

the database is obviously created by another role, how would it appy schema if no database existed?

[badfiles]

the same variable set installs the server on posgtes14 successfully

[BGmot]

is it all-in-one (web-server-db on the same host) set up? The error you see is clearly saying that Zabbix server is connecting to wrong DB. So can you check Zabbix server config and report what settings are wrong there for PG 15? (I suppose you know for sure where your DB is running)

[badfiles]

all-in-one, yes. i'll check

[pyrodie18]

Any update @badfiles ?

[pyrodie18]

So I guess this opens up a discussion. With the new molecule tests we're pushing out in 2.0, we test each version of Zabbix against each version of the supported OSs. Do we need to add testing against different visions of databases as well (at least for the server role perhaps)? @BGmot @D3DeFi

[D3DeFi]

I advise against testing multiple versions of databases. This collection should not solve everything for everyone, just some LTS release of postgres/mysql should be fine

[pyrodie18]

So trying to do some testing on this and I run into problems. When I run molecule and just update the DB version in the prepare script to 15, I error out on the "Create Schema" task. It looks like something changed with 15 because zabbix-server password doesn't work even after the user is created. If modify the Create User task to get rid of the MD5 and just pass the plaintext password, then that password works.

However, even when I do that and continue testing I get something really weird. The Create Schema task runs but it seems like it skips ahead a few tasks (see below)

TASK [community.zabbix.zabbix_server : PostgreSQL | Remote | Create database] ***
task path: /root/devel/collections/ansible_collections/community/zabbix/roles/zabbix_server/tasks/postgresql.yml:52
changed: [zabbix-server-] => {"changed": true, "db": "zabbix-server", "executed_commands": ["CREATE DATABASE \"zabbix-server\""]}

TASK [community.zabbix.zabbix_server : PostgreSQL | Remote | Create database user] ***
task path: /root/devel/collections/ansible_collections/community/zabbix/roles/zabbix_server/tasks/postgresql.yml:61
changed: [zabbix-server-] => {"changed": true, "queries": ["CREATE USER \"********\" WITH ENCRYPTED PASSWORD %(password)s ", "GRANT CREATE, CONNECT, TEMPORARY ON DATABASE \"********\" TO \"********\""], "user": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER"}

TASK [community.zabbix.zabbix_server : PostgreSQL | Remote | Create timescaledb extension] ***
task path: /root/devel/collections/ansible_collections/community/zabbix/roles/zabbix_server/tasks/postgresql.yml:74
skipping: [zabbix-server-] => {"changed": false, "skip_reason": "Conditional result was False"}

TASK [community.zabbix.zabbix_server : PostgreSQL | Create schema] *************
task path: /root/devel/collections/ansible_collections/community/zabbix/roles/zabbix_server/tasks/postgresql.yml:89
fatal: [zabbix-server-]: FAILED! => {"changed": false, "msg": "Unable to start service zabbix-server: Job for zabbix-server.service failed because the service did not take the steps required by its unit configuration.\nSee \"systemctl status zabbix-server.service\" and \"journalctl -xe\" for details.\n"}
ERROR    Task exception was never retrieved
future: <Task finished name='Task-11' coro=<_read_stream() done, defined at /usr/local/lib/python3.9/site-packages/subprocess_tee/__init__.py:24> exception=ValueError('Separator is not found, and chunk exceed the limit')>
Traceback (most recent call last):
  File "/usr/lib64/python3.9/asyncio/streams.py", line 540, in readline
    line = await self.readuntil(sep)
  File "/usr/lib64/python3.9/asyncio/streams.py", line 618, in readuntil
    raise exceptions.LimitOverrunError(
asyncio.exceptions.LimitOverrunError: Separator is not found, and chunk exceed the limit

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/subprocess_tee/__init__.py", line 26, in _read_stream
    line = await stream.readline()
  File "/usr/lib64/python3.9/asyncio/streams.py", line 549, in readline
    raise ValueError(e.args[0])
ValueError: Separator is not found, and chunk exceed the limit
WARNING  Retrying execution failure 2 of: ansible-playbook --inventory /root/.cache/molecule/zabbix/zabbix_server/inventory --skip-tags molecule-notest,notest /root/devel/collections/ansible_collections/community/zabbix/molecule/zabbix_server/converge.yml
CRITICAL Ansible return code was 2, command was: ['ansible-playbook', '--inventory', '/root/.cache/molecule/zabbix/zabbix_server/inventory', '--skip-tags', 'molecule-notest,notest', '/root/devel/collections/ansible_collections/community/zabbix/molecule/zabbix_server/converge.yml']

[loricvdt]

Hi, I think we are having the same issue with Postgres 15 (Debian 12):

TASK [community.zabbix.zabbix_server : PostgreSQL | Create schema] *************
fatal: [zabbix-server]: FAILED! => {"changed": true, "cmd": "set -euxo pipefail\nFILE=server.sql\ncd /usr/share/zabbix-sql-scripts/postgresql\nif [ -f ${FILE}.gz ]\n  then zcat ${FILE}.gz > /tmp/create.sql\nelse\n  cp ${FILE} /tmp/create.sql\nfi\ncat /tmp/create.sql | psql -h 'localhost' -U 'zabbix-server' -d 'zabbix-server' -p '5432'\ntouch /etc/zabbix/schema.done\nrm -f /tmp/create.sql\n", "delta": "0:00:00.183380", "end": "2023-09-14 14:29:18.268812", "msg": "non-zero return code", "rc": 2, "start": "2023-09-14 14:29:18.085432", "stderr": "+ FILE=server.sql\n+ cd /usr/share/zabbix-sql-scripts/postgresql\n+ '[' -f server.sql.gz ']'\n+ zcat server.sql.gz\n+ cat /tmp/create.sql\n+ psql -h localhost -U zabbix-server -d zabbix-server -p 5432\npsql: error: connection to server at \"localhost\" (127.0.0.1), port 5432 failed: FATAL:  password authentication failed for user \"zabbix-server\"\nconnection to server at \"localhost\" (127.0.0.1), port 5432 failed: FATAL:  password authentication failed for user \"zabbix-server\"", "stderr_lines": ["+ FILE=server.sql", "+ cd /usr/share/zabbix-sql-scripts/postgresql", "+ '[' -f server.sql.gz ']'", "+ zcat server.sql.gz", "+ cat /tmp/create.sql", "+ psql -h localhost -U zabbix-server -d zabbix-server -p 5432", "psql: error: connection to server at \"localhost\" (127.0.0.1), port 5432 failed: FATAL:  password authentication failed for user \"zabbix-server\"", "connection to server at \"localhost\" (127.0.0.1), port 5432 failed: FATAL:  password authentication failed for user \"zabbix-server\""], "stdout": "", "stdout_lines": []}

We believe this is due to the permission changes of version 15 (https://www.postgresql.org/docs/release/15.0/) so we implemented a rough fix for our case (by copying part of the role and running it beforehand) following this: https://www.cybertec-postgresql.com/en/error-permission-denied-schema-public/

- name: "PostgreSQL | Delegated"
  become: true
  become_user: postgres
  delegate_to: "{{ delegated_dbhost }}"
  when:
    - zabbix_server_database_creation
    - zabbix_server_pgsql_login_host is not defined
  block:
    - name: "PostgreSQL | Delegated | Create database"
      community.postgresql.postgresql_db:
        name: "{{ zabbix_server_dbname }}"
        port: "{{ zabbix_server_dbport }}"
        state: present
      notify: Restart PostgreSQL
    - name: "PostgreSQL | Delegated | Create database user"
      community.postgresql.postgresql_user:
        db: "{{ zabbix_server_dbname }}"
        name: "{{ zabbix_server_dbuser }}"
        password: "md5{{ (zabbix_server_dbpassword + zabbix_server_dbuser) | hash('md5') }}"
        port: "{{ zabbix_server_dbport }}"
        priv: ALL
        state: present
        encrypted: true
      notify: Restart PostgreSQL
    - name: "PostgreSQL | Delegated | Create timescaledb extension"
      community.postgresql.postgresql_ext:
        db: "{{ zabbix_server_dbname }}"
        name: timescaledb
      when: zabbix_server_database_timescaledb
      notify: Restart PostgreSQL
    # This is the fix!
    - name: "Fix Permissions for public schema of zabbix-db user on zabbix-database"
      community.postgresql.postgresql_privs:
        db: "{{ zabbix_server_dbname }}"
        privs: ALL
        type: schema
        objs: public
        role: "{{ zabbix_server_dbuser }}"
      notify: Restart PostgreSQL

We also edited a little the hba entries of Postgres (unsure if that is required for the fix to work):

- name: Install PostgreSQL
  ansible.builtin.include_role:
    name: geerlingguy.postgresql
  vars:
    postgresql_hba_entries:
      - { type: local, database: all, user: postgres, auth_method: peer }
      - { type: host, database: all, user: all, address: '127.0.0.1/32', auth_method: md5 }
      - { type: host, database: all, user: all, address: '::1/128', auth_method: md5 }
      - { type: local, database: all, user: zabbix-server, auth_method: md5 }
      # - { type: local, database: all, user: all, auth_method: peer }

Hope this extra information helps

[raketick]

    - name: "Fix Permissions for public schema of zabbix-db user on zabbix-database"
      community.postgresql.postgresql_privs:
        db: "{{ zabbix_server_dbname }}"
        privs: ALL
        type: schema
        objs: public
        role: "{{ zabbix_server_dbuser }}"

Saved my day. Same issue Debian 12, Postgres 15. THX.

[pravi]

Another option would be to make zabbix-server an owner of zabbix-server database.

So I added,

- name: Create a new database with name zabbix-server
  become: yes
  become_user: postgres
  community.postgresql.postgresql_db:
    name: zabbix-server
    owner: zabbix-server

also created the user in the same way for #1074.

- name: Connect to zabbix-server database, create zabbix-server user
  become: yes
  become_user: postgres
  community.postgresql.postgresql_user:
    db: zabbix-server
    name: zabbix-server
    password: <use ansible-vault>

and further, copied the zabbix_server role and modified zabbix_server/tasks/postgresql.yml and commented out password field from create database user which was forcing md5 (so would keep the default scram-sha-256 hash from the previous user creation).

With these changes, I can login to zabbix web.

[chladic]

Can fix from @loricvdt be implemented into collection please ? So it can be used with psql 15 and higher

[pyrodie18]

@chladic there is not currently a PR in for this. If you'd like to create way it would be greatly appreciated and we can take a look at it.