Description of problem: I am moving dwh to major version 4_5: https://gerrit.ovirt.org/#/c/ovirt-dwh/+/117134/, https://gerrit.ovirt.org/#/c/ovirt-engine/+/117682/ and as part of the move I created the v4_5 views: a lot of files but most relevant are: https://gerrit.ovirt.org/#/c/ovirt-dwh/+/117134/5/packaging/dbscripts/create_views_4_5.sql https://gerrit.ovirt.org/#/c/ovirt-dwh/+/117134/5/packaging/dbscripts/dbfunc-custom.sh The issue is that the ovirt_engine_history_grafana user does not have access to query the new v4_5 views. The function that GRANT is grantReadOnlyAccessToUser call is in dwh packaging/setup/plugins/ovirt-engine-setup/ovirt-engine-grafana-dwh/provisioning/postgres.py code is in engine packaging/setup/ovirt_engine_setup/engine_common/postgres.py If upgrade, then the command that should have given it permissions is the last one in this function ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO {user} But seems like the GRANT SELECT ON command is called from file: packaging/dbscripts/dbfunc-common.sh: dbfunc_common_restore_permissions() I installed a fresh ost environment and upgrade to the patches, the GRANT command executed on all tables and views except the v4_5 I tried to run engine-setup twice after the 4_5 views are created and same. 1. I deleted these lines from packaging/dbscripts/dbfunc-custom.sh: dbfunc_output "Creating views API 4.4..." dbfunc_psql_die_v --file="${DBFUNC_COMMON_DBSCRIPTS_DIR}/create_views_4_4.sql" > /dev/null dbfunc_output "Creating views API 4.5..." dbfunc_psql_die_v --file="${DBFUNC_COMMON_DBSCRIPTS_DIR}/create_views_4_5.sql" > /dev/null 2. run engine-setup and verify that the 4_4 and 4_5 views are not created. 3. Insert the line from step 1 4. run engine-setup and verify that 4_4 and 4_5 views are created: now the 4_4 views also can't be query by the grafana user. I was able to solve the issue by adding to packaging/dbscripts/create_views_4_5.sql the GRANT commands at the end of the file. but I think we should fix the real issue. Additional info: We found this:https://dba.stackexchange.com/questions/245355/postgres-permission-issues-for-new-views I think maybe the issue is related to the fact that postgres user grant the permissions, and ovirt_engine_history user creates the views https://gerrit.ovirt.org/#/c/ovirt-dwh/+/117134/5/packaging/dbscripts/dbfunc-custom.sh (line 4) https://github.com/oVirt/ovirt-engine/blob/master/packaging/setup/ovirt_engine_setup/engine_common/postgres.py#L746
engine repo: packaging/setup/ovirt_engine_setup/engine_common/postgres.py executes with the postgres user the following statement : (*) ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ovirt_engine_history_grafana; When you are setting ALTER DEFAULT PRIVILEGES for a user it is in the context of a certain role and ONLY if this role creates new objects then the granted user will get the privileges However the (*) statement above is executed by user postgres and therefor translated and stored as ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT SELECT ON TABLES TO ovirt_engine_history_grafana; On the upgrade to 4.5 , the user that executes the upgrade is ovirt_engine_history and this role have no instructions to grant ovirt_engine_history_grafana on new objects. That means that we have to add the following statement before 4.5 new objects are created ALTER DEFAULT PRIVILEGES FOR ROLE ovirt_engine_history IN SCHEMA public GRANT SELECT ON TABLES TO ovirt_engine_history_grafana;
Verified in ovirt-engine-4.5.0.6-0.7.el8ev.noarch ovirt-engine-dwh-4.5.2-1.el8ev.noarch After upgrading from 4.4 to 4.5, Grafana is showing proper data using the v4_5_ views, no permission problems detected.