Bug 2026358

Summary:	ovirt_engine_history_grafana user is not granted permissions to query new tables
Product:	[oVirt] ovirt-engine-dwh	Reporter:	Aviv Litman <alitman>
Component:	Setup	Assignee:	Eli Mesika <emesika>
Status:	CLOSED CURRENTRELEASE	QA Contact:	Pavel Novotny <pnovotny>
Severity:	high	Docs Contact:
Priority:	high
Version:	4.4.5	CC:	bugs, didi, michal.skrivanek, mperina, sradco
Target Milestone:	ovirt-4.5.0	Flags:	mperina: ovirt-4.5+ sbonazzo: devel_ack+ gdeolive: testing_ack+
Target Release:	4.5.0
Hardware:	Unspecified
OS:	Unspecified
Whiteboard:
Fixed In Version:	ovirt-engine-dwh-4.5.0	Doc Type:	No Doc Update
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2022-05-23 06:21:25 UTC	Type:	Bug
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	Infra	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:
Bug Depends On:
Bug Blocks:	2026362

Description Aviv Litman 2021-11-24 13:00:20 UTC

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

Comment 1 Eli Mesika 2021-12-14 11:42:04 UTC

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;

Comment 4 Pavel Novotny 2022-05-06 22:31:18 UTC

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.