+++ This bug was initially created as a clone of Bug #1570988 +++

Description of problem:

Was not able to successfully upgrade existing engine due to presence of functions from pg extensions not owned by engine user.

## Logs:
https://drive.google.com/open?id=187k5Ko48_-yw5i4xsHVq4YPQnGfN67eR

PGwatch2 ( an external monitoring tool )  uses pg extensions.
It seems that ovirt-engine-setup stops when it checks for functions not owned by 'engine' user.


To resolve the issue it was necessary to:
1) Removing pg_stat extension and relevant functions and extensions. 
        via # drop extension pg_stat_statements; pgstattuple; and uuid-ossp, then re-creating extension uuid-ossp
2) Using pg_restore on db dump from previous upgrade.
3) Run engine-setup for successful upgrade 

Version-Release number of selected component (if applicable):
4.2.3.2

How reproducible:
Haven't tried to reproduce it.

Steps to Reproduce:
1. Install pgwatch2 with relevant pg extensions 
2. run engine-setup for an upgrade
3. Upgrade will fail with '[ ERROR ] Engine database rollback failed: must be owner of function public.pgstattuple'


Actual results:
[ INFO  ] Creating/refreshing Engine database schema
[ ERROR ] schema.sh:  drop function if exists public.upsertkdumpstatusforip(character varying, character varying, character varying) cascade;
[ ERROR ] Failed to execute stage 'Misc configuration': Engine schema refresh failed
[ INFO  ] Yum Performing yum transaction rollback
[WARNING] Rollback of DWH database postponed to Stage "Clean up"
[ INFO  ] Rolling back database schema
[ INFO  ] Clearing Engine database engine
[ ERROR ] Engine database rollback failed: must be owner of function public.pgstattuple

Expected results:
List warning that pg_extensions are loaded and are required to be removed, additionally listing the name of extension that the function belongs to that needs to be removed would be helpful and then fail.

Additional info:
PGwatch2 ( an external monitoring tool )  uses pg extensions.
It seems that ovirt-engine-setup stops when it checks for functions not owned by 'engine' user.  Can we assume this is intended behavior in order not to break backup/restore?


## Logs: (upgrade log, dump, and log collector of engine)
https://drive.google.com/open?id=187k5Ko48_-yw5i4xsHVq4YPQnGfN67eR

--- Additional comment from Yaniv Kaul on 2018-04-24 02:26:58 EDT ---

#Cause:
PGwatch2 ( an external monitoring tool )  uses some pg extensions.
It seems that ovirt-engine-setup stops when it checks for functions not owned by 'engine' user.[*]

#Steps to resolve
Roy was able to resolve the issue by:
1) Removing pg_stat extension and relevant functions and extensions. 
        via # drop extension pg_stat_statements; pgstattuple; and uuid-ossp, then re-creating extension uuid-ossp
2) Using pg_restore on db dump from previous upgrade.
3) Run engine-setup for upgrade 
 
#Status:
- Upgrade successful.

--- Additional comment from Eli Mesika on 2018-04-25 06:37:39 EDT ---

(In reply to Yaniv Kaul from comment #1)
> #Cause:
> PGwatch2 ( an external monitoring tool )  uses some pg extensions.
> It seems that ovirt-engine-setup stops when it checks for functions not
> owned by 'engine' user.[*]

This is not the reason, the real reason is that the extension is installing its functions with the "engine" user on the "public" schema , now , in the upgrade process we drop and re-create all functions in the public schema that are owned by "engine" but an attempt to drop the extension functions will fail since the functions are depending on the extension and the engine user can not drop the extension since only db admin role is allowed to do so.

> 
> #Steps to resolve
> Roy was able to resolve the issue by:
> 1) Removing pg_stat extension and relevant functions and extensions. 
>         via # drop extension pg_stat_statements; pgstattuple; and uuid-ossp,
> then re-creating extension uuid-ossp

This does not resolve the problem, this is a bypass, you will still have the same issue on any extension that is installed into the engine database.

I had pushed a draft [1]  that solves this issue by skipping drop of extension functions when the engine database is upgraded, I think that this is the right approach to solve the problem and still allow any other PG extension to be installed on the database 

Please consider re-opening this BZ and test my patch as the solution to this BZ.


[1] https://gerrit.ovirt.org/#/c/90614/

> 2) Using pg_restore on db dump from previous upgrade.
> 3) Run engine-setup for upgrade 
>  
> #Status:
> - Upgrade successful.

--- Additional comment from Martin Perina on 2018-05-02 05:43:13 EDT ---

Reopening and adding details: during the upgrade we are trying to remove all functions from public schema except uuid_ossp extension. But we should modify this code to not remove functions from any PostgreSQL extensions.

--- Additional comment from Lucie Leistnerova on 2018-06-04 08:16:29 EDT ---

I installed pg_stat_statements, plpythonu and pgstattuple extensions to engine database.
By the upgrade, output of generate_drop_all_functions_syntax doesn't contain functions from extensions. So that seems alright. But it tries to drop view
from generate_drop_all_views_syntax:

DROP VIEW if exists pg_stat_statements CASCADE;

ERROR:  cannot drop view pg_stat_statements because extension pg_stat_statements requires it
HINT:  You can drop extension pg_stat_statements instead.


tested in ovirt-engine-setup-4.2.4.1-0.1.el7.noarch

--- Additional comment from Ondra Machacek on 2018-07-24 05:29 EDT ---

I've tried to verify as follows:

 1) install ovirt-engine-4.2.5.1-0.1.el7ev.noarch 
 2) run engine-setup
 3) On engine db run:
      CREATE EXTENSION pg_stat_statements;
      CREATE EXTENSION pgstattuple;
      CREATE EXTENSION plpythonu;
 4) run engine-setup again

I got:

2018-07-24 10:52:51,591+0200 DEBUG otopi.ovirt_engine_setup.engine_common.database database.execute:242 Database: 'None', Statement: '
                select
                    nsp.nspname as object_schema,
                    cls.relname as object_name,
                    rol.rolname as owner,
                    case cls.relkind
                        when 'r' then 'TABLE'
                        when 'i' then 'INDEX'
                        when 'S' then 'SEQUENCE'
                        when 'v' then 'VIEW'
                        when 'c' then 'TYPE'
                    else
                        cls.relkind::text
                    end as object_type
                from
                    pg_class cls join
                    pg_roles rol on rol.oid = cls.relowner join
                    pg_namespace nsp on nsp.oid = cls.relnamespace
                where
                    nsp.nspname not in ('information_schema', 'pg_catalog') and
                    nsp.nspname not like 'pg_%%' and
                    rol.rolname != %(user)s
                order by
                    nsp.nspname,
                    cls.relname
            ', args: {'user': 'engine'}
2018-07-24 10:52:51,591+0200 DEBUG otopi.ovirt_engine_setup.engine_common.database database.execute:247 Creating own connection
2018-07-24 10:52:51,605+0200 DEBUG otopi.ovirt_engine_setup.engine_common.database database.execute:292 Result: [{'owner': 'postgres', 'object_name': 'pg_stat_statements', 'object_type': 'VIEW', 'object_schema':
 'public'}]
2018-07-24 10:52:51,606+0200 DEBUG otopi.context context._executeMethod:143 method exception
Traceback (most recent call last):
  File "/usr/lib/python2.7/site-packages/otopi/context.py", line 133, in _executeMethod
    method['method']()
  File "/usr/share/ovirt-engine/setup/bin/../plugins/ovirt-engine-setup/ovirt-engine/db/schema.py", line 341, in _validation
    self._checkDatabaseOwnership()
  File "/usr/share/ovirt-engine/setup/bin/../plugins/ovirt-engine-setup/ovirt-engine/db/schema.py", line 245, in _checkDatabaseOwnership
    'Cannot upgrade the Engine database schema due to wrong '
RuntimeError: Cannot upgrade the Engine database schema due to wrong ownership of some database entities.

--- Additional comment from Eli Mesika on 2018-07-24 11:37:51 EDT ---

please provide the result of the following query :


select
                    nsp.nspname as object_schema,
                    cls.relname as object_name,
                    rol.rolname as owner,
                    case cls.relkind
                        when 'r' then 'TABLE'
                        when 'i' then 'INDEX'
                        when 'S' then 'SEQUENCE'
                        when 'v' then 'VIEW'
                        when 'c' then 'TYPE'
                    else
                        cls.relkind::text
                    end as object_type
                from
                    pg_class cls join
                    pg_roles rol on rol.oid = cls.relowner join
                    pg_namespace nsp on nsp.oid = cls.relnamespace
                where
                    nsp.nspname not in ('information_schema', 'pg_catalog') and
                    nsp.nspname not like 'pg_%%' and
                    rol.rolname != 'engine'
                order by
                    nsp.nspname,
                    cls.relname;

--- Additional comment from Ondra Machacek on 2018-07-24 12:20:06 EDT ---

engine=# select
    nsp.nspname as object_schema,
    cls.relname as object_name,
    rol.rolname as owner,
    case cls.relkind
when 'r' then 'TABLE'
when 'i' then 'INDEX'
when 'S' then 'SEQUENCE'
when 'v' then 'VIEW'
when 'c' then 'TYPE'
    else
cls.relkind::text
    end as object_type
from
    pg_class cls join
    pg_roles rol on rol.oid = cls.relowner join
    pg_namespace nsp on nsp.oid = cls.relnamespace
where
    nsp.nspname not in ('information_schema', 'pg_catalog') and
    nsp.nspname not like 'pg_%%' and
    rol.rolname != 'engine'
order by
    nsp.nspname,
    cls.relname;
 object_schema |    object_name     |  owner   | object_type 
---------------+--------------------+----------+-------------
 public        | pg_stat_statements | postgres | VIEW
(1 row)

--- Additional comment from Lucie Leistnerova on 2018-08-09 05:19:52 EDT ---

engine-setup ran successfully with pg_stat_statements, plpythonu and pgstattuple extensions. No pg_* tables or views are dropped.

verified in ovirt-engine-4.2.6.1-0.0.master.20180808134452.git7cf7f6b.el7.noarch