steampipe-plugin-aws icon indicating copy to clipboard operation
steampipe-plugin-aws copied to clipboard

Aws resource analysis table

Open omerosaienni opened this issue 1 year ago • 0 comments

Integration test logs

Logs
$ ./tint.js aws_resource_policy_analysis

No env file present for the current environment:  staging 
 Falling back to .env config
No env file present for the current environment:  staging
customEnv TURBOT_TEST_EXPECTED_TIMEOUT undefined

SETUP: tests/aws_resource_policy_analysis []

PRETEST: tests/aws_resource_policy_analysis

TEST: tests/aws_resource_policy_analysis
Running terraform

Terraform used the selected providers to generate the following execution
plan. Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # aws_iam_role.test_role will be created
  + resource "aws_iam_role" "test_role" {
      + arn                   = (known after apply)
      + assume_role_policy    = jsonencode(
            {
              + Statement = [
                  + {
                      + Action    = "sts:AssumeRole"
                      + Effect    = "Allow"
                      + Principal = {
                          + Service = "ec2.amazonaws.com"
                        }
                      + Sid       = "PublicService"
                    },
                  + {
                      + Action    = "sts:AssumeRole"
                      + Condition = {
                          + StringEquals = {
                              + aws:SourceOwner = "012345678901"
                            }
                        }
                      + Effect    = "Allow"
                      + Principal = {
                          + Service = "cloudwatch.amazonaws.com"
                        }
                      + Sid       = "RestrictedService"
                    },
                ]
              + Version   = "2012-10-17"
            }
        )
      + create_date           = (known after apply)
      + force_detach_policies = false
      + id                    = (known after apply)
      + managed_policy_arns   = (known after apply)
      + max_session_duration  = 3600
      + name                  = "turbottest13658"
      + name_prefix           = (known after apply)
      + path                  = "/"
      + tags                  = {
          + "tag-key" = "integration-test"
        }
      + tags_all              = {
          + "tag-key" = "integration-test"
        }
      + unique_id             = (known after apply)

      + inline_policy {
          + name   = (known after apply)
          + policy = (known after apply)
        }
    }

Plan: 1 to add, 0 to change, 0 to destroy.

Changes to Outputs:
  + resource_aka  = (known after apply)
  + resource_id   = (known after apply)
  + resource_name = "turbottest13658"
aws_iam_role.test_role: Creating...
aws_iam_role.test_role: Creation complete after 2s [id=turbottest13658]

Apply complete! Resources: 1 added, 0 changed, 0 destroyed.

Outputs:

resource_aka = "arn:aws:iam::999988887777:role/turbottest13658"
resource_id = "AROATB3DWERA5XMIEMRWZ"
resource_name = "turbottest13658"

Running SQL query: test-get-query.sql
[
  {
    "access_level": "public",
    "allowed_organization_ids": [],
    "allowed_principal_account_ids": [
      "012345678901"
    ],
    "allowed_principal_services": [
      "cloudwatch.amazonaws.com",
      "ec2.amazonaws.com"
    ],
    "allowed_principals": [
      "012345678901"
    ],
    "arn": "arn:aws:iam::999988887777:role/turbottest13658",
    "is_public": true,
    "name": "turbottest13658",
    "private_access_levels": [],
    "public_access_levels": [
      "Write"
    ],
    "public_statement_ids": [
      "PublicService"
    ],
    "role_id": "AROATB3DWERA5XMIEMRWZ",
    "shared_access_levels": [
      "Write"
    ],
    "shared_statement_ids": [
      "RestrictedService"
    ]
  }
]
✔ PASSED

Running SQL query: test-list-query.sql
[
  {
    "access_level": "public",
    "allowed_organization_ids": [],
    "allowed_principal_account_ids": [
      "012345678901"
    ],
    "allowed_principal_services": [
      "cloudwatch.amazonaws.com",
      "ec2.amazonaws.com"
    ],
    "allowed_principals": [
      "012345678901"
    ],
    "arn": "arn:aws:iam::999988887777:role/turbottest13658",
    "is_public": true,
    "name": "turbottest13658",
    "private_access_levels": [],
    "public_access_levels": [
      "Write"
    ],
    "public_statement_ids": [
      "PublicService"
    ],
    "role_id": "AROATB3DWERA5XMIEMRWZ",
    "shared_access_levels": [
      "Write"
    ],
    "shared_statement_ids": [
      "RestrictedService"
    ]
  }
]
✔ PASSED

Running SQL query: test-notfound-query.sql
null
✔ PASSED

POSTTEST: tests/aws_resource_policy_analysis

TEARDOWN: tests/aws_resource_policy_analysis

SUMMARY:

1/1 passed.

Example query results

Results
Query:

```sql
select
  r.name,
  r.arn
from
  aws_s3_bucket as r,
  aws_resource_policy_analysis as pa
where
  pa.is_public = true
  and pa.account_id = r.account_id
  and pa.policy = r.policy_std
order by
  r.name

Result:

+-------------------------------------------+--------------------------------------------------------+
| name                                      | arn                                                    |
+-------------------------------------------+--------------------------------------------------------+
| aws-cloudtrail-logs-333344445555-84bb46df | arn:aws:s3:::aws-cloudtrail-logs-333344445555-84bb46df |
| omero-resource-policy-bucket              | arn:aws:s3:::omero-resource-policy-bucket              |
+-------------------------------------------+--------------------------------------------------------+

Query:

select
  r.name,
  pa.access_level,
  pa.allowed_principal_account_ids,
  pa.allowed_principals,
  pa.allowed_principal_services,
  pa.allowed_organization_ids,
  r.arn
from
  aws_s3_bucket as r,
  aws_resource_policy_analysis as pa
where
  pa.account_id = r.account_id
  and pa.policy = r.policy_std
order by
  r.name

Result:

+-------------------------------------------+--------------+---------------------------------+---------------------------------------------------------------------+------------------------------+--------------------------+--------------------------------------------------------+
| name                                      | access_level | allowed_principal_account_ids   | allowed_principals                                                  | allowed_principal_services   | allowed_organization_ids | arn                                                    |
+-------------------------------------------+--------------+---------------------------------+---------------------------------------------------------------------+------------------------------+--------------------------+--------------------------------------------------------+
| account-tags-test-bucket                  | shared       | ["222244446666"]                | ["arn:aws:iam::222244446666:root"]                                  | []                           | []                       | arn:aws:s3:::account-tags-test-bucket                  |
| aws-cloudtrail-logs-333344445555-84bb46df | public       | []                              | []                                                                  | ["cloudtrail.amazonaws.com"] | []                       | arn:aws:s3:::aws-cloudtrail-logs-333344445555-84bb46df |
| config-bucket-333344445555                | private      | ["333344445555"]                | ["333344445555"]                                                    | ["config.amazonaws.com"]     | []                       | arn:aws:s3:::config-bucket-333344445555                |
| omero-cloudfront-test-bucket              | private      | ["333344445555"]                | ["333344445555"]                                                    | ["cloudtrail.amazonaws.com"] | []                       | arn:aws:s3:::omero-cloudfront-test-bucket              |
| omero-resource-policy-bucket              | public       | ["666655554444","222244446666"] | ["arn:aws:iam::666655554444:root","arn:aws:iam::222244446666:root"] | ["ec2.amazonaws.com"]        | ["o-c3a5y4wd52"]         | arn:aws:s3:::omero-resource-policy-bucket              |
+-------------------------------------------+--------------+---------------------------------+---------------------------------------------------------------------+------------------------------+--------------------------+--------------------------------------------------------+

Query:

select
  r.name,
  pa.is_public,
  pa.allowed_principal_account_ids,
  pa.allowed_principals,
  pa.allowed_principal_services,
  pa.allowed_organization_ids,
  pa.allowed_principal_federated_identities,
  r.arn
from
  aws_iam_role as r,
  aws_resource_policy_analysis as pa
where
  pa.account_id = r.account_id
  and pa.policy = r.assume_role_policy_std
order by
  r.name

Result:

+-------------------------------------------+-----------+---------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------+----------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------+
| name                                      | is_public | allowed_principal_account_ids                                 | allowed_principals                                                                                                                    | allowed_principal_services                                                                                            | allowed_organization_ids | allowed_principal_federated_identities                                                             | arn                                                                                   |
+-------------------------------------------+-----------+---------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------+----------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------+
| test-admin-role                           | false     | ["123456789012"]                                              | ["arn:aws:iam::123456789012:root"]                                                                                                    | []                                                                                                                    | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-admin-role                                        |
| test-amazon-1                             | false     | []                                                            | []                                                                                                                                    | []                                                                                                                    | []                       | ["www.amazon.com"]                                                                                 | arn:aws:iam::123456789012:role/test-amazon-1                                          |
| test-aws-amazon-sub-type-1                | false     | []                                                            | []                                                                                                                                    | []                                                                                                                    | []                       | ["arn:aws:iam::123456789012:saml-provider/AWSSSO_hidden_hidden_hidden_hidden_12"]                  | arn:aws:iam::123456789012:role/test-aws-amazon-sub-type-1                             |
| test-aws-is-broken                        | true      | []                                                            | []                                                                                                                                    | ["lambda.amazonaws.com"]                                                                                              | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-aws-is-broken                                     |
| test-cross-account-function-role-j28907ss | true      | []                                                            | []                                                                                                                                    | ["lambda.amazonaws.com"]                                                                                              | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/service-role/test-cross-account-function-role-j28907ss |
| test-dead-lambda-role                     | true      | []                                                            | []                                                                                                                                    | ["lambda.amazonaws.com"]                                                                                              | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/service-role/test-dead-lambda-role                     |
| test-function-2-role-i16umoc8             | true      | []                                                            | []                                                                                                                                    | ["lambda.amazonaws.com"]                                                                                              | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/service-role/test-function-2-role-i16umoc8             |
| test-function-3-role-ofc3xrg2             | true      | []                                                            | []                                                                                                                                    | ["lambda.amazonaws.com"]                                                                                              | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/service-role/test-function-3-role-ofc3xrg2             |
| test-function-4-role-bjzyzpti             | true      | []                                                            | []                                                                                                                                    | ["lambda.amazonaws.com"]                                                                                              | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/service-role/test-function-4-role-bjzyzpti             |
| test-function-role-ouk9m007               | true      | []                                                            | []                                                                                                                                    | ["lambda.amazonaws.com"]                                                                                              | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/service-role/test-function-role-ouk9m007               |
| test-google-1                             | false     | []                                                            | []                                                                                                                                    | []                                                                                                                    | []                       | ["accounts.google.com"]                                                                            | arn:aws:iam::123456789012:role/test-google-1                                          |
| test-google-2                             | false     | []                                                            | []                                                                                                                                    | []                                                                                                                    | []                       | ["accounts.google.com"]                                                                            | arn:aws:iam::123456789012:role/test-google-2                                          |
| test-google-role                          | false     | []                                                            | []                                                                                                                                    | []                                                                                                                    | []                       | ["accounts.google.com"]                                                                            | arn:aws:iam::123456789012:role/test-google-role                                       |
| test-public-1                             | true      | ["*"]                                                         | ["*"]                                                                                                                                 | []                                                                                                                    | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-public-1                                          |
| test-role-2                               | true      | ["222255559999"]                                              | ["arn:aws:iam::222255559999:root"]                                                                                                    | ["ecs.amazonaws.com"]                                                                                                 | []                       | ["cognito-identity.amazonaws.com"]                                                                 | arn:aws:iam::123456789012:role/test-role-2                                            |
| test-role-3                               | true      | []                                                            | []                                                                                                                                    | []                                                                                                                    | []                       | ["arn:aws:iam::222255559999:saml-provider/provider-name"]                                          | arn:aws:iam::123456789012:role/test-role-3                                            |
| test-role-mulitple                        | false     | ["111122226666","222255559999"]                               | ["arn:aws:iam::111122226666:root","arn:aws:iam::222255559999:root"]                                                                   | []                                                                                                                    | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-role-mulitple                                     |
| test-role-mulitple-2                      | false     | ["555522225555","876587658765","111122226666","222255559999"] | ["arn:aws:iam::555522225555:root","arn:aws:iam::876587658765:root","arn:aws:iam::111122226666:root","arn:aws:iam::222255559999:root"] | []                                                                                                                    | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-role-mulitple-2                                   |
| test-role-org-1                           | false     | ["123456789012"]                                              | ["arn:aws:iam::123456789012:root"]                                                                                                    | []                                                                                                                    | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-role-org-1                                        |
| test-role-org-2                           | false     | ["123456789012"]                                              | ["arn:aws:iam::123456789012:root"]                                                                                                    | []                                                                                                                    | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-role-org-2                                        |
| test-role-org-3                           | false     | ["123456789012"]                                              | ["arn:aws:iam::123456789012:root"]                                                                                                    | []                                                                                                                    | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-role-org-3                                        |
| test-role-org-4                           | true      | ["*"]                                                         | ["*"]                                                                                                                                 | []                                                                                                                    | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-role-org-4                                        |
| test-role-org-5                           | false     | ["123456789012"]                                              | ["arn:aws:iam::123456789012:root"]                                                                                                    | []                                                                                                                    | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-role-org-5                                        |
| test-role-public-2                        | true      | ["*"]                                                         | ["*"]                                                                                                                                 | ["ec2.amazonaws.com"]                                                                                                 | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-role-public-2                                     |
| test-role-public-3                        | true      | ["*"]                                                         | ["*"]                                                                                                                                 | ["ec2.amazonaws.com","ecs.amazonaws.com"]                                                                             | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-role-public-3                                     |
| test-role-public-4                        | true      | ["*"]                                                         | ["*"]                                                                                                                                 | ["cloudwatch.amazonaws.com","ec2.amazonaws.com","ecs.amazonaws.com"]                                                  | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-role-public-4                                     |
| test-role-public-5                        | true      | ["*"]                                                         | ["*"]                                                                                                                                 | ["cloudwatch.amazonaws.com","ec2.amazonaws.com","ecs.amazonaws.com","fsx.amazonaws.com"]                              | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-role-public-5                                     |
| test-role-self                            | false     | ["123456789012"]                                              | ["arn:aws:iam::123456789012:root"]                                                                                                    | []                                                                                                                    | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-role-self                                         |
| test-saml-role                            | false     | []                                                            | []                                                                                                                                    | []                                                                                                                    | []                       | ["arn:aws:iam::123456789012:saml-provider/AWSSSO_hidden_hidden_hidden_hidden_12"]                  | arn:aws:iam::123456789012:role/test-saml-role                                         |
| test-saml-role-1                          | false     | []                                                            | []                                                                                                                                    | []                                                                                                                    | []                       | ["arn:aws:iam::123456789012:saml-provider/AWSSSO_hidden_hidden_hidden_hidden_12"]                  | arn:aws:iam::123456789012:role/test-saml-role-1                                       |
| test-service-role-1                       | true      | []                                                            | []                                                                                                                                    | ["access-analyzer.amazonaws.com"]                                                                                     | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-service-role-1                                    |
| test-service-role-2                       | true      | []                                                            | []                                                                                                                                    | ["access-analyzer.amazonaws.com","backup.amazonaws.com"]                                                              | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-service-role-2                                    |
| test-service-role-3                       | true      | []                                                            | []                                                                                                                                    | ["access-analyzer.amazonaws.com","backup.amazonaws.com","cloudtrail.amazonaws.com"]                                   | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-service-role-3                                    |
| test-service-role-4                       | true      | []                                                            | []                                                                                                                                    | ["access-analyzer.amazonaws.com","backup.amazonaws.com","cloudtrail.amazonaws.com","globalaccelerator.amazonaws.com"] | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-service-role-4                                    |
| test-service-role-5                       | true      | []                                                            | []                                                                                                                                    | ["access-analyzer.amazonaws.com","backup.amazonaws.com","ec2.amazonaws.com","globalaccelerator.amazonaws.com"]        | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-service-role-5                                    |
| test-service-role-6                       | true      | []                                                            | []                                                                                                                                    | ["ec2.amazonaws.com"]                                                                                                 | []                       | []                                                                                                 | arn:aws:iam::123456789012:role/test-service-role-6                                    |
| test-web-identity-1                       | false     | []                                                            | []                                                                                                                                    | []                                                                                                                    | []                       | ["accounts.google.com"]                                                                            | arn:aws:iam::123456789012:role/test-web-identity-1                                    |
+-------------------------------------------+-----------+---------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------+----------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------+

Query:

select
  right(aliases -> 0 ->> 'AliasName', -6) as alias,
  pa.public_statement_ids,
  pa.shared_statement_ids,
  r.id,
  r.arn
from
  aws_kms_key as r,
  aws_resource_policy_analysis as pa
where
  pa.account_id = r.account_id
  and pa.policy = r.policy_std
  and r.key_manager = 'CUSTOMER'
order by
  r.id

Result:

+-------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+--------------------------------------+-----------------------------------------------------------------------------+
| alias                         | public_statement_ids                                                                                                                                                                                     | shared_statement_ids            | id                                   | arn                                                                         |
+-------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+--------------------------------------+-----------------------------------------------------------------------------+
| single-region-shared-multiple | []                                                                                                                                                                                                       | ["Enable IAM User Permissions"] | 252013a6-5107-49b3-8b46-972f071fe2ca | arn:aws:kms:us-east-1:333344445555:key/252013a6-5107-49b3-8b46-972f071fe2ca |
| cloud-trail-kms-alias         | ["Allow CloudTrail to describe key","Allow CloudTrail to encrypt logs","Allow alias creation during setup","Allow principals in the account to decrypt log files","Enable cross account log decryption"] | []                              | 62a473ea-2733-44eb-a626-352318acced6 | arn:aws:kms:us-east-1:333344445555:key/62a473ea-2733-44eb-a626-352318acced6 |
| single-region-private         | []                                                                                                                                                                                                       | []                              | 9692e4a3-4c7f-4857-bfd3-fa78ca0e9b00 | arn:aws:kms:us-east-1:333344445555:key/9692e4a3-4c7f-4857-bfd3-fa78ca0e9b00 |
| single-region-public          | ["Enable IAM User Permissions"]                                                                                                                                                                          | []                              | bfc9225b-b522-46ca-8084-31b022f6b225 | arn:aws:kms:us-east-1:333344445555:key/bfc9225b-b522-46ca-8084-31b022f6b225 |
| single-region-shared          | []                                                                                                                                                                                                       | ["Enable IAM User Permissions"] | c0ce0e39-5be9-4990-81f8-3bd8bb9bdd37 | arn:aws:kms:us-east-1:333344445555:key/c0ce0e39-5be9-4990-81f8-3bd8bb9bdd37 |
+-------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+--------------------------------------+-----------------------------------------------------------------------------+

Query:

select
  r.name,
  pa.public_access_levels,
  pa.shared_access_levels,
  pa.private_access_levels,
  r.arn
from
  aws_lambda_function as r,
  aws_resource_policy_analysis as pa
where
  pa.account_id = r.account_id
  and pa.policy = r.policy_std
order by
  r.name

Result:

+-----------------------+----------------------+----------------------+-----------------------+----------------------------------------------------------------------+
| name                  | public_access_levels | shared_access_levels | private_access_levels | arn                                                                  |
+-----------------------+----------------------+----------------------+-----------------------+----------------------------------------------------------------------+
| test-function         | []                   | []                   | ["Tagging"]           | arn:aws:lambda:us-east-1:333344445555:function:test-function         |
| test-function-3       | []                   | ["Tagging"]          | []                    | arn:aws:lambda:us-east-1:333344445555:function:test-function-3       |
| test-function-4       | []                   | ["Read"]             | []                    | arn:aws:lambda:us-east-1:333344445555:function:test-function-4       |
| test-private-function | []                   | []                   | ["Write"]             | arn:aws:lambda:us-east-1:333344445555:function:test-private-function |
+-----------------------+----------------------+----------------------+-----------------------+----------------------------------------------------------------------+

Query:

select
  r.name,
  pa.public_access_levels,
  pa.shared_access_levels,
  pa.private_access_levels,
  pa.allowed_principal_account_ids,
  pa.allowed_principals,
  pa.allowed_principal_services,
  pa.allowed_organization_ids,
  r.arn
from
  aws_lambda_function as r,
  aws_resource_policy_analysis as pa
where
  pa.account_id = r.account_id
  and pa.policy = r.policy_std
  and (
    pa.public_access_levels <@ '["Tagging", "Write"]'
    or pa.shared_access_levels <@ '["Tagging", "Write"]'
    or pa.private_access_levels <@ '["Tagging", "Write"]'
  )
order by
  r.name

Result:

+-----------------------+----------------------+----------------------+-----------------------+---------------------------------+--------------------------------------------------------------+----------------------------+--------------------------+----------------------------------------------------------------------+
| name                  | public_access_levels | shared_access_levels | private_access_levels | allowed_principal_account_ids   | allowed_principals                                           | allowed_principal_services | allowed_organization_ids | arn                                                                  |
+-----------------------+----------------------+----------------------+-----------------------+---------------------------------+--------------------------------------------------------------+----------------------------+--------------------------+----------------------------------------------------------------------+
| test-function         | []                   | []                   | ["Tagging"]           | ["333344445555"]                | ["arn:aws:iam::333344445555:role/iam_trusted_access_role_6"] | []                         | []                       | arn:aws:lambda:us-east-1:333344445555:function:test-function         |
| test-function-3       | []                   | ["Tagging"]          | []                    | ["222244446666"]                | ["222244446666"]                                             | ["s3.amazonaws.com"]       | []                       | arn:aws:lambda:us-east-1:333344445555:function:test-function-3       |
| test-function-4       | []                   | ["Read"]             | []                    | ["222244446666","097350876455"] | ["222244446666","097350876455"]                              | ["s3.amazonaws.com"]       | []                       | arn:aws:lambda:us-east-1:333344445555:function:test-function-4       |
| test-private-function | []                   | []                   | ["Write"]             | ["333344445555"]                | ["333344445555"]                                             | ["s3.amazonaws.com"]       | []                       | arn:aws:lambda:us-east-1:333344445555:function:test-private-function |
+-----------------------+----------------------+----------------------+-----------------------+---------------------------------+--------------------------------------------------------------+----------------------------+--------------------------+----------------------------------------------------------------------+

Query:

select
  pa.allowed_principal_account_ids
from
  aws_efs_file_system as r,
  aws_resource_policy_analysis as pa
where
  pa.account_id = r.account_id
  and pa.policy = r.policy_std
  and not pa.allowed_principal_account_ids <@ '["555544443333", "111122223333"]'
  and pa.is_public = false
  and jsonb_array_length(pa.shared_statement_ids) > 0

Result:

+---------------------------------+
| allowed_principal_account_ids   |
+---------------------------------+
| ["123412341234","987698769876"] |
+---------------------------------+

Query:

select
  pa.is_public,
  pa.allowed_principal_account_ids,
  pa.allowed_principals,
  pa.allowed_principal_services,
  pa.allowed_organization_ids
from
  aws_resource_policy_analysis as pa
where
  account_id = '111122223333'
  and policy = '
  {
    "Version": "2012-10-17",
    "Id": "Policy1658140668960",
    "Statement": [
      {
        "Sid": "AllowedPricipal",
        "Effect": "Allow",
        "Principal": { "AWS": "arn:aws:iam::111122223333:root" },
        "Resource": "arn:aws:s3:::test-bucket",
        "Action": "s3:*"
      },
      {
        "Sid": "AllowedService",
        "Effect": "Allow",
        "Principal": { "Service": "ec2.amazonaws.com" },
        "Resource": "arn:aws:s3:::test-bucket",
        "Action": "s3:*",
        "Condition": { "StringEquals": { "aws:SourceAccount": "555566667777" } }
      },
      {
        "Sid": "AllowedOrganization",
        "Effect": "Allow",
        "Principal": { "AWS": "*" },
        "Action": "s3:*",
        "Resource": "arn:aws:s3:::test-bucket",
        "Condition": { "StringEquals": { "aws:PrincipalOrgID": "o-aaabbbccc123" } }
      }
    ]
  }
  '

Result:

+-----------+-------------------------------------+-------------------------------------------------------+----------------------------+--------------------------+
| is_public | allowed_principal_account_ids       | allowed_principals                                    | allowed_principal_services | allowed_organization_ids |
+-----------+-------------------------------------+-------------------------------------------------------+----------------------------+--------------------------+
| false     | ["*","111122223333","555566667777"] | ["*","555566667777","arn:aws:iam::111122223333:root"] | ["ec2.amazonaws.com"]      | ["o-aaabbbccc123"]       |
+-----------+-------------------------------------+-------------------------------------------------------+----------------------------+--------------------------+

Query:

select
  pa.is_public,
  pa.allowed_principal_account_ids,
  pa.allowed_principals,
  pa.allowed_principal_services,
  pa.allowed_organization_ids,
  pa.allowed_principal_federated_identities
from
  aws_resource_policy_analysis as pa
where
  account_id = '111122223333'
  and policy = '
  {
    "Version": "2012-10-17",
    "Statement": [
      {
        "Sid": "AwsPrincipal",
        "Effect": "Allow",
        "Principal": { "AWS": "arn:aws:iam::444455556666:root" },
        "Action": "sts:AssumeRole"
      },
      {
        "Sid": "Federated",
        "Effect": "Allow",
        "Principal": { "Federated": "arn:aws:iam::111122223333:saml-provider/SSO" },
        "Action": "sts:AssumeRoleWithSAML",
        "Condition": { "StringEquals": { "SAML:aud": "aud" } }
      },
      {
        "Sid": "Service",
        "Effect": "Allow",
        "Principal": { "Service": "ec2.amazonaws.com" },
        "Action": "sts:AssumeRole",
        "Condition": { "StringEquals": { "aws:SourceAccount": "666655554444" } }
      },
      {
        "Sid": "Organization",
        "Effect": "Allow",
        "Principal": { "AWS": "*" },
        "Action": "sts:AssumeRole",
        "Condition": { "StringEquals": { "aws:PrincipalOrgID": "o-aaabbbccc123" } }
      },
      {
        "Sid": "WebIdentity",
        "Effect": "Allow",
        "Principal": { "Federated": "accounts.google.com" },
        "Action": "sts:AssumeRoleWithWebIdentity",
        "Condition": { "StringEquals": { "accounts.google.com:aud": "aud" } }
      }
    ]
  }
  '

Result:

+-----------+-------------------------------------+-------------------------------------------------------+----------------------------+--------------------------+-----------------------------------------------------------------------+
| is_public | allowed_principal_account_ids       | allowed_principals                                    | allowed_principal_services | allowed_organization_ids | allowed_principal_federated_identities                                |
+-----------+-------------------------------------+-------------------------------------------------------+----------------------------+--------------------------+-----------------------------------------------------------------------+
| false     | ["*","666655554444","444455556666"] | ["*","666655554444","arn:aws:iam::444455556666:root"] | ["ec2.amazonaws.com"]      | ["o-aaabbbccc123"]       | ["accounts.google.com","arn:aws:iam::111122223333:saml-provider/SSO"] |
+-----------+-------------------------------------+-------------------------------------------------------+----------------------------+--------------------------+-----------------------------------------------------------------------+
</details>

omerosaienni avatar Aug 31 '22 01:08 omerosaienni