Bug 923614
Summary: | procedures are owned by postgres instead of engine user | ||||||||
---|---|---|---|---|---|---|---|---|---|
Product: | Red Hat Enterprise Virtualization Manager | Reporter: | Pavel Zhukov <pzhukov> | ||||||
Component: | ovirt-engine | Assignee: | Eli Mesika <emesika> | ||||||
Status: | CLOSED ERRATA | QA Contact: | Ido Begun <ibegun> | ||||||
Severity: | high | Docs Contact: | |||||||
Priority: | urgent | ||||||||
Version: | 3.1.3 | CC: | acathrow, dornelas, dyasny, iheim, jbiddle, lpeer, mhuth, mkalinin, nobody, oramraz, pstehlik, Rhev-m-bugs, sgrinber, yeylon, ykaul, yzaslavs | ||||||
Target Milestone: | --- | Keywords: | Patch | ||||||
Target Release: | 3.2.0 | ||||||||
Hardware: | All | ||||||||
OS: | Linux | ||||||||
Whiteboard: | infra | ||||||||
Fixed In Version: | sf13 | Doc Type: | Bug Fix | ||||||
Doc Text: |
Previously, the 'restore database' function did not check if objects were owned by the user 'engine'. Consequently, incorrect database object privileges would cause errors during upgrades. Now, object ownership is checked and fixed if necessary and as a result databases that were exported with postgres owner privileges can be correctly restored and set with engine privileges.
|
Story Points: | --- | ||||||
Clone Of: | Environment: | ||||||||
Last Closed: | 2013-06-10 21:37:37 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: | |||||||||
Attachments: |
|
Description
Pavel Zhukov
2013-03-20 08:12:07 UTC
please specify the command being used for restore in step 5 of the bug description Had also tested that using the backup/sh / restore.sh utilities : /backup.sh -d 923614 -u engine -l ~/tmp -f 923614.sql then ./restore.sh -u engine -d 923614 -r -f ~/tmp/923614.sql This created all objects with engine credentials Please check that 1) You use backup.sh with -u engine 2) You are using restore.sh to restore your data 3) You use restore.sh with -u engine Ugh, please ignore comments #4 to #7, I wish I could delete them, but bugzilla doesn't allow me to. These comments were not taken from a freshly updated system because I had been tinkering with it prior to generating these results. I have now redone the upgrade so the system is completely fresh. Ok, so here goes, running the backup script... # ./backup.sh -d engine -u engine -l ~/dbbackups ... Backup of database engine to /root/dbbackups/engine_Mon_Mar_25_06:45:58_EST_2013.sql completed. # grep 'OWNER TO engine' engine_Mon_Mar_25_06\:45\:58_EST_2013.sql # ... nothing is set to be owned by 'engine'! I've attached the engine_Mon_Mar_25_06\:45\:58_EST_2013.sql file. So restoring the DB, there is no way to know to change the ownership of the objects to 'engine'. # dropdb -U engine engine # createdb -U engine engine .# /restore.sh -u engine -d engine -r -f ~/dbbackups/engine_Mon_Mar_25_06\:45\:58_EST_2013.sql engine=# \l+ List of databases Name | Owner | Encoding | Collation | Ctype | Access privileges | Size | Tablespace | Description -----------+----------+----------+-------------+-------------+-----------------------+----------+------------+--------------------------- engine | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 10142 kB | pg_default | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 5510 kB | 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 (4 rows) engine=# select c.relname, r.rolname from pg_class c join pg_roles r on r.oid = c.relowner join pg_namespace n on n.oid = c.relnamespace where c.relkind in ('r','v','S') and n.nspname = 'public'; relname | rolname -----------------------------------------------+---------- storage_pool | postgres vds_groups | postgres vds_static | postgres gluster_volume_access_protocols | postgres vm_templates_view | postgres business_entity_snapshot | postgres ... [root@gsslab-24-184 dbbackups]# rpm -qa | grep rhevm | sort quartz-rhevm-1.8.3-5.noarch rhevm-3.1.0-50.el6ev.noarch rhevm-backend-3.1.0-50.el6ev.noarch rhevm-cli-3.1.1.2-1.el6ev.noarch rhevm-config-3.1.0-50.el6ev.noarch rhevm-dbscripts-3.1.0-50.el6ev.noarch HTH, Mark Created attachment 715686 [details]
File resulting from ./backup.sh -d engine -u engine -l ~/dbbackups on freshly upgraded system from RHEVM3.0 to 3.1
Created attachment 715722 [details]
Sets ownership of DB objects to engine
[root@dbviewer31 dbscripts]# ./restore.sh -u postgres -d engine -r -f ~/rhevm3.0to3.1.sql
DROP DATABASE
Restore of database engine from /root/rhevm3.0to3.1.sql started...
SET
...
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
Restore of database engine from /root/rhevm3.0to3.1.sql completed.
Changing ownership of objects in database 'engine' to owner 'engine' started...
ALTER TABLE
ALTER TABLE
ALTER TABLE
...
ALTER TABLE
ALTER TABLE
ALTER TABLE
Changing ownership of objects in database 'engine' to owner 'engine' completed.
Should I submit the patch to gerrit as well? Here are some notes from trying to restore a DB on RHEVM3.1 where the DB was from a RHEVM system that was upgraded from 3.0 to 3.1: # dropdb -U postgres engine # createdb -U postgres engine # psql -U postgres engine psql (8.4.13) Type "help" for help. engine=# alter database engine owner to engine; ALTER DATABASE # ./restore.sh -u engine -d engine -f ~/rhevm3.0to3.1.sql Database engine exists, please use -r to force removing it. # ./restore.sh -u engine -d engine -r -f ~/rhevm3.0to3.1.sql ERROR: cannot drop the currently open database Restore of database engine from /root/rhevm3.0to3.1.sql started... SET SET SET SET SET SET psql:/root/rhevm3.0to3.1.sql:19: ERROR: database "engine" already exists psql:/root/rhevm3.0to3.1.sql:22: ERROR: must be member of role "postgres" psql (8.4.13) You are now connected to database "engine". SET SET SET SET SET SET CREATE LANGUAGE psql:/root/rhevm3.0to3.1.sql:41: ERROR: must be member of role "postgres" SET CREATE TYPE psql:/root/rhevm3.0to3.1.sql:60: ERROR: must be member of role "postgres" CREATE TYPE psql:/root/rhevm3.0to3.1.sql:73: ERROR: must be member of role "postgres" CREATE TYPE psql:/root/rhevm3.0to3.1.sql:87: ERROR: must be member of role "postgres" CREATE TYPE psql:/root/rhevm3.0to3.1.sql:108: ERROR: must be member of role "postgres" CREATE TYPE psql:/root/rhevm3.0to3.1.sql:130: ERROR: must be member of role "postgres" ... And the restore fails. But using ./restore -u postgres and my patch - the restore works and I can access the RHEVM WebUI. HTH, Mark my conclusion is that the original database that was used was created with the postgres user.
Please provide the following on the original database
> psql -U engine -l
IMHO this BZ had nothing to do with backup/restore.
the scenario is simple
1) The DB was created at first with user postgres
2) The DB was backed up (user postgres owns all objects)
3) The user was restored from file with -u engine, but all objects already owned by postgres and therefore are restored as such.
The backup script honours the user given in the -u argument and pass it to the the underlying pg_dump utility as is.
Please double check and close the bug as NOTABUG if you find out that the original DB has a postgres owner since we are installing officially this DB with the engine user
Hi Eli, Thanks for the feedback. The bug here is that if you using the backup and restore scripts to backup a RHEVM3.1 DB that was from a system that was initially upgraded from 3.0 to 3.1 and then try to restore that 3.1 DB on a fresh 3.1 system, then you can't access the WebUI because the objects are owned by postgres and not engine and a permission denied exceptioon is encountered. root cause org.postgresql.util.PSQLException: ERROR: permission denied for relation business_entity_snapshot Where: PL/pgSQL function "get_all_commands" line 2 at RETURN QUERY org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062) org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795) org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479) org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367) org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271) org.jboss.jca.adapters.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:107) org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462) org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:644) org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587) org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:637) org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:666) org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:706) org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresSimpleJdbcCall.executeCallInternal(PostgresDbEngineDialect.java:155) org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresSimpleJdbcCall.doExecute(PostgresDbEngineDialect.java:121) org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:181) org.ovirt.engine.core.dal.dbbroker.SimpleJdbcCallsHandler.executeImpl(SimpleJdbcCallsHandler.java:124) org.ovirt.engine.core.dal.dbbroker.SimpleJdbcCallsHandler.executeReadAndReturnMap(SimpleJdbcCallsHandler.java:75) org.ovirt.engine.core.dal.dbbroker.SimpleJdbcCallsHandler.executeReadList(SimpleJdbcCallsHandler.java:66) org.ovirt.engine.core.dao.BusinessEntitySnapshotDAODbFacadeImpl.getAllCommands(BusinessEntitySnapshotDAODbFacadeImpl.java:91) org.ovirt.engine.core.bll.Backend.compensate(Backend.java:263) org.ovirt.engine.core.bll.Backend.Initialize(Backend.java:168) org.ovirt.engine.core.bll.Backend.create(Backend.java:119) This is the problem the customer faced and the problem we have found in restoring some RHEVM3.1 databases when troubleshooting them. The backup file is created with 'OWNED BY postgres' in it, regardless of whether you ./backup.sh -u engine or -u postgres and the objects are owned by postgres regardless of whether you ./restore -u engine or -u postgres. It seems the restore.sh script on RHEVM3.1 should make sure that the objects are owned by engine and not postgres otherwise the WebUI may not be accessible. That's the problem I've been trying to fix. HTH, Mark This is the output from the psql -U postgres -l taken on the RHEVM3.1 that was upgraded from RHEVM3.0: [root@gsslab-24-184 ~]# psql -U postgres -l List of databases Name | Owner | Encoding | Collation | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- engine | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres (4 rows) It still seems like a bug in restore.sh (and try telling a customer that it isn't) when you use backup.sh on one RHEVM3.1 system (upgraded from 3.0), and then try to use restore.sh on *another* RHEVM3.1 system (that wasn't upgraded) and you can't access the WebUI because of the permission denied error. Mark (In reply to comment #14) > This is the output from the psql -U postgres -l taken on the RHEVM3.1 that > was upgraded from RHEVM3.0: > > [root@gsslab-24-184 ~]# psql -U postgres -l > List of databases > Name | Owner | Encoding | Collation | Ctype | Access > privileges > -----------+----------+----------+-------------+-------------+--------------- > -------- > engine | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | > postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | > template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres > : > postgres=CTc/postgres > template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres > : > postgres=CTc/postgres > (4 rows) > > It still seems like a bug in restore.sh (and try telling a customer that it > isn't) when you use backup.sh on one RHEVM3.1 system (upgraded from 3.0), > and then try to use restore.sh on *another* RHEVM3.1 system (that wasn't > upgraded) and you can't access the WebUI because of the permission denied > error. > > Mark OK, now problem is clear and again it is not a backup/restore problem I think that pore accurate is to describe it as upgrade problem. 1) We had at first (3.0) all objects owned by user postgres 2) We had moved from 3.1 to DB objects owned by user engine 3) We should provide an upgrade script for the 3.0->3.1 transition that will transform all engine DB objects from user postgres to user engine. Is that agreed ? Another option is to provide another argument -o to the restore script that will change the owner in the backup sql using sed/awk keep in mind that the user given by -u to the restore script is used as the user connecting to the database (the session user) and not the owner recorded at the file at the time DB was backed-up Hi Eli, I'm not sure it is an upgrade problem because the RHEVM3.1 that was upgraded from 3.0 to 3.1 works fine with the objects owned by postgres. Its only when you restore that DB to another 3.1 system that wasn't upgraded does the problem manifest itself. So yes, a -o switch to restore.sh could be one option. My patch could be another :) Cheers, Mark (In reply to comment #17) > Hi Eli, > > I'm not sure it is an upgrade problem because the RHEVM3.1 that was upgraded > from 3.0 to 3.1 works fine with the objects owned by postgres. Its only > when you restore that DB to another 3.1 system that wasn't upgraded does the > problem manifest itself. So yes, a -o switch to restore.sh could be one > option. My patch could be another :) Mark, please submit your patch, it is better than my -o switch suggestion since we may encounter backups that were made with other supported formats like a tar file or custom. So, although my approach will work for this case, it is dependant in the backup format while yours isn't. The only thing I am thinking of now is should we implement the change still in the context of the restore script. i.e. we can still add the switch and let it do your SQL magic to fix the owner... anyway, please submit a patch and make me a reviewer thanks a lot for your kind help in that > > Cheers, > Mark fixed in commit: 066f78a Since the problem described in this bug report should be resolved in a recent advisory, it has been closed with a resolution of ERRATA. For information on the advisory, and where to find the updated files, follow the link below. If the solution does not work for you, open a new bug report. http://rhn.redhat.com/errata/RHSA-2013-0888.html |