[Turbot / AWS] table 'aws_ssoadmin_account_assignment' doesn't work for IAM Identity Center (formerly known as AWS SSO)

[ahkai86]

Describe the bug A clear and concise description of what the bug is. According to the site (https://hub.steampipe.io/plugins/turbot/aws/tables/aws_ssoadmin_account_assignment), the examples could not work and will trigger errors:

"Error: operation error SSO Admin: ListAccountAssignments, https response error StatusCode: 400, RequestID: 6dbdc0da-dcdb-47d5-980c-e5eaf08ca47a, AccessDeniedExcept"
+--------------------+-------------------+----------------+--------------+
| permission_set_arn | target_account_id | principal_type | principal_id |
+--------------------+-------------------+----------------+--------------+
+--------------------+-------------------+----------------+--------------+

The error above will show multiple lines based on the number of aws accounts configured. Note that my IAM user assumed role has full administrative access and my steampipe dashboard works okey for all the AWS dashboard mods except tables pertaining to 'aws_ssoadmin'

Some users together with me has raise this over at Steampipe slack channel as well.

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

To reproduce Steps to reproduce the behavior (please include relevant code and/or commands).

steampipe query select permission_set_arn, target_account_id, identity_store_id, principal_type, principal_id, from aws_ssoadmin_account_assignment where permission_set_arn = 'arn:aws:sso:::permissionSet/ssoins-0123456789abcdef/ps-0123456789abcdef' and target_account_id = '012347678910';

Expected behavior Display the following information in a command line table: 1) permission_set_arn, 2) target_account_id, 3) identity_store_id, 4) principal_type, 5) principal_id,

Additional context I could be wrong, AWS has some undocumented APIs for IAM Identity Center (formerly know as AWS SSO). Hence there is still no console dashboard. My objective will be to create an IAM Identity Center dashboard to display AWS accounts mapped to with which users and which PermissionSets.

Affected tables:

• https://hub.steampipe.io/plugins/turbot/aws/tables/aws_ssoadmin_account_assignment
• https://hub.steampipe.io/plugins/turbot/aws/tables/aws_ssoadmin_permission_set
• https://hub.steampipe.io/plugins/turbot/aws/tables/aws_ssoadmin_instance?filter=sso

[misraved]

Apologies @ahkai86 for the bump!!

Could you please share how which authentication mode is being used by you to access the AWS tables? If possible could you share how your aws.spc file looks like after removing any sensitive information?

Also, are you able to run basic AWS SSO CLI commands as mentioned in https://docs.aws.amazon.com/cli/latest/reference/sso/index.html?

[ahkai86]

HI @misraved , no worries on the bump! In fact appreciate your bump! =)

the following is my aws.spc file:

# Automatically Generated at Thu Mar 23 16:21:55 +08 2023
# Create an aggregator of _all_ the accounts as the first entry in the search path.
connection "aws" {
  plugin = "aws"
  type        = "aggregator"
  connections = ["aws_*"]
}

connection "aws_Account1" {
  plugin  = "aws"
  profile = "sp_Account1"
  regions = ["*"]
}

connection "aws_Account2" {
  plugin  = "aws"
  profile = "sp_Account2"
  regions = ["*"]
}

connection "aws_Account3" {
  plugin  = "aws"
  profile = "sp_Account3"
  regions = ["*"]
}

connection "aws_Account4" {
  plugin  = "aws"
  profile = "sp_Account4"
  regions = ["*"]
}

connection "aws_Account5" {
  plugin  = "aws"
  profile = "sp_Account5"
  regions = ["*"]
}

connection "aws_Account6" {
  plugin  = "aws"
  profile = "sp_Account6"
  regions = ["*"]
}

[ahkai86]

Yes. the AWS SSO CLI works, i have also attached the screenshot in slack channel ! Do note that all my credentials has administrator permissions, and all the steampipe dashboard mod works perfectly, only SSO tables has issues

[rajlearner17]

Hi @ahkai86, while we are investigating this behaviour, like add a few notes for further validation purpose

First of all, this example query is broken with the incorrect SQL format; we will be correcting the same soon. Appreciate your feedback. 👍

The query only works for accounts where in AWS > SSO > [IAM Identity Center] > Permission sets are defined. E.g. if we configure the permission sets in the organization's master account and use the permission sets in the child account.

To be specific, see the attached image where we have permission sets defined in the master account, and I am querying the accounts where these permission sets are used to login as SSO; the below query is executed in the master account with target_account_id specified as the child account.

select
  permission_set_arn,
  target_account_id,
  principal_type,
  principal_id
from
  aws_master.aws_ssoadmin_account_assignment where permission_set_arn = 'arn:aws:sso:::permissionSet/ssoins-12341234567d0040/ps-123456339b6dace2' and target_account_id = '123448204312'

Here I am querying to master the account with .spc connection name as aws_master and target_account_id as one of the child accounts.

Another catch in this is the query works when we specify the region where SSO permission sets are enabled.

# Master Account SSO setting
connection "aws_master" {
  plugin  = "aws"
  profile = "master-SSO-ReadOnly-123456781433"
  # regions = ["*"] # Not working
  regions = ["us-east-2"]  
# Region where SSO permission sets are enabled.
}

If I understand correctly, when we consider the above limitation, in that case, I assume that all your configured aws accounts will not have SSO enabled by themselves, as we usually do not enable the same if the organisation's master account manages the accounts. (Pls correct me)

So when we query aws_ssoadmin_account_assignment, it requires 2 required quals: i.e.'permission_set_arn' & 'target_account_id';

Considering your default aggregator connection will not differentiate which account will meet the above requirement, it will end up resulting error, as you have mentioned, and we could reproduce it for now.

Even though I am unsure why the SSO-enabled master account works only when the specific region is specified, we will investigate this further.

I hope the above information can help you check and match our findings or at least help isolate this query to run on specific account(s).

I'll keep you posted; we'd happily get your feedback when you have it.

Result

[ParthaI]

@ahkai86 Thanks for waiting, in case you have seen this :)

The occurrence of an AccessDeniedException is a result of SSO not being enabled in certain regions, i.e. when you provide regions = ["*"]. At the same time, AWS allows only one region to be enabled with IAM Identity Center.

To avoid this error, could you please add AccessDeniedException in ignore_error_codes for the specific account connection?, e.g. as given below.

connection "aws_master" {
  plugin  = "aws"
  profile = "billing-org-SSO-ReadOnly-346944191234"
  regions = ["*"]
  #regions = ["us-east-2"]
  ignore_error_codes = ["AccessDeniedException"]
}

Or

You can add a specific region where SSO is enabled e.g. us-east-2 or any other region based on your SSO configuration in IAM Identity Center.

connection "aws_master" {
  plugin  = "aws"
  profile = "billing-org-SSO-ReadOnly-346944191234"
  #regions = ["*"]
  regions = ["us-east-2"]
}

Please let us know, if it helps you, Thanks!

[ahkai86]

Hi @ParthaI and @rajlearner17,

Good Day! Thanks so much for the detailed resolutions, appreciated !

From your screenshot (blue), what do i input for the question marks??

"<???>.aws_ssoadmin_account_assignment" where permission_set_arn = 'arn:aws:sso:::permissionSet/ssoins-82105c91f042da55/ps-xxxxxxxxxxx' and target_account_id = 'xxxxxxxxxxxx' "

aws.spc file:

connection "aws_CloudRGB_Management_Account" {
  plugin  = "aws"
  profile = "sp_CloudRGB_Management_Account"
  regions = ["*"]
  ignore_error_codes = ["AccessDeniedException"]
}

[ParthaI]

"<???>.aws_ssoadmin_account_assignment" where permission_set_arn = 'arn:aws:sso:::permissionSet/ssoins-82105c91f042da55/ps-xxxxxxxxxxx' and target_account_id = 'xxxxxxxxxxxx' "

@ahkai86 In the place of ??, please provide the connection name (the Steampipe connection config name of the SSO credential set up in the aws.spc file for the master/organization account). If the default connection is being used, there is no need to specify a connection name before the table name. Thanks!

[ahkai86]

Hi @ParthaI , i am still encountered errors as shown above "Error: relation "aws_cloudrgb_management_account.aws_ssoadmin_account_assignment" does not exist (SQLSTATE 42P01)"

let retry a few other settings

Steampipe v0.19.5

[ParthaI]

@ahkai86, I hope you are checking the settings on your end. The configuration is having issues, which is not detecting the connection. Please refer to this in case it helps.

[ahkai86]

Hi @ParthaI ,

Got it solved, the connection name has to be lowercase. It is case sensitive =)

Able to ignore case sensitive connection name? The aggregator mapped it according to AWS Account name under AWS Organization.

[ahkai86]

Hi @ParthaI , will there be update of attributes which the table can list? I can trying to formulate a table to display all Users/Groups to display PermissionSets which is allocated to which AWS accounts. Will that be possible?

[ParthaI]

Hi @ahkai86, there is an example query which can help you to display all Users/Groups to display PermissionSets which is allocated to which AWS accounts.

with aws_ssoadmin_principal as
(
  select
    i.arn as instance_arn,
    'GROUP' as "type",
    g.id,
    g.title
  from
    aws_ssoadmin_instance i
    left join
      aws_identitystore_group g
      on i.identity_store_id = g.identity_store_id
    union
    select
      i.arn as instance_arn,
      'USER' as "type",
      u.id,
      u.title
    from
      aws_ssoadmin_instance i
      left join
        aws_identitystore_user u
        on i.identity_store_id = u.identity_store_id
)
select
  a.target_account_id,
  a.principal_type,
  p.title as principal_title
from
  aws_ssoadmin_account_assignment a
  left join
    aws_ssoadmin_principal p
    on a.principal_type = p.type
    and a.principal_id = p.id
    and a.instance_arn = p.instance_arn
where
  a.target_account_id = '112233445566' and a.permission_set_arn = 'arn:aws:sso:::permissionSet/ssoins-34356373315d0040/ps-7fdb1333ew34rf';

The sample output:

+-------------------+----------------+--------------------+
| target_account_id | principal_type | principal_title    |
+-------------------+----------------+--------------------+
| 112233445566      | USER           | abcssddd@gmail.com |
| 112233445566      | USER           | fsfeff@gmail.com   |
| 112233445566      | USER           | www@gmail.com      |
| 112233445566      | USER           | fsdd@gmail.com     |
| 112233445566      | USER           | rewww@gmail.com    |
| 112233445566      | USER           | kkfjrbtt@gmail.com |
| 112233445566      | USER           | frg@gmail.com      |
+-------------------+----------------+--------------------+

Please let us know if that helps you. Thanks!

[ahkai86]

Hi @ParthaI,

Thanks of the info earlier on, i had managed to circumvent using table "aws_ssoadmin_account_assignment" to list for all AWS accounts with their associated PermissionSets and IAM Users/Groups.

Thanks and cheers!

[vkumbha]

@ahkai86 Could you please provide us with the query you used? Sharing it would be valuable for others facing a similar use-case.

[ahkai86]

Hi @vkumbha , sure! Let me list the code here in a bit as there are some impediments when running more than 10 aws accounts. I am still doing various testing

[ahkai86]

Hi @vkumbha @ParthaI and @rajlearner17,

Good Day!

Here is the code which i have used for less than 10 AWS accounts, but once it tested with more aws accounts which i tried ( >90 aws accounts) ,took 46 mins to run.

Hope anyone can optimize further of a greater usage for IAM Identity Center !

with orgs as (
    SELECT id FROM aws_master.aws_organizations_account
  )
  , 
  aws_ssoadmin_principal as (
  select
    i.arn as instance_arn,
    'GROUP' as "type",
    g.id,
    g.title
  from
    aws_master.aws_ssoadmin_instance i
    left join aws_identitystore_group g on i.identity_store_id = g.identity_store_id
  union
  select
    i.arn as instance_arn,
    'USER' as "type",
    u.id,
    u.title
  from
    aws_master.aws_ssoadmin_instance i
    left join aws_identitystore_user u on i.identity_store_id = u.identity_store_id
)

SELECT
  a.name as "AWS Account Name",
  a.id as "Account ID",
  u.title as "Username/GroupName",
  g.type as "Type",
  p.name as "PermissionSet",
  p.description as "PermissionSet Description"
FROM
  aws_master.aws_organizations_account a
  LEFT JOIN aws_master.aws_ssoadmin_account_assignment aa ON aa.target_account_id = a.id

  JOIN aws_ssoadmin_principal u ON u.id = aa.principal_id 
  LEFT JOIN aws_ssoadmin_principal g ON g.id = u.id

  JOIN aws_master.aws_ssoadmin_permission_set p ON p.arn = aa.permission_set_arn
WHERE
  permission_set_arn IN (SELECT p.arn FROM aws_master.aws_ssoadmin_permission_set)
  and target_account_id IN (SELECT a.id FROM orgs)
  ORDER BY
    a.name DESC;

Appreciate any help! Thanks so much

10 aws account -> took 10mins to load 90 aws account -> took 46 mins to load

[ahkai86]

Hihi,

Good Day! Any updates to speed up the queries? Tried several methods but a query to the master AWS accounts took very long for >95 aws accounts

[ahkai86]

Hihi,

on 2nd run of the sql query (below), it will not work anymore. Is there any postgreSQL limitation or??

with orgs as (
    SELECT id FROM aws_master.aws_organizations_account
  )
  , 
  aws_ssoadmin_principal as (
  select
    i.arn as instance_arn,
    'GROUP' as "type",
    g.id,
    g.title
  from
    aws_master.aws_ssoadmin_instance i
    left join aws_identitystore_group g on i.identity_store_id = g.identity_store_id
  union
  select
    i.arn as instance_arn,
    'USER' as "type",
    u.id,
    u.title
  from
    aws_master.aws_ssoadmin_instance i
    left join aws_identitystore_user u on i.identity_store_id = u.identity_store_id
)

SELECT
  a.name as "AWS Account Name",
  a.id as "Account ID",
  u.title as "Username/GroupName",
  g.type as "Type",
  p.name as "PermissionSet",
  p.description as "PermissionSet Description"
FROM
  aws_master.aws_organizations_account a
  LEFT JOIN aws_master.aws_ssoadmin_account_assignment aa ON aa.target_account_id = a.id

  JOIN aws_ssoadmin_principal u ON u.id = aa.principal_id 
  LEFT JOIN aws_ssoadmin_principal g ON g.id = u.id

  JOIN aws_master.aws_ssoadmin_permission_set p ON p.arn = aa.permission_set_arn
WHERE
  permission_set_arn IN (SELECT p.arn FROM aws_master.aws_ssoadmin_permission_set)
  and target_account_id IN (SELECT a.id FROM orgs)
  ORDER BY
    a.name DESC;

[rajlearner17]

@ahkai86 Thanks for your continuous endeavour in this one; much appreciated 👍

There is no immediate response to this with us now; sorry for that, but we will try to simulate it in our org account to see what possibility we can explore.

@vkumbha @khushboo9024

[github-actions[bot]]

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.

[github-actions[bot]]

This issue was closed because it has been stalled for 90 days with no activity.