Bug Report: FortiSOAR Playbook Hangs on PostgreSQL Query with No Output

[kaashif_m]

Description:
I encountered an issue in FortiSOAR when executing a PostgreSQL query within a playbook. If the query is incorrect or does not return any output, the playbook execution remains stuck in that step indefinitely until the session times out.

Additionally, I observed a CPU spike in the uwsgi service, which reached 100% utilization during this process.

Expected Behavior:
When a query executes successfully but returns no output, the playbook should complete the step and move forward instead of getting stuck.

Issue Reproduction:

1. Run a PostgreSQL query in a playbook.
2. If the query has no output or is incorrect, the playbook remains in execution indefinitely.
3. Monitor system resources and notice uwsgi service consuming 100% CPU.

Impact:

• Playbook execution hangs indefinitely.
• High CPU usage degrades system performance.
• Users must manually intervene to stop the playbook.

Request:
I hope the Fortinet team can investigate and resolve this issue in the next FortiSOAR release.

Thank you!

[ranjeet]

Hi @kaashif_m ,

Could you please share the PostgreSQL query along with the details of the data you're looking to retrieve ? altertnatively, can you share the same playbook as you worked. This way, we can test it on our setup and ensure it works properly or not.

[kaashif_m]

Dear Ranjit, 

Assume we have  a query which has a line commented out which works good in the GUI workbench.  

SELECT c.col_1, c.col_2,
a.col_3, a.col_4, a.col_5,
a.col_6 AS current_status, b.col_6 AS old_status,
e.col_7, d.col_8,
a.col_9, a.col_10,
a.col_11, a.col_12,
a.col_13, a.col_14,
a.col_15, a.col_16,
b.col_17
FROM schema_1.table_1 b,
schema_1.table_2 a,
schema_1.table_3 c,
schema_1.table_4 d,
schema_1.table_5 e
WHERE a.col_3 = b.col_3
AND a.col_5 = c.col_1
AND d.col_8 = a.col_8
AND e.col_7 = a.col_7
AND a.col_6 != b.col_6
AND a.col_6 IN ('C', 'I')

--and trunc(b.date_of_change) = TO_CHAR(CURRENT_DATE, 'dd/mm/yyyy')
AND TRUNC(b.col_17) = CURRENT_DATE;

When I copy pasted the same into the fortisoar. I observed the playbook running till the timeout and the CPU spike. 

After the debugging when i removed the commented line and ran the same query it was working fine 

eg . 

SELECT c.col_1, c.col_2,
a.col_3, a.col_4, a.col_5,
a.col_6 AS current_status, b.col_6 AS old_status,
e.col_7, d.col_8,
a.col_9, a.col_10,
a.col_11, a.col_12,
a.col_13, a.col_14,
a.col_15, a.col_16,
b.col_17
FROM schema_1.table_1 b,
schema_1.table_2 a,
schema_1.table_3 c,
schema_1.table_4 d,
schema_1.table_5 e
WHERE a.col_3 = b.col_3
AND a.col_5 = c.col_1
AND d.col_8 = a.col_8
AND e.col_7 = a.col_7
AND a.col_6 != b.col_6
AND a.col_6 IN ('C', 'I')
AND TRUNC(b.col_17) = CURRENT_DATE;

Due to compliance issue i cannot share you the actual query  and playbook. 

Kindly try the query by introducing a commented line as below you might able to reproduce it 

--and trunc(b.date_of_change) = TO_CHAR(CURRENT_DATE, 'dd/mm/yyyy')