Bug 2043097

Summary: sql dump of dynflow data is encoded, what breaks sosreport
Product: Red Hat Satellite Reporter: Pavel Moravec <pmoravec>
Component: DynflowAssignee: Adam Ruzicka <aruzicka>
Status: CLOSED ERRATA QA Contact: Lukáš Hellebrandt <lhellebr>
Severity: medium Docs Contact:
Priority: unspecified    
Version: 6.11.0CC: aruzicka, pcreech
Target Milestone: 6.11.0Keywords: Triaged
Target Release: Unused   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: dynflow-utils-1.6.3 Doc Type: If docs needed, set a value
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2022-07-05 14:32:15 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:
Bug Depends On:    
Bug Blocks: 2043102    

Description Pavel Moravec 2022-01-20 15:47:05 UTC 
Description of problem:
sosreport collects sql dump of few dynflow tables as a "CSV task export". Since dynflow 1.6, most of the data will be encoded, hence sosreport will collect human unreadable content.

Please provide a tool that sos (or user) can call (e.g. with an argument of psql request or whole psql command), such that the encrypted data are decoded.


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


How reproducible:
100%


Steps to Reproduce:
export PGPASSWORD=$(grep password /etc/foreman/database.yml | cut -d\" -f2)

timeout 600s psql --no-password -h localhost -p 5432 -U foreman -d foreman -c "COPY (select dynflow_actions.* from foreman_tasks_tasks join dynflow_actions on (foreman_tasks_tasks.external_id = dynflow_actions.execution_plan_uuid::varchar) where foreman_tasks_tasks.started_at > NOW() - interval '1 months') TO STDOUT WITH (FORMAT 'csv', DELIMITER ',', HEADER)"

(similar commands for dynflow_steps and dynflow_execution_plans apply the same, see https://github.com/sosreport/sos/blob/main/sos/report/plugins/foreman.py#L186-L207)


Actual results:
psql command returns human unreadable output like:
72fd2b82-2b87-4179-95e2-3ccd0aa2b4ba,1,,,ForemanInventoryUpload::Async::GenerateAllReportsJob,1,,,,\x85b263757272656e745f726571756573745f6964d92465333333323538632d373730612d346562662d393665322d656532313535393264313935b063757272656e745f74696d657a6f6e65a3555443b763757272656e745f6f7267616e697a6174696f6e5f6964c0b363757272656e745f6c6f636174696f6e5f6964c0af63757272656e745f757365725f696401,
0b63013f-f852-42f5-a992-ffd9635cf913,2,,1,InventorySync::Async::InventoryFullSync,2,,,,\x85b263757272656e745f726571756573745f6964d92462353264313835302d396562642d343762642d623836362d303136396164653030363639b063757272656e745f74696d657a6f6e65a3555443b763757272656e745f6f7267616e697a6174696f6e5f6964c0b363757272656e745f6c6f636174696f6e5f6964c0af63757272656e745f757365725f696401,


Expected results:
Tool accepting psql arguments or directly the psql query, to concatenate the output with dynflow-expand tool to decode the content to e.g.:

timeout 600s psql --no-password -h localhost -p 5432 -U foreman -d foreman -c "COPY (select dynflow_actions.* from foreman_tasks_tasks join dynflow_actions on (foreman_tasks_tasks.external_id = dynflow_actions.execution_plan_uuid::varchar) where foreman_tasks_tasks.started_at > NOW() - interval '1 months') TO STDOUT WITH (FORMAT 'csv', DELIMITER ',', HEADER)" | /root/dynflow/extras/expand/dynflow-expand-x86_64
..
0b63013f-f852-42f5-a992-ffd9635cf913,2,,1,InventorySync::Async::InventoryFullSync,2,,,,"{""current_location_id"":null,""current_organization_id"":null,""current_request_id"":""b52d1850-9ebd-47bd-b866-0169ade00669"",""current_timezone"":""UTC"",""current_user_id"":1}",
8db736f8-db5f-4223-8dea-dd2f0a94a03a,3,,2,ForemanInventoryUpload::Async::QueueForUploadJob,4,5,,,"{""base_folder"":""/var/lib/foreman/red_hat_inventory/generated_reports/"",""current_location_id"":null,""current_organization_id"":null,""current_request_id"":""9885f598-a5de-447c-8685-71419d5c5b01"",""current_timezone"":""UTC"",""current_user_id"":1,""report_file"":""report_for_1.tar.xz""}",{}


Additional info:
Comment 1 Adam Ruzicka 2022-01-20 16:08:15 UTC 
The tool will be delivered in a new package dynflow-utils, it will get pulled in as a dependency of tfm-rubygem-dynflow. Upstream packaging PR[1] is opened.

[1] - https://github.com/theforeman/foreman-packaging/pull/7501
Comment 3 Lukáš Hellebrandt 2022-04-12 09:45:07 UTC 
Verified with Sat 6.11 snap 15.0.

# export PGPASSWORD=$(grep password /etc/foreman/database.yml | cut -d\" -f2)

# timeout 600s psql --no-password -h localhost -p 5432 -U foreman -d foreman -c "COPY (select encode(dynflow_execution_plans.execution_history, 'escape') from foreman_tasks_tasks join dynflow_execution_plans on (foreman_tasks_tasks.external_id = dynflow_execution_plans.uuid::varchar) where foreman_tasks_tasks.started_at > NOW() - interval '1 months') TO STDOUT WITH (FORMAT 'csv', DELIMITER ',', HEADER)" | tail -n 2
\223\203\244time\316b\244name\245delay\250world_id\331$32f7e466-d6d8-4e55-b2db-7ec1f7642951\203\244time\316bSo\221\244name\257start execution\250world_id\331$b7b400c3-11e0-4cf6-af5b-0a18e89e571d\203\244time\316bSo\333\244name\260finish execution\250world_id\331$b7b400c3-11e0-4cf6-af5b-0a18e89e571d
\223\203\244time\316bP\314\222\244name\245delay\250world_id\331$32f7e466-d6d8-4e55-b2db-7ec1f7642951\203\244time\316bR\244name\257start execution\250world_id\331$b7b400c3-11e0-4cf6-af5b-0a18e89e571d\203\244time\316bR\244name\260finish execution\250world_id\331$b7b400c3-11e0-4cf6-af5b-0a18e89e571d

# timeout 600s psql --no-password -h localhost -p 5432 -U foreman -d foreman -c "COPY (select dynflow_execution_plans.execution_history from foreman_tasks_tasks join dynflow_execution_plans on (foreman_tasks_tasks.external_id = dynflow_execution_plans.uuid::varchar) where foreman_tasks_tasks.started_at > NOW() - interval '1 months') TO STDOUT WITH (FORMAT 'csv', DELIMITER ',', HEADER)" | tail -n 2
\x9383a474696d65ce62521e08a46e616d65a564656c6179a8776f726c645f6964d92433326637653436362d643664382d346535352d623264622d37656331663736343239353183a474696d65ce62536f91a46e616d65af737461727420657865637574696f6ea8776f726c645f6964d92462376234303063332d313165302d346366362d616635622d30613138653839653537316483a474696d65ce62536fdba46e616d65b066696e69736820657865637574696f6ea8776f726c645f6964d92462376234303063332d313165302d346366362d616635622d306131386538396535373164
\x9383a474696d65ce6250cc92a46e616d65a564656c6179a8776f726c645f6964d92433326637653436362d643664382d346535352d623264622d37656331663736343239353183a474696d65ce62521e05a46e616d65af737461727420657865637574696f6ea8776f726c645f6964d92462376234303063332d313165302d346366362d616635622d30613138653839653537316483a474696d65ce62521e05a46e616d65b066696e69736820657865637574696f6ea8776f726c645f6964d92462376234303063332d313165302d346366362d616635622d306131386538396535373164

Last entry converted (https://toolslick.com/conversion/data/messagepack-to-json) is:
[
  {
    "time": 1649462418,
    "name": "delay",
    "world_id": "32f7e466-d6d8-4e55-b2db-7ec1f7642951"
  },
  {
    "time": 1649548805,
    "name": "start execution",
    "world_id": "b7b400c3-11e0-4cf6-af5b-0a18e89e571d"
  },
  {
    "time": 1649548805,
    "name": "finish execution",
    "world_id": "b7b400c3-11e0-4cf6-af5b-0a18e89e571d"
  }
]


Now, let's use the tool provided:
# timeout 600s psql --no-password -h localhost -p 5432 -U foreman -d foreman -c "COPY (select dynflow_execution_plans.execution_history from foreman_tasks_tasks join dynflow_execution_plans on (foreman_tasks_tasks.external_id = dynflow_execution_plans.uuid::varchar) where foreman_tasks_tasks.started_at > NOW() - interval '1 months') TO STDOUT WITH (FORMAT 'csv', DELIMITER ',', HEADER)" | /usr/libexec/dynflow-expand | tail -n 2
"[{""name"":""delay"",""time"":1649548808,""world_id"":""32f7e466-d6d8-4e55-b2db-7ec1f7642951""},{""name"":""start execution"",""time"":1649635217,""world_id"":""b7b400c3-11e0-4cf6-af5b-0a18e89e571d""},{""name"":""finish execution"",""time"":1649635291,""world_id"":""b7b400c3-11e0-4cf6-af5b-0a18e89e571d""}]"
"[{""name"":""delay"",""time"":1649462418,""world_id"":""32f7e466-d6d8-4e55-b2db-7ec1f7642951""},{""name"":""start execution"",""time"":1649548805,""world_id"":""b7b400c3-11e0-4cf6-af5b-0a18e89e571d""},{""name"":""finish execution"",""time"":1649548805,""world_id"":""b7b400c3-11e0-4cf6-af5b-0a18e89e571d""}]"


And let's use it according to OP:
# timeout 600s psql --no-password -h localhost -p 5432 -U foreman -d foreman -c "COPY (select dynflow_actions.* from foreman_tasks_tasks join dynflow_actions on (foreman_tasks_tasks.external_id = dynflow_actions.execution_plan_uuid::varchar) where foreman_tasks_tasks.started_at > NOW() - interval '1 months') TO STDOUT WITH (FORMAT 'csv', DELIMITER ',', HEADER)" | /usr/libexec/dynflow-expand  | tail -n 2
d0240620-e490-4f18-a6c1-9aa8edcda836,1,,,InsightsCloud::Async::InsightsScheduledSync,1,,,,"{""current_location_id"":null,""current_organization_id"":null,""current_request_id"":""05726dc3-cde4-4c24-ae7a-3ddc45f42327"",""current_timezone"":""UTC"",""current_user_id"":1}",
a5a49bc4-cbb4-4270-9d9d-aad64b428276,1,,,InsightsCloud::Async::InsightsClientStatusAging,1,2,,,"{""current_location_id"":null,""current_organization_id"":null,""current_request_id"":""0d27a29b-4569-4b56-8e64-44c1533ed3fb"",""current_timezone"":""UTC"",""current_user_id"":1}",{}


I have discovered some issues in /usr/share/doc/dynflow-utils-1.6.3/README.md , I'll file a new BZ about those.
Comment 6 errata-xmlrpc 2022-07-05 14:32:15 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 (Moderate: Satellite 6.11 Release), 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://access.redhat.com/errata/RHSA-2022:5498