Non-admin overview of contacts gives SQL error

[quentinsch]

If you view the Overview -> Contacts as a non-admin user it produces a SQL-error with "unknown column".
If you are an admin user this overview works just fine, so the issue is probably related to permissions somehow.

Expected Behavior

As a non admin user I want to see the contacts within my permission group.

Current Behavior

Produces a SQL error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ho.object_id' in 'on clause', query was: SELECT DISTINCT c.contact_name, c.contact_alias, c.contact_email, c.contact_pager, c.contact_notify_service_timeperiod, c.contact_notify_host_timeperiod FROM (SELECT c.contact_id, co.name1 COLLATE latin1_general_ci AS contact, co.name1 AS contact_name, c.alias COLLATE latin1_general_ci AS contact_alias, c.email_address COLLATE latin1_general_ci AS contact_email, c.pager_address AS contact_pager, c.contact_object_id, c.host_notifications_enabled AS contact_has_host_notfications, c.service_notifications_enabled AS contact_has_service_notfications, c.can_submit_commands AS contact_can_submit_commands, c.notify_service_recovery AS contact_notify_service_recovery, c.notify_service_warning AS contact_notify_service_warning, c.notify_service_critical AS contact_notify_service_critical, c.notify_service_unknown AS contact_notify_service_unknown, c.notify_service_flapping AS contact_notify_service_flapping, c.notify_service_downtime AS contact_notify_service_downtime, c.notify_host_recovery AS contact_notify_host_recovery, c.notify_host_down AS contact_notify_host_down, c.notify_host_unreachable AS contact_notify_host_unreachable, c.notify_host_flapping AS contact_notify_host_flapping, c.notify_host_downtime AS contact_notify_host_downtime, ht.alias COLLATE latin1_general_ci AS contact_notify_host_timeperiod, st.alias COLLATE latin1_general_ci AS contact_notify_service_timeperiod FROM icinga_contacts AS c
INNER JOIN icinga_objects AS co ON co.object_id = c.contact_object_id AND co.is_active = 1
LEFT JOIN icinga_timeperiods AS ht ON ht.timeperiod_object_id = c.host_timeperiod_object_id
LEFT JOIN icinga_timeperiods AS st ON st.timeperiod_object_id = c.service_timeperiod_object_id
LEFT JOIN icinga_hostgroup_members AS hgm ON hgm.host_object_id = ho.object_id
LEFT JOIN icinga_hostgroups AS hg ON hg.hostgroup_id = hgm.hostgroup_id
LEFT JOIN icinga_objects AS hgo ON hgo.object_id = hg.hostgroup_object_id AND hgo.is_active = 1 AND hgo.objecttype_id = 3 WHERE ( (hgo.name1 LIKE 'ICT-%') ) UNION ALL SELECT c.contact_id, co.name1 COLLATE latin1_general_ci AS contact, co.name1 AS contact_name, c.alias COLLATE latin1_general_ci AS contact_alias, c.email_address COLLATE latin1_general_ci AS contact_email, c.pager_address AS contact_pager, c.contact_object_id, c.host_notifications_enabled AS contact_has_host_notfications, c.service_notifications_enabled AS contact_has_service_notfications, c.can_submit_commands AS contact_can_submit_commands, c.notify_service_recovery AS contact_notify_service_recovery, c.notify_service_warning AS contact_notify_service_warning, c.notify_service_critical AS contact_notify_service_critical, c.notify_service_unknown AS contact_notify_service_unknown, c.notify_service_flapping AS contact_notify_service_flapping, c.notify_service_downtime AS contact_notify_service_downtime, c.notify_host_recovery AS contact_notify_host_recovery, c.notify_host_down AS contact_notify_host_down, c.notify_host_unreachable AS contact_notify_host_unreachable, c.notify_host_flapping AS contact_notify_host_flapping, c.notify_host_downtime AS contact_notify_host_downtime, ht.alias COLLATE latin1_general_ci AS contact_notify_host_timeperiod, st.alias COLLATE latin1_general_ci AS contact_notify_service_timeperiod FROM icinga_contacts AS c
INNER JOIN icinga_objects AS co ON co.object_id = c.contact_object_id AND co.is_active = 1
LEFT JOIN icinga_service_contacts AS sc ON sc.contact_object_id = c.contact_object_id
LEFT JOIN icinga_services AS s ON s.service_id = sc.service_id
LEFT JOIN icinga_objects AS so ON so.object_id = s.service_object_id AND so.is_active = 1 AND so.objecttype_id = 2
LEFT JOIN icinga_timeperiods AS ht ON ht.timeperiod_object_id = c.host_timeperiod_object_id
LEFT JOIN icinga_timeperiods AS st ON st.timeperiod_object_id = c.service_timeperiod_object_id
LEFT JOIN icinga_hosts AS h ON h.host_object_id = s.host_object_id
LEFT JOIN icinga_objects AS ho ON ho.object_id = h.host_object_id AND ho.is_active = 1
LEFT JOIN icinga_hostgroup_members AS hgm ON hgm.host_object_id = ho.object_id
LEFT JOIN icinga_hostgroups AS hg ON hg.hostgroup_id = hgm.hostgroup_id
LEFT JOIN icinga_objects AS hgo ON hgo.object_id = hg.hostgroup_object_id AND hgo.is_active = 1 AND hgo.objecttype_id = 3 WHERE ( (hgo.name1 LIKE 'ICT-%') ) GROUP BY co.object_id,
c.contact_id,
ht.timeperiod_id,
st.timeperiod_id) AS c ORDER BY c.contact_name ASC LIMIT 25

#0 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement.php(297): Zend_Db_Statement_Pdo->_execute(Array)
#1 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(470): Zend_Db_Statement->execute(Array)
#2 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Select), Array)
#3 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(744): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select), Array)
#4 /usr/share/php/Icinga/Data/Db/DbConnection.php(313): Zend_Db_Adapter_Abstract->fetchRow(Object(Zend_Db_Select))
#5 /usr/share/php/Icinga/Data/SimpleQuery.php(579): Icinga\Data\Db\DbConnection->fetchRow(Object(Icinga\Module\Monitoring\Backend\Ido\Query\ContactQuery))
#6 /usr/share/php/Icinga/Data/SimpleQuery.php(455): Icinga\Data\SimpleQuery->fetchRow()
#7 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/DataView/DataView.php(538): Icinga\Data\SimpleQuery->hasResult()
#8 zend.view:///usr/share/icingaweb2/modules/monitoring/application/views/scripts/list/contacts.phtml(13): Icinga\Module\Monitoring\DataView\DataView->hasResult()
#9 /usr/share/php/Icinga/Web/View.php(259): include(String)
#10 /usr/share/icingaweb2/library/vendor/Zend/View/Abstract.php(877): Icinga\Web\View->_run(String)
#11 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(904): Zend_View_Abstract->render(String)
#12 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(925): Zend_Controller_Action_Helper_ViewRenderer->renderScript(String, NULL)
#13 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(964): Zend_Controller_Action_Helper_ViewRenderer->render()
#14 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/HelperBroker.php(272): Zend_Controller_Action_Helper_ViewRenderer->postDispatch()
#15 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(518): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
#16 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
#17 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#18 /usr/share/php/Icinga/Application/Web.php(300): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#19 /usr/share/php/Icinga/Application/webrouter.php(104): Icinga\Application\Web->dispatch()
#20 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#21 {main}

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ho.object_id' in 'on clause'

#0 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement/Pdo.php(219): PDOStatement->execute(Array)
#1 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement.php(297): Zend_Db_Statement_Pdo->_execute(Array)
#2 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(470): Zend_Db_Statement->execute(Array)
#3 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Select), Array)
#4 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(744): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select), Array)
#5 /usr/share/php/Icinga/Data/Db/DbConnection.php(313): Zend_Db_Adapter_Abstract->fetchRow(Object(Zend_Db_Select))
#6 /usr/share/php/Icinga/Data/SimpleQuery.php(579): Icinga\Data\Db\DbConnection->fetchRow(Object(Icinga\Module\Monitoring\Backend\Ido\Query\ContactQuery))
#7 /usr/share/php/Icinga/Data/SimpleQuery.php(455): Icinga\Data\SimpleQuery->fetchRow()
#8 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/DataView/DataView.php(538): Icinga\Data\SimpleQuery->hasResult()
#9 zend.view:///usr/share/icingaweb2/modules/monitoring/application/views/scripts/list/contacts.phtml(13): Icinga\Module\Monitoring\DataView\DataView->hasResult()
#10 /usr/share/php/Icinga/Web/View.php(259): include(String)
#11 /usr/share/icingaweb2/library/vendor/Zend/View/Abstract.php(877): Icinga\Web\View->_run(String)
#12 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(904): Zend_View_Abstract->render(String)
#13 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(925): Zend_Controller_Action_Helper_ViewRenderer->renderScript(String, NULL)
#14 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(964): Zend_Controller_Action_Helper_ViewRenderer->render()
#15 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/HelperBroker.php(272): Zend_Controller_Action_Helper_ViewRenderer->postDispatch()
#16 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(518): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
#17 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
#18 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#19 /usr/share/php/Icinga/Application/Web.php(300): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#20 /usr/share/php/Icinga/Application/webrouter.php(104): Icinga\Application\Web->dispatch()
#21 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#22 {main}

Possible Solution

Probably a permission issue?

Steps to Reproduce (for bugs)

Use a non-admin user and browse to Overview -> Contacts

Context

Your Environment

• Icinga Web 2 version and modules (System - About): v2.6.1
• Version used (icinga2 --version): v.2.9.1-1
• Operating System and version: Debian 8.11 (Jessie)
• Enabled features (icinga2 feature list): api checker command graphite ido-mysql livestatus mainlog notification perfdata
• Config validation (icinga2 daemon -C): Validated OK

[latuannetnam]

I also have the same issue with user with permission filter "monitoring/filter/objects". If you remove the filter then the function works fine. To temporarily fix that, I modified 2 files as below:

1. /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/HostcontactQuery.php

• function joinHostgroups(): add $this->requireVirtualTable('hosts'); in the top of function

2. /usr/share/icingaweb2/modules/monitoring/application/controllers/ShowController.php

• function contactAction(): comment out line $this->applyRestriction('monitoring/filter/objects', $query);

Hope this will help.

[lippserd]

Hi,

Thanks for the report. Could please try the patch in the related PR?

@latuannetnam Adding $this->requireVirtualTable('hosts'); should be sufficient. Why did you also remove the restriction?

Best,
Eric

[Rolf-Zi]

With this patch I get another error:

You cannot define a correlation name 'hc' more than once

#0 /usr/share/icingaweb2/library/vendor/Zend/Db/Select.php(373): Zend_Db_Select->_join(String, Array, String, Array, NULL)
#1 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/HostcontactQuery.php(149): Zend_Db_Select->joinLeft(Array, String, Array)
#2 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/HostcontactQuery.php(199): Icinga\Module\Monitoring\Backend\Ido\Query\HostcontactQuery->joinHosts()
#3 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/IdoQuery.php(1057): Icinga\Module\Monitoring\Backend\Ido\Query\HostcontactQuery->joinServices()
#4 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/IdoQuery.php(1016): Icinga\Module\Monitoring\Backend\Ido\Query\IdoQuery->joinVirtualTable(String)
#5 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/HostcontactQuery.php(178): Icinga\Module\Monitoring\Backend\Ido\Query\IdoQuery->requireVirtualTable(String)
#6 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/IdoQuery.php(1057): Icinga\Module\Monitoring\Backend\Ido\Query\HostcontactQuery->joinServicegroups()
#7 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/IdoQuery.php(1016): Icinga\Module\Monitoring\Backend\Ido\Query\IdoQuery->joinVirtualTable(String)
#8 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/IdoQuery.php(977): Icinga\Module\Monitoring\Backend\Ido\Query\IdoQuery->requireVirtualTable(String)
#9 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/IdoQuery.php(675): Icinga\Module\Monitoring\Backend\Ido\Query\IdoQuery->requireColumn(String)
#10 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/IdoQuery.php(695): Icinga\Module\Monitoring\Backend\Ido\Query\IdoQuery->requireFilterColumns(Object(Icinga\Data\Filter\FilterMatch))
#11 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/IdoQuery.php(695): Icinga\Module\Monitoring\Backend\Ido\Query\IdoQuery->requireFilterColumns(Object(Icinga\Data\Filter\FilterOr))
#12 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/IdoQuery.php(710): Icinga\Module\Monitoring\Backend\Ido\Query\IdoQuery->requireFilterColumns(Object(Icinga\Data\Filter\FilterOr))
#13 /usr/share/php/Icinga/Data/SimpleQuery.php(274): Icinga\Module\Monitoring\Backend\Ido\Query\IdoQuery->addFilter(Object(Icinga\Data\Filter\FilterOr))
#14 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/ContactQuery.php(70): Icinga\Data\SimpleQuery->applyFilter(Object(Icinga\Data\Filter\FilterOr))
#15 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/DataView/DataView.php(493): Icinga\Module\Monitoring\Backend\Ido\Query\ContactQuery->addFilter(Object(Icinga\Data\Filter\FilterOr))
#16 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/DataView/DataView.php(432): Icinga\Module\Monitoring\DataView\DataView->addFilter(Object(Icinga\Data\Filter\FilterOr))
#17 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Controller.php(91): Icinga\Module\Monitoring\DataView\DataView->applyFilter(Object(Icinga\Data\Filter\FilterOr))
#18 /usr/share/icingaweb2/modules/monitoring/application/controllers/ListController.php(321): Icinga\Module\Monitoring\Controller->applyRestriction(String, Object(Icinga\Module\Monitoring\DataView\Contact))
#19 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(507): Icinga\Module\Monitoring\Controllers\ListController->contactsAction()
#20 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
#21 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#22 /usr/share/php/Icinga/Application/Web.php(300): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#23 /usr/share/php/Icinga/Application/webrouter.php(104): Icinga\Application\Web->dispatch()
#24 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#25 {main}

[lippserd]

@Rolf-Zi Could please share the filter/restriction which is applied here and whether you're using MySQL/MariaDB or PostgreSQL.

[lippserd]

@Rolf-Zi I updated the PR. Could you please test it again?

[Rolf-Zi]

Now it works :-)
thanks