Bug 1369757

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