Bug 1570988 - Don't try to remove functions, views or tables in public schema installed by PostgreSQL extensions
Summary: Don't try to remove functions, views or tables in public schema installed by ...
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: ovirt-engine
Classification: oVirt
Component: Database.Core
Version: 4.2.3.2
Hardware: Unspecified
OS: Unspecified
unspecified
medium
Target Milestone: ovirt-4.2.6
: 4.2.6.1
Assignee: Eli Mesika
QA Contact: Lucie Leistnerova
URL:
Whiteboard:
Depends On:
Blocks: 1618981
TreeView+ depends on / blocked
 
Reported: 2018-04-23 22:24 UTC by mlehrer
Modified: 2018-09-03 15:08 UTC (History)
6 users (show)

Fixed In Version: ovirt-engine-4.2.6.1
Doc Type: If docs needed, set a value
Doc Text:
Clone Of:
: 1618981 (view as bug list)
Environment:
Last Closed: 2018-09-03 15:08:50 UTC
oVirt Team: Infra
rule-engine: ovirt-4.2+
lsvaty: testing_ack+


Attachments (Terms of Use)
engine setup log (504.27 KB, text/plain)
2018-07-24 09:29 UTC, Ondra Machacek
no flags Details


Links
System ID Private Priority Status Summary Last Updated
oVirt gerrit 90614 0 'None' MERGED db: skip dropping extension functions 2020-08-06 08:18:30 UTC
oVirt gerrit 91079 0 'None' MERGED db: skip dropping extension functions 2020-08-06 08:18:30 UTC
oVirt gerrit 92137 0 'None' MERGED db: do not drop extension views 2020-08-06 08:18:30 UTC
oVirt gerrit 92796 0 'None' MERGED db: do not drop extension views 2020-08-06 08:18:30 UTC
oVirt gerrit 93318 0 'None' MERGED db: do not drop extension objects 2020-08-06 08:18:30 UTC
oVirt gerrit 93339 0 'None' ABANDONED db: do not drop extension objects 2020-08-06 08:18:29 UTC
oVirt gerrit 93499 0 'None' MERGED db: do not drop extension objects 2020-08-06 08:18:29 UTC

Description mlehrer 2018-04-23 22:24:57 UTC
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

Comment 1 Yaniv Kaul 2018-04-24 06:26:58 UTC
#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.

Comment 2 Eli Mesika 2018-04-25 10:37:39 UTC
(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.

Comment 3 Martin Perina 2018-05-02 09:43:13 UTC
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.

Comment 4 Lucie Leistnerova 2018-06-04 12:16:29 UTC
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

Comment 5 Ondra Machacek 2018-07-24 09:29:07 UTC
Created attachment 1470241 [details]
engine setup log

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.

Comment 6 Eli Mesika 2018-07-24 15:37:51 UTC
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;

Comment 7 Ondra Machacek 2018-07-24 16:20:06 UTC
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)

Comment 8 Lucie Leistnerova 2018-08-09 09:19:52 UTC
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


Note You need to log in before you can comment on or make changes to this bug.