Bug 1034929

Description of problem:

Unfortunately upgrade 3.1-3.2-3.3 fails:
[ ERROR ] Failed to execute stage 'Setup validation': Cannot upgrade the database schema due to wrong ownership of some database entities. Please execute: /usr/share/ovirt-engine/setup/dbutils/changedbowner.sh -s localhost -p 5432 -d engine -f postgres -t engine Using the password of the "postgres" user.

Even executing the command (which successfully finishes) leads me to getting the same error while upgrading the next time.

Looking at the logs I see:

2013-11-26 16:52:59 DEBUG otopi.context context._executeMethod:137 method exception
Traceback (most recent call last):
  File "/usr/lib/python2.6/site-packages/otopi/context.py", line 127, in _executeMethod
    method['method']()
  File "/usr/share/ovirt-engine/setup/bin/../plugins/ovirt-engine-setup/db/schema.py", line 204, in _validation
    self._checkDatabaseOwnership()
  File "/usr/share/ovirt-engine/setup/bin/../plugins/ovirt-engine-setup/db/schema.py", line 150, in _checkDatabaseOwnership
    user=self.environment[osetupcons.DBEnv.USER],
RuntimeError: Cannot upgrade the database schema due to wrong ownership of some database entities.
Please execute: /usr/share/ovirt-engine/setup/dbutils/changedbowner.sh -s localhost -p 5432 -d engine -f postgres -t engine
Using the password of the "postgres" user.
2013-11-26 16:52:59 ERROR otopi.context context._executeMethod:146 Failed to execute stage 'Setup validation': Cannot upgrade the database schema due to wrong ownership of some database entities.
Please execute: /usr/share/ovirt-engine/setup/dbutils/changedbowner.sh -s localhost -p 5432 -d engine -f postgres -t engine
Using the password of the "postgres" user.

Which suggests the ownership rights for DB instance are wrong.
When I run psql "-U postgres -c "\l+"" I get:

postgres=# \l+
                                                                 
        Name          | Owner    | Encoding |  Collation  |    Ctype    |   Access rights       | Size     | Tablespace |           Description           
----------------------+----------+----------+-------------+-------------+-----------------------+----------+------------+---------------------------
 engine               | engine   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 16 MB    | pg_default | 
 ovirt_engine_history | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 69 MB    | pg_default | 
 postgres             | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 5510 kB  | pg_default | 
 rhevmreports         | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 19 MB    | pg_default | 
 template0            | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres           | 5408 kB  | pg_default | 
                                                                        : postgres=CTc/postgres                           
 template1            | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres           | 5510 kB  | pg_default | default template database
                                                                        : postgres=CTc/postgres     

Which should be the correct rights. Indeed running the setup-suggested script has no effect at all (as the rights are correct, running -f engine -t engine does no change as well).
No dirty hacks have been done on this environment and upgrade-wise I used following guidelines (https://home.corp.redhat.com/wiki/virtmanagement/integration/RHEVM_3_3RPMHowTo).

Looking at the code of is24.2 the function _checkDatabaseOwnership contains following SQL 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_toast%%' and
                    rol.rolname != %(user)s
                order by
                    nsp.nspname,
                    cls.relname

Replacing the "rol.rolname != %(user)s" statement with either 'engine' or 'postgres' gives out zero length result set which is ok for the script
to continue with upgrade. Codewise assignment of the values seems fine so either we're assigning wrong values here which is supported by debug log from otopi:

2013-11-26 18:21:28 DEBUG otopi.ovirt_engine_setup.database database.execute:123 Database: 'engine', 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_toast%%' and
                    rol.rolname != %(user)s
                order by
                    nsp.nspname,
                    cls.relname
            ', args: {'user': 'engine'}

However the real issue comes with execution of the function database.py execute:

2013-11-26 18:21:28 DEBUG otopi.ovirt_engine_setup.database database.execute:128 Creating own connection
2013-11-26 18:21:28 DEBUG otopi.ovirt_engine_setup.database database.execute:181 Result: [{'owner': 'postgres', 'object_name': 'tt_temp22', 'object_type': 'TABLE', 'object_schema': 'pg_temp_4'}]

This temporary table though has nothing to do with engine itself it's db-maintained temporary table. This is also why the script mentioned by rhevm-setup
cannot work as postgres temporary tables are always created and owned by postgres as well as deleted. 
(and the script changedbowner.sh covers only ALTER database, not all objects in the database so it won't fix the table ownership).

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

How reproducible:
100 % on env with pg_temp temporary tables (which are randomly created and deleted and owned by postgres for internal db maintenance purposes).

Steps to Reproduce:
Please see detailed description above

Actual results:
Upgrade fails, manual DB edit is needed, changedbowner.sh is useless

Expected results:
Should work just fine