# Steampipe + Flowpipe

> How to use Steampipe, in a Flowpipe pipelines, to query and act across accounts and regions

By Turbot Team
Published: 2024-03-08


In [The Flowpipe query trigger](https://flowpipe.io/blog/query-trigger) we show how to use a Steampipe query as a Flowpipe [trigger](https://flowpipe.io/docs/flowpipe-hcl/trigger). You can also use Steampipe in a Flowpipe [query step](https://flowpipe.io/docs/flowpipe-hcl/step/query). Here's a basic Flowpipe pipeline with a query step.

```hcl
pipeline "s3_buckets" {

  step "query" "list_buckets" {
    database = "postgres://steampipe@localhost:9193/steampipe"
    sql = <<EOQ
      select 
        name,
        region,
        account_id,
        _ctx->>'connection_name' as connection_name
      from
	      aws_s3_bucket
    EOQ
  }

  output "buckets" {
    value = step.query.list_buckets.rows
  }

}
```

If you're using a Steampipe [aggregator](https://steampipe.io/docs/managing/connections#querying-multiple-connections) to query across multiple accounts, the results may include buckets from each account/region pair. Now suppose you want to take action on all those buckets, using the [Put S3 Bucket Versioning](https://hub.flowpipe.io/mods/turbot/aws/pipelines/aws.pipeline.put_s3_bucket_versioning) (from the [Flowipe AWS mod](https://hub.flowpipe.io/mods/turbot/aws)) to ensure they are all versioned. The `put-bucket-versioning` pipeline needs to authenticate to one account at a time, because under the covers it's using an AWS CLI command like this one:

```bash
aws s3api put-bucket-versioning --bucket flowpipe-steampipe-demo --versioning-configuration Status=Enabled --profile=dundermifflin
```

Now suppose your Steampipe aggregator mentions these connections:

```hcl
connection "all_aws" {
  plugin = "aws"
  type = "aggregator"
  connections = ["dundermifflin", "sso"]
}

connection "dundermifflin" {
  plugin = "aws"
  profile = "dundermifflin"
}

connection "sso" {
  plugin = "aws"
  profile = "SSO-ReadOnly-605491513981"
}
```

It would be handy to automagically use all the aggregated Steampipe connections as Flowpipe credentials. And now, thanks to the new [credential_import](https://flowpipe.io/docs/reference/config-files/credential_import#credential-import) block, you can.

## Use credential_import to leverage all your Steampipe connections

Here's the recipe for this bit of magic:

```hcl
credential_import "steampipe" {
  source      = "~/.steampipe/config/aws.spc"
  connections = ["*"]
}
```

Place this block in a `.fpc` file that lives either at the root of your Flowpipe mod or in the `~/.flowpipe/config` directory. With the `credential_import` block in place, we can refer to those connections in Flowpipe.

```hcl
pipeline "show_imported_creds" {

  output "dundermifflin" {
    value = credential.aws["dundermifflin"].access_key
  }

  output "sso" {
    value = credential.aws["sso"].access_key
  }
  
}
```

```bash
[flowpipe] Execution ID: exec_cncj082nn1tmbi9udci0
[show_imported_creds] Starting pipeline
[show_imported_creds] Output dundermifflin = REDACTED
[show_imported_creds] Output sso = REDACTED
[show_imported_creds] Complete 696ms
```

Now let's look at the output of the above Steampipe query.

```
+-------------------------------------------+----------------+--------------+-----------------+
| name                                      | region         | account_id   | connection_name |
+-------------------------------------------+----------------+--------------+-----------------+
| cf-templates-1s5tzrjxv4j52-us-west-1      | us-west-1      | 605491513981 | sso             |
| aws-cloudtrail-logs-605491513981-45df8af0 | us-east-1      | 605491513981 | sso             |
| jon-turbot-test-bucket-01                 | us-east-1      | 605491513981 | sso             |
| aws-cloudtrail-regional-test              | us-east-2      | 899206412154 | dundermifflin   |
| 10k-with-bucket-kms                       | us-east-2      | 899206412154 | dundermifflin   |
| cf-templates-1ldox7k310167-us-east-2      | us-east-2      | 899206412154 | dundermifflin   |
| cf-templates-1ldox7k310167-ca-central-1   | ca-central-1   | 899206412154 | dundermifflin   |
```

We have all the ingredients needed to create a parent pipeline that iterates over buckets, and dynamically uses connection names to enable a child pipeline to act on each connection. Let's put them all together.

```hcl
pipeline "ensure_bucket_versioning" {

  step "query" "list_buckets" {
    database = "postgres://steampipe@localhost:9193/steampipe"
    sql = <<EOQ
      select 
        name,
        region,
        account_id,
        _ctx->>'connection_name' as connection_name
      from
	      aws_s3_bucket
      limit 6
    EOQ
  }
  
  step "pipeline" "version_the_buckets" {
    for_each = step.query.list_buckets.rows
    pipeline = pipeline.version_the_bucket
    args = {
      row = each.value
    }
  }

}

pipeline "version_the_bucket" {

  param "row" {
    type = map
  }

  output "row" {
    value = param.row
  }

  step "pipeline" "call_library_mod" {
    pipeline = aws.pipeline.put_s3_bucket_versioning
    args = {
      bucket = param.row.name
      versioning = true
      region = param.row.region
      cred = param.row.connection_name
    }

  }

}
```

## Steampipe + Flowpipe: Better together

Use Steampipe and Flowpipe together to query resources across multiple accounts and regions, then act on those resources in a targeted and efficient way. The `credential_import` block can make all your Steampipe connections available as Flowpipe credentials, so whether you're managing a handful of accounts or hundreds, you can translate query results into actions performed in all of them. Give it a try and [let us know](/community/join) how it goes.