Bug 1080542 - taskcleaner.sh requires postgres user permissions to remove zombie tasks
Summary: taskcleaner.sh requires postgres user permissions to remove zombie tasks
Keywords:
Status: CLOSED ERRATA
Alias: None
Product: Red Hat Enterprise Virtualization Manager
Classification: Red Hat
Component: ovirt-engine
Version: 3.3.0
Hardware: All
OS: Linux
high
high
Target Milestone: ovirt-3.6.0-rc
: 3.6.0
Assignee: Yedidyah Bar David
QA Contact: Gonza
URL:
Whiteboard:
: 1235707 (view as bug list)
Depends On:
Blocks: 1223826
TreeView+ depends on / blocked
 
Reported: 2014-03-25 16:16 UTC by James W. Mills
Modified: 2019-07-16 11:59 UTC (History)
12 users (show)

Fixed In Version: 3.6.0-4 alpha3
Doc Type: Bug Fix
Doc Text:
Clone Of:
Environment:
Last Closed: 2016-03-09 20:44:19 UTC
oVirt Team: Infra


Attachments (Terms of Use)


Links
System ID Priority Status Summary Last Updated
Red Hat Product Errata RHEA-2016:0376 normal SHIPPED_LIVE Red Hat Enterprise Virtualization Manager 3.6.0 2016-03-10 01:20:52 UTC
oVirt gerrit 42655 None None None Never
oVirt gerrit 42943 master MERGED packaging: dbutils: Drop SPs at end Never
oVirt gerrit 42980 master ABANDONED uninstall dbutils procs after usage Never

Description James W. Mills 2014-03-25 16:16:58 UTC
Description of problem:
Running "taskcleaner.sh -z" or "taskcleaner.sh -z -R -J -A" to list/clean up all zombie activity throws a bunch of errors dealing with function ownership:

# ./taskcleaner.sh -z -R -J -A
psql:./taskcleaner_sp.sql:19: ERROR:  must be owner of function getasynctaskszombies
psql:./taskcleaner_sp.sql:28: ERROR:  must be owner of function deleteasynctaskzombiesbytaskid
psql:./taskcleaner_sp.sql:37: ERROR:  must be owner of function deleteasynctaskzombiesbycommandid
psql:./taskcleaner_sp.sql:44: ERROR:  must be owner of function deleteasynctaskbycommandid
psql:./taskcleaner_sp.sql:51: ERROR:  must be owner of function deleteasynctaskszombies
psql:./taskcleaner_sp.sql:58: ERROR:  must be owner of function deletejobstepsbytaskid
psql:./taskcleaner_sp.sql:68: ERROR:  must be owner of function deleteentitysnapshotbyzombietaskid
psql:./taskcleaner_sp.sql:78: ERROR:  must be owner of function deleteentitysnapshotbytaskid
psql:./taskcleaner_sp.sql:85: ERROR:  must be owner of function deleteentitysnapshotzombies
psql:./taskcleaner_sp.sql:92: ERROR:  must be owner of function deletejobstepsbyzombiecommandid
psql:./taskcleaner_sp.sql:99: ERROR:  must be owner of function deletejobstepsbycommandid
psql:./taskcleaner_sp.sql:106: ERROR:  must be owner of function deletejobstepszombies
psql:./taskcleaner_sp.sql:113: ERROR:  must be owner of function deletealljobs
psql:./taskcleaner_sp.sql:120: ERROR:  must be owner of function deleteallentitysnapshot
 This will remove all Zombie Tasks in async_tasks table, and all Job Steps and Compensation data!!! 
Caution, this operation should be used with care. Please contact support prior to running this command
Are you sure you want to proceed? [y/n]

I've tested this on RHEV 3.1 and RHEV 3.3.

These errors go away if you add "-u postgres", but in RHEV 3.3 we do not share the postgres password as we used to.

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

rhevm-setup-3.3.1-0.48.el6ev.noarch
rhevm-setup-3.1.0-56.el6ev.noarch

How reproducible:

100%

Steps to Reproduce:

# . /etc/ovirt-engine/engine.conf.d/10-setup-database.conf 
# export PGPASSWORD=$ENGINE_DB_PASSWORD
# cd /usr/share/ovirt-engine/setup/dbutils
# ./taskcleaner.sh  -z

Actual results:

# ./taskcleaner.sh  -z
psql:./taskcleaner_sp.sql:19: ERROR:  must be owner of function getasynctaskszombies
psql:./taskcleaner_sp.sql:28: ERROR:  must be owner of function deleteasynctaskzombiesbytaskid
psql:./taskcleaner_sp.sql:37: ERROR:  must be owner of function deleteasynctaskzombiesbycommandid
psql:./taskcleaner_sp.sql:44: ERROR:  must be owner of function deleteasynctaskbycommandid
psql:./taskcleaner_sp.sql:51: ERROR:  must be owner of function deleteasynctaskszombies
psql:./taskcleaner_sp.sql:58: ERROR:  must be owner of function deletejobstepsbytaskid
psql:./taskcleaner_sp.sql:68: ERROR:  must be owner of function deleteentitysnapshotbyzombietaskid
psql:./taskcleaner_sp.sql:78: ERROR:  must be owner of function deleteentitysnapshotbytaskid
psql:./taskcleaner_sp.sql:85: ERROR:  must be owner of function deleteentitysnapshotzombies
psql:./taskcleaner_sp.sql:92: ERROR:  must be owner of function deletejobstepsbyzombiecommandid
psql:./taskcleaner_sp.sql:99: ERROR:  must be owner of function deletejobstepsbycommandid
psql:./taskcleaner_sp.sql:106: ERROR:  must be owner of function deletejobstepszombies
psql:./taskcleaner_sp.sql:113: ERROR:  must be owner of function deletealljobs
psql:./taskcleaner_sp.sql:120: ERROR:  must be owner of function deleteallentitysnapshot
(No rows)

Expected results:

# ./taskcleaner.sh  -z
(No rows)

Comment 2 Eli Mesika 2014-03-26 09:24:13 UTC
Please look at the taskcleaner.sh script 

--snip---

# Install taskcleaner procedures
psql -w -U ${USERNAME} -h ${SERVERNAME} -p ${PORT} -f ./taskcleaner_sp.sql ${DATABASE} > /dev/null

---snip---

that means that this script was running once with postgres and therefor the SPs were installed and owned by postgres 

to fix that , please paste the following content to any file , lets assume drop.sql 

---code---
DROP FUNCTION IF EXISTS GetAsyncTasksZombies();
DROP FUNCTION IF EXISTS DeleteAsyncTaskZombiesByTaskId(v_task_id UUID);
DROP FUNCTION IF EXISTS DeleteAsyncTaskZombiesByCommandId(v_command_id UUID);
DROP FUNCTION IF EXISTS DeleteAsyncTaskByCommandId(v_command_id UUID);
DROP FUNCTION IF EXISTS DeleteAsyncTasksZombies();
DROP FUNCTION IF EXISTS DeleteJobStepsByTaskId(v_task_id UUID);
DROP FUNCTION IF EXISTS DeleteEntitySnapshotByZombieTaskId(v_task_id UUID);
DROP FUNCTION IF EXISTS DeleteEntitySnapshotByTaskId(v_task_id UUID);
DROP FUNCTION IF EXISTS DeleteEntitySnapshotZombies();
DROP FUNCTION IF EXISTS DeleteJobStepsByZombieCommandId(v_command_id UUID);
DROP FUNCTION IF EXISTS DeleteJobStepsByCommandId(v_command_id UUID);
DROP FUNCTION IF EXISTS DeleteJobStepsZombies();
DROP FUNCTION IF EXISTS DeleteAllJobs();
DROP FUNCTION IF EXISTS DeleteAllEntitySnapshot();
---code---

Then run

psql -U postgres -f ./drop.sql engine 

After that you can run :

taskcleaner.sh -z -R -J -A

Comment 5 Yedidyah Bar David 2015-06-10 10:08:22 UTC
(In reply to Eli Mesika from comment #2)
> Please look at the taskcleaner.sh script 
> 
> --snip---
> 
> # Install taskcleaner procedures
> psql -w -U ${USERNAME} -h ${SERVERNAME} -p ${PORT} -f ./taskcleaner_sp.sql
> ${DATABASE} > /dev/null
> 
> ---snip---
> 
> that means that this script was running once with postgres and therefor the
> SPs were installed and owned by postgres 

Don't you think that we should prevent that? Perhaps do one or both of:

1. Create the SPs in engine-setup and not in taskcleaner

2. Use only the engine user there (by reading engine config) and do not allow passing -u

If you do not like (2.) because you want to keep dbutils independent of engine, perhaps we should create a wrapper for it.

Comment 6 Eli Mesika 2015-06-10 13:30:44 UTC
(In reply to Yedidyah Bar David from comment #5)
> (In reply to Eli Mesika from comment #2)
> > Please look at the taskcleaner.sh script 
> > 
> > --snip---
> > 
> > # Install taskcleaner procedures
> > psql -w -U ${USERNAME} -h ${SERVERNAME} -p ${PORT} -f ./taskcleaner_sp.sql
> > ${DATABASE} > /dev/null
> > 
> > ---snip---
> > 
> > that means that this script was running once with postgres and therefor the
> > SPs were installed and owned by postgres 
> 
> Don't you think that we should prevent that? Perhaps do one or both of:
> 
> 1. Create the SPs in engine-setup and not in taskcleaner

taskcleaner can be executed directly so I don't think its a good solution 

> 
> 2. Use only the engine user there (by reading engine config) and do not
> allow passing -u
> 
> If you do not like (2.) because you want to keep dbutils independent of
> engine, perhaps we should create a wrapper for it.

I don't like this either 

Maybe we can check who is the DB owner and prevent passing anything else ...
what do you think ?

Comment 7 Yedidyah Bar David 2015-06-22 07:02:23 UTC
(In reply to Eli Mesika from comment #6)
> (In reply to Yedidyah Bar David from comment #5)
> > (In reply to Eli Mesika from comment #2)
> > > Please look at the taskcleaner.sh script 
> > > 
> > > --snip---
> > > 
> > > # Install taskcleaner procedures
> > > psql -w -U ${USERNAME} -h ${SERVERNAME} -p ${PORT} -f ./taskcleaner_sp.sql
> > > ${DATABASE} > /dev/null
> > > 
> > > ---snip---
> > > 
> > > that means that this script was running once with postgres and therefor the
> > > SPs were installed and owned by postgres 
> > 
> > Don't you think that we should prevent that? Perhaps do one or both of:
> > 
> > 1. Create the SPs in engine-setup and not in taskcleaner
> 
> taskcleaner can be executed directly so I don't think its a good solution 

As we discussed, this might still be the best way.

engine-setup loads the SPs, taskcleaner uses them.

> 
> > 
> > 2. Use only the engine user there (by reading engine config) and do not
> > allow passing -u
> > 
> > If you do not like (2.) because you want to keep dbutils independent of
> > engine, perhaps we should create a wrapper for it.
> 
> I don't like this either 
> 
> Maybe we can check who is the DB owner and prevent passing anything else ...
> what do you think ?

Not sure how this is different from (2.)...

Please take over. Thanks.

Comment 8 Eli Mesika 2015-06-22 08:48:50 UTC
Will move SPs under dbutils to be installed by engine-setup, this will prevent installing those procedures as another user

Comment 9 Yedidyah Bar David 2015-06-25 15:08:48 UTC
Fails on upgrade if SPs were not loaded yet.

We need to load them at upgrade before calling taskcleaner/fkvalidator

Comment 10 Eli Mesika 2015-06-25 15:36:24 UTC
Didi, following our conversation, this will be done by you, can you take teh bug
Also consider mark as duplicate 1235707

Comment 11 Eli Mesika 2015-06-28 09:56:58 UTC
fixing patch (42655) was reverted by 42932 since it caused problems in upgrade

Comment 12 Yedidyah Bar David 2015-06-29 12:33:54 UTC
*** Bug 1235707 has been marked as a duplicate of this bug. ***

Comment 13 Yedidyah Bar David 2015-06-29 13:04:13 UTC
Eventually we decided to drop them at end of each util.

To run them with engine db user:
================================

su -

. /usr/share/ovirt-engine/bin/engine-prolog.sh
export PGPASSWORD="${ENGINE_DB_PASSWORD}"

cd /usr/share/ovirt-engine/setup/dbutils

./fkvalidator.sh -u "${ENGINE_DB_USER}" -d "${ENGINE_DB_DATABASE}"

./taskcleaner.sh -u "${ENGINE_DB_USER}" -d "${ENGINE_DB_DATABASE}"

./unlock_entity.sh -u "${ENGINE_DB_USER}" -d "${ENGINE_DB_DATABASE}" -t all

To run them with postgres db user:
==================================
(assuming db on localhost)

su - postgres

cd /usr/share/ovirt-engine/setup/dbutils

./fkvalidator.sh -u postgres -d engine -s /tmp

./taskcleaner.sh -u postgres -d engine -s /tmp

./unlock_entity.sh -u postgres -d engine -s /tmp -t all

If you still get an error message
=================================
It's probably because you ran it with user postgres in the past (with an older version) and now try with engine db user.

Just run once with postgres, it will run and then drop the SPs. Then engine db user will be able to create them and run successfully.

Comment 14 Yedidyah Bar David 2015-07-01 11:36:43 UTC
comment 13 above refers to the behavior with a pending patch (42980). Without the patch, everything in the comment still works, but will not drop the SPs, so a next attempt might fail (third case there). If so you can still run with user postgres, but running with user engine (as does engine-setup) will still fail. See comment 2 for how to drop manually.

Comment 15 Gonza 2015-08-03 10:37:23 UTC
Verified with instructions from #c13 with:
ovirt-engine-3.6.0-0.0.master.20150627185750.git6f063c1.el6.noarch

unlock all  completed successfully.

Comment 17 errata-xmlrpc 2016-03-09 20:44:19 UTC
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.

https://rhn.redhat.com/errata/RHEA-2016-0376.html


Note You need to log in before you can comment on or make changes to this bug.