Description of problem: Bug #1368589 requires some change in backup/ restore too. Addressing them here.
Changes in behavior of engine-backup --mode=restore with the currently pending changes: Per db dump, if the dump contains GRANT statements for extra users (meaning, other than the owner, "postgres" and "PUBLIC"), and if --provision-*db and --restore-permissions are passed, and db host is localhost (not remote), then these extra users are created (if they do not already exist), and their passwords are set to random ones. Later on, when actually restoring the dump, the extra GRANT statements will succeed. Reproduction/Verification flow: 1. setup engine+dwh, accept automatic provisioning. 2. Add extra users and grants, e.g. using [1](3.6) or [2](4.0). 3. backup using 'engine-backup --mode=backup --file=file1 --log=log1' 4. Install engine+dwh on a new clean machine and copy there file1 5. on the new machine: engine-backup --mode=restore --file=file1 --log=restorelog1 --provision-db --provision-dwh-db --restore-permissions 6. engine-setup [1] https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Virtualization/3.6/html/Reports_and_Data_Warehouse_Guide/sect-History_Database.html#Allowing_Read_Only_Access_to_the_History_Database [2] https://access.redhat.com/documentation/en/red-hat-virtualization/4.0/paged/data-warehouse-guide/22-history-database#Allowing_Read_Only_Access_to_the_History_Database Step (4.) can be partially simulated using the same machine by doing: A. engine-cleanup B. service postgresql stop C. rm -rf /var/lib/pgsql/data In reproduction (with a bad version during restore), step (5.) will fail while running grant commands. In verification (with a good version during restore), all will succeed. backup mode was not changed. In particular: 1. The above works well also when restoring on 4.0 a backup taken on 3.6. 2. Passwords for the extra users are not saved inside the backup, thus can't be restored, so are set to random ones. Also note: The fix for this bug is not enough to allow access using the extra users. It's only enough so that restore works. To re-allow access, these steps are needed too: 1. edit /var/lib/pgsql/data/pg_hba.conf : Find the lines containing 'ovirt_engine_history', there should be two such lines duplicate them in the duplicates, replace the second occurrence of ovirt_engine_history with the extra user. E.g. if the extra user is 'rouser', do this: sed -i '/ovirt_engine_history *ovirt_engine_history/p; s/^\([^ ]* *ovirt_engine_history *\)ovirt_engine_history/\1rouser/' /var/lib/pgsql/data/pg_hba.conf 2. Grant the user to connect, e.g.: su - postgres -c 'psql -U postgres -c "GRANT CONNECT ON DATABASE ovirt_engine_history TO rouser;"' 3. change its password, e.g.: su - postgres -c 'psql -U postgres -c "ALTER role rouser with login encrypted password '\''mypassword'\'';"' 4. service postgresql restart Then this should work: psql -h localhost -U rouser ovirt_engine_history (input mypassword in the prompt) ovirt_engine_history=> select * from schema_version;
(In reply to Yedidyah Bar David from comment #1) > 2. Passwords for the extra users are not saved inside the backup, thus can't > be restored, so are set to random ones. Does the restore say that? > > Also note: > > The fix for this bug is not enough to allow access using the extra users. > It's only enough so that restore works. To re-allow access, these steps are > needed too: > > 1. edit /var/lib/pgsql/data/pg_hba.conf : > Find the lines containing 'ovirt_engine_history', there should be two such > lines > duplicate them > in the duplicates, replace the second occurrence of ovirt_engine_history > with the extra user. > E.g. if the extra user is 'rouser', do this: > > sed -i '/ovirt_engine_history *ovirt_engine_history/p; s/^\([^ ]* > *ovirt_engine_history *\)ovirt_engine_history/\1rouser/' > /var/lib/pgsql/data/pg_hba.conf > > 2. Grant the user to connect, e.g.: > > su - postgres -c 'psql -U postgres -c "GRANT CONNECT ON DATABASE > ovirt_engine_history TO rouser;"' > > 3. change its password, e.g.: > > su - postgres -c 'psql -U postgres -c "ALTER role rouser with login > encrypted password '\''mypassword'\'';"' > > 4. service postgresql restart > > Then this should work: > > psql -h localhost -U rouser ovirt_engine_history > (input mypassword in the prompt) > ovirt_engine_history=> select * from schema_version; Did you link to any wiki saying you need to do these steps?
(In reply to Yaniv Dary from comment #2) > (In reply to Yedidyah Bar David from comment #1) > > 2. Passwords for the extra users are not saved inside the backup, thus can't > > be restored, so are set to random ones. > > Does the restore say that? No, moving back to POST for this. If you want the passwords to be saved too, so that they can be restored, please open another bug (IMO). > > > > > Also note: > > > > The fix for this bug is not enough to allow access using the extra users. > > It's only enough so that restore works. To re-allow access, these steps are > > needed too: > > > > 1. edit /var/lib/pgsql/data/pg_hba.conf : > > Find the lines containing 'ovirt_engine_history', there should be two such > > lines > > duplicate them > > in the duplicates, replace the second occurrence of ovirt_engine_history > > with the extra user. > > E.g. if the extra user is 'rouser', do this: > > > > sed -i '/ovirt_engine_history *ovirt_engine_history/p; s/^\([^ ]* > > *ovirt_engine_history *\)ovirt_engine_history/\1rouser/' > > /var/lib/pgsql/data/pg_hba.conf > > > > 2. Grant the user to connect, e.g.: > > > > su - postgres -c 'psql -U postgres -c "GRANT CONNECT ON DATABASE > > ovirt_engine_history TO rouser;"' > > > > 3. change its password, e.g.: > > > > su - postgres -c 'psql -U postgres -c "ALTER role rouser with login > > encrypted password '\''mypassword'\'';"' > > > > 4. service postgresql restart > > > > Then this should work: > > > > psql -h localhost -U rouser ovirt_engine_history > > (input mypassword in the prompt) > > ovirt_engine_history=> select * from schema_version; > > Did you link to any wiki saying you need to do these steps? No, this bz comment is the only place I wrote it, following Sandro's request. I do not see much point in proper documentation of a change that is still pending review... Also, I thought there might still be a slight chance of getting this into 4.0.3, thus tried to do things quickly. If it's for sure 4.0.4, we have a bit more time. I'll add you as reviewer to the patch.
Prepared a patch for the site [1], will get it merged after all patches are merged. [1] https://github.com/didib/ovirt-site/commits/engine-backup-restore-extra-users
Please review https://gerrit.ovirt.org/62886 . Thanks.
Sandro already merged it. We can push another patch if you want. Cherry-picked to 4.0 for now.
(In reply to Yedidyah Bar David from comment #4) > Prepared a patch for the site [1], will get it merged after all patches are > merged. > > [1] > https://github.com/didib/ovirt-site/commits/engine-backup-restore-extra-users Setting needinfo on myself to remember to merge above once 4.0.4 it released.
Failed in ovirt-engine-4.0.4.3-0.1.el7ev.noarch Preparing to restore: - Unpacking file 'file1' Restoring: - Files Provisioning PostgreSQL users/databases: - user 'engine', database 'engine' - user 'ovirt_engine_history', database 'ovirt_engine_history' - extra user 'history_role' having grants on database ovirt_engine_history, created with a random password FATAL: Creating user 'history_role' failed, please check the log for details Log: ... [ INFO ] Stage: Closing up [ INFO ] Stage: Clean up Log file is located at /var/log/ovirt-engine/setup/ovirt-engine-provisiondb-20160916071122-okv138.log [ INFO ] Stage: Pre-termination [ INFO ] Stage: Termination [ INFO ] Execution of provisiondb completed successfully 2016-09-16 07:11:27 7225: OUTPUT: - extra user 'history_role' having grants on database ovirt_engine_history, created with a random password [ INFO ] Stage: Initializing [ INFO ] Stage: Environment setup Configuration files: ['/etc/ovirt-engine-setup.conf.d/10-packaging-wsp.conf', '/etc/ovirt-engine-setup.conf.d/10-packaging.conf', '/etc/ovirt-engine-setup.conf.d/20-setup-ovirt-post.conf'] Log file: /var/log/ovirt-engine/setup/ovirt-engine-provisiondb-20160916071127-946zmj.log Version: otopi-1.5.2 (otopi-1.5.2-1.el7ev) [ INFO ] Stage: Environment packages setup [ INFO ] Stage: Programs detection [ INFO ] Stage: Environment customization [ ERROR ] Failed to execute stage 'Environment customization': 'OVESETUP_PROVISION_DB/database' [ INFO ] Stage: Clean up Log file is located at /var/log/ovirt-engine/setup/ovirt-engine-provisiondb-20160916071127-946zmj.log [ INFO ] Stage: Pre-termination [ INFO ] Stage: Termination [ ERROR ] Execution of provisiondb failed 2016-09-16 07:11:28 7225: FATAL: Creating user 'history_role' failed, please check the log for details
Target release should be placed once a package build is known to fix a issue. Since this bug is not modified, the target version has been reset. Please use target milestone to plan a fix for a oVirt release.
(In reply to Lukas Svaty from comment #8) > Failed in ovirt-engine-4.0.4.3-0.1.el7ev.noarch rpm -q ovirt-engine-setup-plugin-ovirt-engine-common ? Should be included in latest 4.0.4 build of it, wasn't in 4.0.3.
Indeed it was checked on latest 4.0.4. Leaving on ASSIGNED as the problem is still present. Discussed this within direct messaging channels.
(In reply to Yedidyah Bar David from comment #10) > (In reply to Lukas Svaty from comment #8) > > Failed in ovirt-engine-4.0.4.3-0.1.el7ev.noarch > > rpm -q ovirt-engine-setup-plugin-ovirt-engine-common ? > > Should be included in latest 4.0.4 build of it, wasn't in 4.0.3. To clarify: Patch [1] was pushed to engine master branch quite many months ago, without relation to current bug. Previous patches for current bug were only tested on master builds, not 4.0 ones. Now cherry-picked [1] to 4.0 and 4.0.4 branches. [1] https://gerrit.ovirt.org/57967
verified in ovirt-engine-4.0.4.4-0.1.el7ev.noarch