unexpected EOF in correct SQL query

[ixemad]

Describe the bug

An unexpected EOF error is raised for the following SQL query:

SELECT role_name, subscription_id, action AS forbidden_action
FROM (
  SELECT
    role_name,
    subscription_id,
    jsonb_array_elements_text(perm->'actions') AS action
  FROM prod_all.azure_role_definition
  CROSS JOIN LATERAL jsonb_array_elements(permissions) AS perm
  WHERE role_type = 'CustomRole'
) t
WHERE action IN ( '*', 
                  'Microsoft.Authorization/*', 
                  'Microsoft.Authorization/*/Write', 
                  'Microsoft.Authorization/*/Delete', 
                  'Microsoft.Network/*' )

Steampipe version (steampipe -v)
Example: v2.2.4

Plugin version (steampipe plugin list)
Example: v1.12.0

To reproduce

steampipe query --output json statement.sql

Expected behavior

If the query is somehow shorten like the next one (no delete action), no error is raised and meaningful data is returned.

SELECT role_name, subscription_id, action AS forbidden_action
FROM (
  SELECT
    role_name,
    subscription_id,
    jsonb_array_elements_text(perm->'actions') AS action
  FROM prod_all.azure_role_definition
  CROSS JOIN LATERAL jsonb_array_elements(permissions) AS perm
  WHERE role_type = 'CustomRole'
) t
WHERE action IN ( '*', 
                  'Microsoft.Authorization/*', 
                  'Microsoft.Authorization/*/Write', 
                  'Microsoft.Network/*' )

[cbruno10]

Hi @ixemad , thanks for raising this!

I think this is something we've seen before based on how the FDW creates plans. The unexpected EOF is the Postgres backend segfaulting in the FDW layer, and it gets triggered by the cross join lateral jsonb_array_elements(permissions) over a JSON column from a foreign table. It isn't really the in list, that just shifts the plan enough to flip the crash on or off. Same thing has come up before in turbot/steampipe#1388 and turbot/steampipe#4225.

As a workaround, can you please try materializing the scan into a CTE first works around it and returns the same results:

with custom_roles as MATERIALIZED
  (select role_name, subscription_id, permissions
   from prod_all.azure_role_definition
   where role_type = 'CustomRole')
select role_name,
       subscription_id,
       action as forbidden_action
from
  (select role_name,
          subscription_id,
          jsonb_array_elements_text(perm->'actions') as action
   from custom_roles
   cross join lateral jsonb_array_elements(permissions) as perm) t
where action in ('*',
                 'Microsoft.Authorization/*',
                 'Microsoft.Authorization/*/Write',
                 'Microsoft.Authorization/*/Delete',
                 'Microsoft.Network/*');

Thanks!

[ixemad]

I'm using xonsh shell. Same issue

However, if the following example the Microsoft.Authorization/*/Write item was deleted and the query worked as expected.

[github-actions]

This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days.