Slow query performance on ECS Task Definition table

[estemendoza]

Describe the bug

Command executed: powerpipe control run aws_compliance.control.ecs_task_definition_user_for_host_mode_check --output brief

I have around 11k of items on the aws_ecs_task_definition table and when running controls that uses that table, it takes a lot of time to process it and it ends up with a query timeout error. I tried to increase the query timeout to different values but it always ends up with an error like this:

2024-08-14 16:50:09.258 UTC [WARN]  steampipe-plugin-aws.plugin: [WARN]  1723653476107: QueryData StreamError table 'aws_ecs_task_definition' column 'pid_mode' requires hydrate data from getEcsTaskDefinition, which failed with error operation error ECS: DescribeTaskDefinition, https response error StatusCode: 400, RequestID: 0c27320e-cdd0-41e3-9e4b-79c380abbf70, api error ExpiredTokenException: The security token included in the request is expired.

To try to isolate the root cause, i modified the ecs_task_definition_no_host_pid_mode query, which is pretty straight forward and it doesn't do any join on the table, to limit the amount of records to be retrieved:

query "ecs_task_definition_no_host_pid_mode" {
  sql = <<-EOQ
    select
      task_definition_arn as resource,
      case
        when pid_mode = 'host' then 'alarm'
        else 'ok'
      end as status,
      case
        when pid_mode = 'host' then title || ' shares the host process namespace.'
        else title || ' does not share the host process namespace.'
      end as reason
      ${local.tag_dimensions_sql}
      ${local.common_dimensions_sql}
    from
      aws_ecs_task_definition
    limit 500;
  EOQ
}

I manually added the limit in the SQL to only get a reduced number of records.

The results is this:

{"time":"2024-08-14T18:18:11.953688+02:00","level":"DEBUG","msg":"begin ExecutionTree.Execute"}
⠋ Starting controls…{"time":"2024-08-14T18:18:11.953765+02:00","level":"DEBUG","msg":"begin ResultGroup.Execute","group id":"root_result_group"}
{"time":"2024-08-14T18:18:11.953909+02:00","level":"DEBUG","msg":"end ResultGroup.Execute","group id":"root_result_group"}
{"time":"2024-08-14T18:18:11.954064+02:00","level":"DEBUG","msg":"begin ControlRun.Start","name":"aws_compliance.control.ecs_task_definition_no_host_pid_mode"}
{"time":"2024-08-14T18:18:11.954849+02:00","level":"DEBUG","msg":"ResolveQueryFromQueryProvider","resourceName":"aws_compliance.control.ecs_task_definition_no_host_pid_mode"}
{"time":"2024-08-14T18:18:11.954885+02:00","level":"DEBUG","msg":"ResolveQueryFromQueryProvider","resourceName":"aws_compliance.query.ecs_task_definition_no_host_pid_mode"}
{"time":"2024-08-14T18:18:11.954914+02:00","level":"DEBUG","msg":"control defines inline SQL"}
{"time":"2024-08-14T18:18:11.954934+02:00","level":"DEBUG","msg":"execute start","name":"aws_compliance.control.ecs_task_definition_no_host_pid_mode"}
⠏ Running 1 control. (0 complete, 1 running, 0 pending, 0 errors){"time":"2024-08-14T18:18:14.960207+02:00","level":"DEBUG","msg":"execute finish","name":"aws_compliance.control.ecs_task_definition_no_host_pid_mode"}
{"time":"2024-08-14T18:18:14.960373+02:00","level":"DEBUG","msg":"wait result","name":"aws_compliance.control.ecs_task_definition_no_host_pid_mode"}
⠋ Running 1 control. (0 complete, 1 running, 0 pending, 0 errors){"time":"2024-08-14T18:18:39.350294+02:00","level":"DEBUG","msg":"finish result","name":"aws_compliance.control.ecs_task_definition_no_host_pid_mode"}
{"time":"2024-08-14T18:18:39.351477+02:00","level":"DEBUG","msg":"end ControlRun.Start","name":"aws_compliance.control.ecs_task_definition_no_host_pid_mode"}
{"time":"2024-08-14T18:18:39.351599+02:00","level":"DEBUG","msg":"end ExecutionTree.Execute"}

+ ECS task definitions should not share the host's process namespace ......................................................................................................... 0 / 500 [==========]
  |
Summary

OK ............................................................................................................................................................................... 500 [==========]
SKIP ............................................................................................................................................................................... 0 [          ]
INFO ............................................................................................................................................................................... 0 [          ]
ALARM .............................................................................................................................................................................. 0 [          ]
ERROR .............................................................................................................................................................................. 0 [          ]

TOTAL ........................................................................................................................................................................ 0 / 500 [==========]

As you can see, from the message wait result until the message end ControlRun.Start it took 25 seconds for 500 rows, which i think it is pretty high for that small amount of resources, but i am not sure if I am missing something or it is actually an issue.

Powerpipe version (powerpipe -v) v0.4.0

Steampipe version (steampipe -v) Steampipe v0.23.2

Plugin version (steampipe plugin list) 0.139.0

Additional context

Before testing it, I stopped the steampipe service, i deleted the database, started the service again and the db was re downloaded and installed correctly. I manually connected to the database and checked that the table was empty. Once i executed the control, i could see that the table was filled with the 11k or records after a while, so the tasks definitions seems to be saved correctly.