Let me query aws_ssoadmin_account_assignment without qualifiers

[iainelder]

I want to list all the assignments in my Identity Center instance.

The aws_ssoadmin_account_assignment table should solve it with this query.

SELECT * FROM aws_ssoadmin_account_assignment;

But the query fails because it doesn't qualify the permission set ARN and the target account ID.

Error: rpc error: code = Internal desc = aws: rpc error: code = Internal desc = 'List' call for table 'aws_ssoadmin_account_assignment' is missing 2 required quals:
    column:'permission_set_arn' operator: =
    column:'target_account_id' operator: =
 (SQLSTATE HV000)

I try to work around it with this query to join a list of organization accounts and a list of permission sets. It gives the same error.

SELECT assignment.*
FROM aws_ssoadmin_account_assignment AS assignment
INNER JOIN aws_organizations_account AS ac ON (ac.account_id = assignment.target_account_id)
INNER JOIN aws_ssoadmin_permission_set AS ps ON (ps.arn = assignment.permission_set_arn)
;

Steampipe can provide more value here by abstracting away all the required parameters of the underlying ListAccountAssignments API. If I want all the assignments, Steampipe should figure out the API calls to make that happen.

aws-sso-uitil has a command to solve the problem called aws-sso-utill admin assignments. The Steampipe table could copy its solution.

aws-sso-util takes care of this, by looping over all the accounts in your organization, then over all the permission sets in each account and then over all principals with that permission set in that account.