Quals issues with multiple tables and `aws_iam_policy_simulator`

[dboeke]

Describe the bug The aws_iam_policy_simulator requires 3 Quals (principal_arn, resource_arn, action). Joining this table to two other tables (e.g. aws_iam_user.arn and aws_s3_bucket.arn) results in the following error message:

Error: 'List' call requires an '=' qual for all columns: principal_arn,action,resource_arn

Using a join for one qual and hard-coded values for the other two quals works, but applying multiple joins does not.

This works

select 
  u.name,
  s.action,
  s.decision
from
  aws_iam_policy_simulator s
  join aws_iam_user u on s.principal_arn = u.arn
where
  resource_arn = 'arn:aws:s3:::dmi-employee-data'
  and action = 's3:ListBucket';

And this works

select 
  b.name,
  s.action,
  s.decision
from
  aws_iam_policy_simulator s
  join aws_s3_bucket b on s.resource_arn = b.arn
where
  s.principal_arn = 'arn:aws:iam::899206412154:user/toby_flenderson'
  and b.name like 'dmi%'
  and s.action = 's3:ListBucket';

This does not work

select
  u.name as user,
  b.name as bucket,
  s.action,
  s.decision
from
  aws_iam_policy_simulator s
  join aws_iam_user u on s.principal_arn = u.arn
  join aws_s3_bucket b on s.resource_arn = b.arn
where
  b.name like 'dmi%'
  and s.action = 's3:ListBucket';

Steampipe version (steampipe -v) Example: v0.4.0

Plugin version (steampipe plugin list) AWS: v0.12.0

To reproduce

1. Create an iam user
2. Create a bucket named dmi-test-bucket-xxxxx
3. Run the query

Expected behavior The aws_iam_policy_simulator table should return a row result for each combination of IAM user and S3 Bucket

[cbruno10]

Hey @dboeke , I believe this is an issue to look at within the Postgres FDW, so I'm moving the issue over to https://github.com/turbot/steampipe-postgres-fdw.

[johnsmyth]

@kaidaguerre , @dboeke This is probably a tricky one, because its a multi-table planning issue. By default it generates a hash join plan:

jsmyth$ steampipe query
Welcome to Steampipe v0.4.0-rc.1
For more information, type .help
> explain select
  u.name as user,
  b.name as bucket,
  s.action,
  s.decision
from
  aws_iam_policy_simulator s
  join aws_iam_user u on s.principal_arn = u.arn
  join aws_s3_bucket b on s.resource_arn = b.arn
where
  b.name like 'dmi%'
  and s.action = 's3:ListBucket';
+---------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                    |
+---------------------------------------------------------------------------------------------------------------+
| Hash Join  (cost=20000000012773.44..80890287811015.64 rows=25000000000000 width=128)                          |
|   Hash Cond: (s.resource_arn = b.arn)                                                                         |
|   ->  Nested Loop  (cost=0.00..60015273442500.00 rows=5000000000 width=128)                                   |
|         Join Filter: (s.principal_arn = u.arn)                                                                |
|         ->  Foreign Scan on aws_iam_policy_simulator s  (cost=0.00..40000000000000.00 rows=1000000 width=400) |
|               Filter: (action = 's3:ListBucket'::text)                                                        |
|         ->  Materialize  (cost=0.00..20000000005273.44 rows=1000000 width=200)                                |
|               ->  Foreign Scan on aws_iam_user u  (cost=0.00..20000000000000.00 rows=1000000 width=200)       |
|   ->  Hash  (cost=20000000000000.00..20000000000000.00 rows=1000000 width=200)                                |
|         ->  Foreign Scan on aws_s3_bucket b  (cost=0.00..20000000000000.00 rows=1000000 width=200)            |
|               Filter: (name ~~ 'dmi%'::text)                                                                  |
+---------------------------------------------------------------------------------------------------------------+

If you disable hash and merge joins, it orders it such that it works:

> set enable_hashjoin to off

Time: 305.881µs
> set enable_mergejoin to off

Time: 361.713µs
> explain select
  u.name as user,
  b.name as bucket,
  s.action,
  s.decision
from
  aws_iam_policy_simulator s
  join aws_iam_user u on s.principal_arn = u.arn
  join aws_s3_bucket b on s.resource_arn = b.arn
where
  b.name like 'dmi%'
  and s.action = 's3:ListBucket';
+---------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                          |
+---------------------------------------------------------------------------------------------------------------------+
| Nested Loop  (cost=0.00..155943020322500.00 rows=25000000000000 width=128)                                          |
|   Join Filter: (s.resource_arn = b.arn)                                                                             |
|   ->  Foreign Scan on aws_s3_bucket b  (cost=0.00..20000000000000.00 rows=1000000 width=200)                        |
|         Filter: (name ~~ 'dmi%'::text)                                                                              |
|   ->  Materialize  (cost=0.00..60015299370234.38 rows=5000000000 width=128)                                         |
|         ->  Nested Loop  (cost=0.00..60015273442500.00 rows=5000000000 width=128)                                   |
|               Join Filter: (s.principal_arn = u.arn)                                                                |
|               ->  Foreign Scan on aws_iam_policy_simulator s  (cost=0.00..40000000000000.00 rows=1000000 width=400) |
|                     Filter: (action = 's3:ListBucket'::text)                                                        |
|               ->  Materialize  (cost=0.00..20000000005273.44 rows=1000000 width=200)                                |
|                     ->  Foreign Scan on aws_iam_user u  (cost=0.00..20000000000000.00 rows=1000000 width=200)       |
+---------------------------------------------------------------------------------------------------------------------+

Time: 1.368774ms
> select
  u.name as user,
  b.name as bucket,
  s.action,
  s.decision
from
  aws_iam_policy_simulator s
  join aws_iam_user u on s.principal_arn = u.arn
  join aws_s3_bucket b on s.resource_arn = b.arn
where
  b.name like 'dmi%'
  and s.action = 's3:ListBucket';
+------+--------+--------+----------+
| user | bucket | action | decision |
+------+--------+--------+----------+
+------+--------+--------+----------+

Time: 1.026285594s