<a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/us

Wow <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url=

<a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/us

Multiple connections - how to access different accounts at the same time? about steampipe-plugin-aws HOT 4 CLOSED

turbot commented on July 29, 2024 2

Multiple connections - how to access different accounts at the same time?

from steampipe-plugin-aws.

Comments (4)

johnsmyth commented on July 29, 2024 6

@aidansteele - I did want to add, you can create views from the union queries and then select from the views instead. Use at our own risk, but you can use this function to generate such views. Given a 'model' schema and a pattern, the function will create a view for all tables in the model that is a union of all tables in schemas that match the pattern. The view will start with an underscore:

CREATE OR REPLACE FUNCTION public.generate_consolidated_views(model_schema TEXT, schema_pattern TEXT)  RETURNS void as $$
    DECLARE 
        table_name TEXT;
        schema RECORD;
        sql TEXT;
    BEGIN
        FOR table_name in EXECUTE
            format('select table_name from information_schema.tables where table_schema = %L', model_schema)
        LOOP        
            sql := '';
            FOR schema IN EXECUTE
                format('SELECT schema_name FROM information_schema.schemata WHERE schema_name like %L', schema_pattern)
            LOOP
                sql := sql || format('SELECT * FROM %I.%I UNION ALL ', schema.schema_name, table_name);
            END LOOP;
            EXECUTE
                format('CREATE OR REPLACE VIEW public.%I AS ', '_' || table_name) || left(sql, -11);
        END LOOP;
    END
$$ LANGUAGE plpgsql;

I named all my aws connections prefixed with aws_ and used the default aws schema as the model:

select public.generate_consolidated_views('aws', 'aws_%');

Which creates these views that are all unions of all my aws_ schemas:

steampipe> \dv public.*                                                                                                                                                                                                            
+----------+-----------------------------------------+--------+-----------+
| Schema   | Name                                    | Type   | Owner     |
|----------+-----------------------------------------+--------+-----------|
| public   | _aws_account                            | view   | steampipe |
| public   | _aws_acm_certificate                    | view   | steampipe |
| public   | _aws_api_gateway_api_key                | view   | steampipe |
| public   | _aws_api_gateway_authorizer             | view   | steampipe |
| public   | _aws_api_gateway_rest_api               | view   | steampipe |
| public   | _aws_api_gateway_stage                  | view   | steampipe |
| public   | _aws_api_gateway_usage_plan             | view   | steampipe |
| public   | _aws_api_gatewayv2_api                  | view   | steampipe |
| public   | _aws_api_gatewayv2_domain_name          | view   | steampipe |
| public   | _aws_api_gatewayv2_stage                | view   | steampipe |
| public   | _aws_availability_zone                  | view   | steampipe |
| public   | _aws_cloudformation_stack               | view   | steampipe |
| public   | _aws_cloudwatch_log_group               | view   | steampipe |
| public   | _aws_cloudwatch_log_metric_filter       | view   | steampipe |
| public   | _aws_dynamodb_backup                    | view   | steampipe |
| public   | _aws_dynamodb_global_table              | view   | steampipe |
| public   | _aws_dynamodb_table                     | view   | steampipe |
| public   | _aws_ebs_snapshot                       | view   | steampipe |
| public   | _aws_ebs_volume                         | view   | steampipe |
| public   | _aws_ec2_ami                            | view   | steampipe |
| public   | _aws_ec2_application_load_balancer      | view   | steampipe |
| public   | _aws_ec2_autoscaling_group              | view   | steampipe |
| public   | _aws_ec2_classic_load_balancer          | view   | steampipe |
| public   | _aws_ec2_instance                       | view   | steampipe |
| public   | _aws_ec2_instance_availability          | view   | steampipe |
| public   | _aws_ec2_instance_type                  | view   | steampipe |
| public   | _aws_ec2_key_pair                       | view   | steampipe |
| public   | _aws_ec2_launch_configuration           | view   | steampipe |
| public   | _aws_ec2_load_balancer_listener         | view   | steampipe |
| public   | _aws_ec2_network_interface              | view   | steampipe |
| public   | _aws_ec2_network_load_balancer          | view   | steampipe |
| public   | _aws_ec2_target_group                   | view   | steampipe |
| public   | _aws_ec2_transit_gateway                | view   | steampipe |
| public   | _aws_ec2_transit_gateway_route_table    | view   | steampipe |
| public   | _aws_ec2_transit_gateway_vpc_attachment | view   | steampipe |
| public   | _aws_iam_access_advisor                 | view   | steampipe |
| public   | _aws_iam_access_key                     | view   | steampipe |
| public   | _aws_iam_account_password_policy        | view   | steampipe |
| public   | _aws_iam_account_summary                | view   | steampipe |
| public   | _aws_iam_action                         | view   | steampipe |
| public   | _aws_iam_credential_report              | view   | steampipe |
| public   | _aws_iam_group                          | view   | steampipe |
| public   | _aws_iam_policy                         | view   | steampipe |
| public   | _aws_iam_policy_simulator               | view   | steampipe |
| public   | _aws_iam_role                           | view   | steampipe |
| public   | _aws_iam_user                           | view   | steampipe |
| public   | _aws_kms_key                            | view   | steampipe |
| public   | _aws_lambda_alias                       | view   | steampipe |
| public   | _aws_lambda_function                    | view   | steampipe |
| public   | _aws_lambda_version                     | view   | steampipe |
| public   | _aws_rds_db_cluster                     | view   | steampipe |
| public   | _aws_rds_db_cluster_parameter_group     | view   | steampipe |
| public   | _aws_rds_db_cluster_snapshot            | view   | steampipe |
| public   | _aws_rds_db_instance                    | view   | steampipe |
| public   | _aws_rds_db_option_group                | view   | steampipe |
| public   | _aws_rds_db_parameter_group             | view   | steampipe |
| public   | _aws_rds_db_snapshot                    | view   | steampipe |
| public   | _aws_rds_db_subnet_group                | view   | steampipe |
| public   | _aws_region                             | view   | steampipe |
| public   | _aws_route53_record                     | view   | steampipe |
| public   | _aws_route53_zone                       | view   | steampipe |
| public   | _aws_s3_account_settings                | view   | steampipe |
| public   | _aws_s3_bucket                          | view   | steampipe |
| public   | _aws_sns_topic                          | view   | steampipe |
| public   | _aws_sns_topic_subscription             | view   | steampipe |
| public   | _aws_sqs_queue                          | view   | steampipe |
| public   | _aws_ssm_parameter                      | view   | steampipe |
| public   | _aws_vpc                                | view   | steampipe |
| public   | _aws_vpc_customer_gateway               | view   | steampipe |
| public   | _aws_vpc_dhcp_options                   | view   | steampipe |
| public   | _aws_vpc_egress_only_internet_gateway   | view   | steampipe |
| public   | _aws_vpc_eip                            | view   | steampipe |
| public   | _aws_vpc_endpoint                       | view   | steampipe |
| public   | _aws_vpc_endpoint_service               | view   | steampipe |
| public   | _aws_vpc_internet_gateway               | view   | steampipe |
| public   | _aws_vpc_nat_gateway                    | view   | steampipe |
| public   | _aws_vpc_network_acl                    | view   | steampipe |
| public   | _aws_vpc_route                          | view   | steampipe |
| public   | _aws_vpc_route_table                    | view   | steampipe |
| public   | _aws_vpc_security_group                 | view   | steampipe |
| public   | _aws_vpc_security_group_rule            | view   | steampipe |
| public   | _aws_vpc_subnet                         | view   | steampipe |
| public   | _aws_vpc_vpn_gateway                    | view   | steampipe |
+----------+-----------------------------------------+--------+-----------+

so i can do select * from _aws_s3_bucket and get all the buckets for all my aws connections, for example. Note that we dont currently back up the public schema and updates may overwrite it, so back up anything important that you do there....

from steampipe-plugin-aws.

aidansteele commented on July 29, 2024 1

Some further details: I am excited by the possibility of using steampipe to easily query my entire AWS org. What I was hoping to do was have a connection per account and then write queries like:

select * from acctA.aws_s3_bucket
union all select * from acctB.aws_s3_bucket
union all select * from acctC.aws_s3_bucket

Which would let me list all buckets across accounts A, B and C.

This works ok for a small number of accounts, but makes for some verbose queries when you have a lot of accounts. So then I thought maybe it makes sense to employ table inheritance. This way you could have e.g. 10 AWS connections, each of which has all the AWS tables - and those tables all inherit from base tables in a "base" schema. Then I could instead do select * from aws_base.aws_s3_bucket which would achieve the same as the union all above.

I think that something similar could be done for AWS regions. This would make steampipe go from being a very cool tool to an indispensable part of an AWS org admin's toolkit IMO. What do you think? If you think it could be useful, then I could start looking at the source code and try to come up with a more concrete proposal for how it could be implemented.

from steampipe-plugin-aws.

e-gineer commented on July 29, 2024

Wow @aidansteele ... thanks for trying Steampipe and sharing your thoughtful insights!

As you've discovered, we are planning for multi-connection support, but decided it was time to ship something :-)

I'm sure @johnsmyth will want to expand more, but here are some initial comments on our current thinking around connections:

Each connection is a separate schema (as you've identified).
A connection should (but doesn't yet) support multiple regions. The default will be 1 (the default region) for speed. This will likely be implemented using go channels within the plugin for parallel queries.
Each connection will likely be a single account, to keep credential management sensible.

Table inheritance is a powerful angle for Steampipe to leverage. @johnsmyth did some exploration to prove the potential, but we mostly focused on it in the context of "resource interfaces". You'll notice each table includes common columns (title, akas and tags) that are available across clouds. It didn't make it into v0.1.0, but the idea was to use table inheritance to make those queryable across resource types. Your inheritance approach is similar, but targets the same table across schemas rather than different tables within the same schema.

To wrap up:

Does our planned architecture & breakup of multi-connection / multi-region make sense to you?
As you can tell, we'd definitely be interested in discussing a proposal for table inheritance across accounts.

Also ... in case it's helpful ... Steampipe leaves the public schema in the steampipe database open for creating tables / etc. You can create views / materialized views / etc in that area that leverage the connections.

from steampipe-plugin-aws.

johnsmyth commented on July 29, 2024

@aidansteele - we released steampipe 0.2.0 and aws plugin 0.5.0 today, which support multi-region aws connections, as well as multiple aws connections to different accounts. I have to admit that I was pretty floored when you posted this issue, as you pretty much nailed our design.
The details are on the hub site but it mostly works as you described. You can have multiple connections, with each connection specifying a profile or aws key pair, as well as a list of regions. We are planning on digging into inheritance as well, and I did a POC a while back that looks promising. For now you'll have to use the union all queries. I have some concerns about running out of local resources like sockets and handles when we implement inherited tables, so we working through some throttling and caching stuff first. BTW - caching is also in 0.2.0 but off by default as its still "experimental". You can enable by setting the STEAMPIPE_CACHE environment variable to true. The default ttl is 300 (5 minutes) but you can also control this via an env var (STEAMPIPE_CACHE_TTL)

I'm going to close this issue out unless you object, but we really appreciate your feedback and input. Feel free to ask any other questions, open issues, request features etc!

from steampipe-plugin-aws.

Multiple connections - how to access different accounts at the same time? about steampipe-plugin-aws HOT 4 CLOSED

Comments (4)

Related Issues (20)

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent