Description of problem: After backup-restore procedure of upgraded 3.0->3.1 environment some procedures are owned by postgres user and cannot be runnable. Version-Release number of selected component (if applicable): rhevm-3.1.0-50.el6ev.noarch How reproducible: 100% Steps to Reproduce: 1. Install RHEV 3,0 2. Upgrade to RHEV 3.1 3. Create backup (https://access.redhat.com/knowledge/docs/en-US/Red_Hat_Enterprise_Virtualization/3.1/html/Administration_Guide/appe-Backups.html#Backing_up_the_engine_database_using_the_backup.sh_script) 4) Install new RHEL6 machine 5) Restore backup 6) Run ovirt-engine Actual results: webadmin unable to start For my system I created engine as superuser for workaround. Additional info: $ cat var/lib/pgsql/data/pg_log/postgresql-Mon.log | grep denied | sort | uniq ERROR: permission denied for language c ERROR: permission denied for relation action_version_map ERROR: permission denied for relation business_entity_snapshot ERROR: permission denied for relation job ERROR: permission denied for relation storage_pool ERROR: permission denied for relation vds ERROR: permission denied for relation vds_groups ERROR: permission denied for relation vm_pool_map root/engine_Wed_Mar_20_07:56:15_CET_2013.sql:ALTER FUNCTION public.updatevmtagsdefaultdisplaytype(v_tag_id uuid, v_vm_id uuid, v_defaultdisplaytype integer) OWNER TO postgres; /root/engine_Wed_Mar_20_07:56:15_CET_2013.sql:ALTER FUNCTION public.updatevmtemplate(v_child_count integer, v_creation_date timestamp with time zone, v_description character varying, v_mem_size_mb integer, v_name character varying, v_num_of_sockets integer, v_cpu_per_socket integer, v_os integer, v_vmt_guid uuid, v_vds_group_id uuid, v_domain character varying, v_num_of_monitors integer, v_allow_console_reconnect boolean, v_status integer, v_usb_policy integer, v_time_zone character varying, v_fail_back boolean, v_is_auto_suspend boolean, v_vm_type integer, v_hypervisor_type integer, v_operation_mode integer, v_nice_level integer, v_default_boot_sequence integer, v_default_display_type integer, v_priority integer, v_auto_startup boolean, v_is_stateless boolean, v_iso_path character varying, v_origin integer, v_initrd_url character varying, v_kernel_url character varying, v_kernel_params character varying, v_quota_id uuid, v_migration_support integer, v_dedicated_vm_for_vds uuid) OWNER TO postgres; /root/engine_Wed_Mar_20_07:56:15_CET_2013.sql:ALTER FUNCTION public.updatevmtemplatestatus(v_vmt_guid uuid, v_status integer) OWNER TO postgres; /root/engine_Wed_Mar_20_07:56:15_CET_2013.sql:ALTER FUNCTION public.uuid_generate_v1() OWNER TO postgres; /root/engine_Wed_Mar_20_07:56:15_CET_2013.sql:ALTER FUNCTION public.uuid_generate_v1mc() OWNER TO postgres; /root/engine_Wed_Mar_20_07:56:15_CET_2013.sql:ALTER FUNCTION public.uuid_generate_v3(namespace uuid, name text) OWNER TO postgres; /root/engine_Wed_Mar_20_07:56:15_CET_2013.sql:ALTER FUNCTION public.uuid_generate_v4() OWNER TO postgres; /root/engine_Wed_Mar_20_07:56:15_CET_2013.sql:ALTER FUNCTION public.uuid_generate_v5(namespace uuid, name text) OWNER TO postgres; /root/engine_Wed_Mar_20_07:56:15_CET_2013.sql:ALTER FUNCTION public.uuid_nil() OWNER TO postgres; /root/engine_Wed_Mar_20_07:56:15_CET_2013.sql:ALTER FUNCTION public.uuid_ns_dns() OWNER TO postgres; /root/engine_Wed_Mar_20_07:56:15_CET_2013.sql:ALTER FUNCTION public.uuid_ns_oid() OWNER TO postgres; /root/engine_Wed_Mar_20_07:56:15_CET_2013.sql:ALTER FUNCTION public.uuid_ns_url() OWNER TO postgres; /root/engine_Wed_Mar_20_07:56:15_CET_2013.sql:ALTER FUNCTION public.uuid_ns_x500() OWNER TO postgres; # grep 'ALTER FUNCTION' /root/engine_Wed_Mar_20_07\:56\:* | grep --color 'OWNER TO postgres' | wc -l 1190
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