Steampipe (devops-ia/steampipe)

Steampipe

https://github.com/devops-ia/steampipe
Admin
Steampipe is a tool that uses SQL to instantly query cloud services including AWS, Azure, GCP and...

Tokens:13,183
Snippets:134
Trust Score:7.5
Update:1 day ago
Show doc for...
Context Summary (auto-generated)
Raw
# Steampipe Docker Image

Steampipe is an open-source tool that uses SQL to query cloud services including AWS, Azure, GCP, and over 140 other platforms. This community Docker image project provides multi-architecture container images (linux/amd64, linux/arm64) built from official Steampipe binaries, filling the gap after Turbot discontinued official Docker images following version 0.22.0.

The container exposes a PostgreSQL-compatible endpoint on port 9193, allowing you to connect with any SQL client, BI tool, or programming language that supports PostgreSQL. The image is pre-configured with container-optimized defaults for telemetry, update checking, and memory limits, making it ready for production deployment in Docker, Docker Compose, or Kubernetes environments via the companion Helm chart.

## Running as a PostgreSQL Service

Start Steampipe as a persistent database service accessible on port 9193 for external connections from SQL clients.

```bash
# Create a named volume for plugin persistence across container restarts
docker volume create steampipe-data

# Run Steampipe as a background service with network access enabled
docker run -d --name steampipe \
  -p 9193:9193 \
  -v steampipe-data:/home/steampipe/.steampipe \
  -e STEAMPIPE_DATABASE_PASSWORD=mypassword \
  ghcr.io/devops-ia/steampipe:2.4.1 \
  steampipe service start --foreground --database-listen network

# Verify the service is running
docker logs steampipe
# Output: "Database is now running"

# Test database connectivity
docker exec steampipe pg_isready -h localhost -p 9193 -U steampipe
# Output: localhost:9193 - accepting connections
```

## Running as an Interactive Query Shell

Execute a one-off interactive SQL session for quick queries without starting a persistent service.

```bash
# Launch an interactive query shell
docker run -it --rm \
  ghcr.io/devops-ia/steampipe:2.4.1 \
  steampipe query

# Run a single query and exit
docker run -it --rm \
  ghcr.io/devops-ia/steampipe:2.4.1 \
  steampipe query "select 1 as test"
# Output:
# +------+
# | test |
# +------+
# | 1    |
# +------+
```

## Installing Plugins

Plugins extend Steampipe to query specific cloud providers and services. Install plugins at runtime and persist them using Docker volumes.

```bash
# Install the AWS plugin
docker exec steampipe steampipe plugin install aws

# Install multiple plugins at once
docker exec steampipe steampipe plugin install aws azure gcp

# List all installed plugins
docker exec steampipe steampipe plugin list
# Output:
# +--------+---------+-------------+
# | Name   | Version | Connections |
# +--------+---------+-------------+
# | aws    | 0.141.0 | aws         |
# | azure  | 0.63.0  | azure       |
# | gcp    | 0.54.0  | gcp         |
# +--------+---------+-------------+
```

## Connecting with PostgreSQL Clients

Connect to the Steampipe service using any PostgreSQL-compatible client including psql, DBeaver, TablePlus, or DataGrip.

```bash
# Connect using psql
psql -h localhost -p 9193 -U steampipe -d steampipe

# Run a query file
psql -h localhost -p 9193 -U steampipe -d steampipe -f my-query.sql

# Export results to CSV
psql -h localhost -p 9193 -U steampipe -d steampipe \
  -c "select name, region from aws_s3_bucket" \
  --csv > buckets.csv

# Connection parameters for GUI clients:
# Host:     localhost
# Port:     9193
# Database: steampipe
# User:     steampipe
# Password: (value of STEAMPIPE_DATABASE_PASSWORD)
```

## Querying AWS Resources

Query AWS services using SQL after installing and configuring the AWS plugin with appropriate credentials.

```bash
# Install AWS plugin
docker exec steampipe steampipe plugin install aws

# List all S3 buckets sorted by creation date
docker exec steampipe steampipe query \
  "select name, region, creation_date from aws_s3_bucket order by creation_date desc"

# Find public S3 buckets (security audit)
docker exec steampipe steampipe query \
  "select name, region from aws_s3_bucket where bucket_policy_is_public = true"

# List EC2 instances by state
docker exec steampipe steampipe query \
  "select instance_id, instance_type, instance_state, region from aws_ec2_instance order by instance_state"

# Find IAM users with console access but no MFA enabled
docker exec steampipe steampipe query "
SELECT
  user_name,
  create_date,
  password_last_used
FROM aws_iam_user
WHERE
  password_enabled = true
  AND mfa_enabled = false
ORDER BY create_date
"

# Find security groups with unrestricted inbound access
docker exec steampipe steampipe query "
SELECT
  group_id,
  group_name,
  description,
  region
FROM aws_vpc_security_group
WHERE
  EXISTS (
    SELECT 1
    FROM jsonb_array_elements(ip_permissions) AS p
    WHERE p->>'IpRanges' LIKE '%0.0.0.0/0%'
  )
ORDER BY region, group_name
"
```

## Configuring AWS Plugin Credentials

Configure AWS plugin authentication using environment variables or mounted AWS credentials files.

```bash
# Create aws.spc configuration file
cat > aws.spc << 'EOF'
connection "aws" {
  plugin  = "aws"
  regions = ["us-east-1", "eu-west-1", "ap-southeast-1"]
}
EOF

# Option 1: Pass credentials via environment variables
docker run -d --name steampipe \
  -p 9193:9193 \
  -v "$PWD/aws.spc:/home/steampipe/.steampipe/config/aws.spc:ro" \
  -e AWS_ACCESS_KEY_ID=AKIAIOSFODNN7EXAMPLE \
  -e AWS_SECRET_ACCESS_KEY=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY \
  -e AWS_DEFAULT_REGION=us-east-1 \
  ghcr.io/devops-ia/steampipe:2.4.1 \
  steampipe service start --foreground --database-listen network

# Option 2: Mount AWS credentials directory
docker run -d --name steampipe \
  -p 9193:9193 \
  -v "$HOME/.aws:/home/steampipe/.aws:ro" \
  -v "$PWD/aws.spc:/home/steampipe/.steampipe/config/aws.spc:ro" \
  ghcr.io/devops-ia/steampipe:2.4.1 \
  steampipe service start --foreground --database-listen network
```

## Multi-Account AWS Configuration with Aggregator

Configure multiple AWS accounts and query across all of them using an aggregator connection.

```hcl
# multi-account-aws.spc
connection "aws_dev" {
  plugin  = "aws"
  profile = "dev"
  regions = ["us-east-1"]
}

connection "aws_prod" {
  plugin  = "aws"
  profile = "prod"
  regions = ["us-east-1", "eu-west-1"]
}

# Aggregator combines all accounts into a single connection
connection "aws_all" {
  plugin      = "aws"
  type        = "aggregator"
  connections = ["aws_dev", "aws_prod"]
}
```

```bash
# Mount the config and credentials
docker run -d --name steampipe \
  -p 9193:9193 \
  -v "$PWD/multi-account-aws.spc:/home/steampipe/.steampipe/config/aws.spc:ro" \
  -v "$HOME/.aws:/home/steampipe/.aws:ro" \
  ghcr.io/devops-ia/steampipe:2.4.1 \
  steampipe service start --foreground --database-listen network

# Query across all accounts using the aggregator
docker exec steampipe steampipe query \
  "select _ctx->>'connection_name' as account, name, region from aws_all.aws_s3_bucket"
```

## Configuring GCP Plugin

Configure the GCP plugin with service account credentials for querying Google Cloud resources.

```hcl
# gcp.spc
connection "gcp" {
  plugin  = "gcp"
  project = "my-project-id"
}
```

```bash
# Mount GCP config and service account credentials
docker run -d --name steampipe \
  -p 9193:9193 \
  -v "$PWD/gcp.spc:/home/steampipe/.steampipe/config/gcp.spc:ro" \
  -v "$PWD/service-account.json:/home/steampipe/.config/gcloud/application_default_credentials.json:ro" \
  ghcr.io/devops-ia/steampipe:2.4.1 \
  steampipe service start --foreground --database-listen network

# Install GCP plugin
docker exec steampipe steampipe plugin install gcp
```

## Multi-Cloud Queries

Query and compare resources across multiple cloud providers in a single SQL statement.

```bash
# Install multiple cloud plugins
docker exec steampipe steampipe plugin install aws azure gcp

# Compare running VMs across AWS and Azure
docker exec steampipe steampipe query "
  select 'aws' as cloud, instance_id as id, instance_type as size, region
  from aws_ec2_instance where instance_state = 'running'
  union all
  select 'azure', id, size, location
  from azure_compute_virtual_machine where power_state = 'running'
  order by cloud, region
"
```

## Docker Compose Deployment

Deploy Steampipe as a service using Docker Compose with persistent storage and health checks.

```yaml
# docker-compose.yml
services:
  steampipe:
    image: ghcr.io/devops-ia/steampipe:2.4.1
    container_name: steampipe
    command: steampipe service start --foreground --database-listen network
    ports:
      - "9193:9193"
    volumes:
      - steampipe-data:/home/steampipe/.steampipe
    environment:
      STEAMPIPE_DATABASE_PASSWORD: steampipe
      STEAMPIPE_UPDATE_CHECK: "false"
      STEAMPIPE_TELEMETRY: none
      STEAMPIPE_LOG_LEVEL: warn
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -h localhost -p 9193 -U steampipe"]
      interval: 10s
      timeout: 5s
      retries: 10
      start_period: 30s
    restart: unless-stopped

volumes:
  steampipe-data:
```

```bash
# Start the service
docker compose up -d

# Install plugins
docker compose exec steampipe steampipe plugin install aws

# View logs
docker compose logs -f steampipe
```

## Steampipe with Powerpipe Dashboard

Deploy Steampipe alongside Powerpipe for compliance dashboards and visualizations.

```yaml
# docker-compose-with-powerpipe.yml
services:
  steampipe:
    image: ghcr.io/devops-ia/steampipe:2.4.1
    container_name: steampipe
    command: steampipe service start --foreground --database-listen network
    ports:
      - "9193:9193"
    volumes:
      - steampipe-data:/home/steampipe/.steampipe
    environment:
      STEAMPIPE_DATABASE_PASSWORD: steampipe
      STEAMPIPE_UPDATE_CHECK: "false"
      STEAMPIPE_TELEMETRY: none
      STEAMPIPE_LOG_LEVEL: warn
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -h localhost -p 9193 -U steampipe"]
      interval: 10s
      timeout: 5s
      retries: 10
      start_period: 30s
    restart: unless-stopped

  powerpipe:
    image: ghcr.io/devops-ia/powerpipe:1.5.1
    container_name: powerpipe
    ports:
      - "9033:9033"
    volumes:
      - workspace:/workspace
    environment:
      POWERPIPE_DATABASE: "postgres://steampipe:steampipe@steampipe:9193/steampipe"
      POWERPIPE_UPDATE_CHECK: "false"
      POWERPIPE_TELEMETRY: none
      POWERPIPE_LISTEN: network
    depends_on:
      steampipe:
        condition: service_healthy
    restart: unless-stopped

volumes:
  steampipe-data:
  workspace:
```

```bash
# Start both services
docker compose -f docker-compose-with-powerpipe.yml up -d

# Install AWS plugin in Steampipe
docker compose exec steampipe steampipe plugin install aws

# Install AWS compliance mod in Powerpipe
docker compose exec powerpipe powerpipe mod install github.com/turbot/steampipe-mod-aws-compliance

# Open the dashboard at http://localhost:9033
```

## Environment Variables

Configure Steampipe behavior using environment variables for memory, caching, logging, and timeouts.

```bash
# Run with custom environment configuration
docker run -d --name steampipe \
  -p 9193:9193 \
  -e STEAMPIPE_DATABASE_PASSWORD=supersecret \
  -e STEAMPIPE_UPDATE_CHECK=false \
  -e STEAMPIPE_TELEMETRY=none \
  -e STEAMPIPE_LOG_LEVEL=warn \
  -e STEAMPIPE_MEMORY_MAX_MB=2048 \
  -e STEAMPIPE_PLUGIN_MEMORY_MAX_MB=1024 \
  -e STEAMPIPE_CACHE=true \
  -e STEAMPIPE_CACHE_TTL=300 \
  -e STEAMPIPE_QUERY_TIMEOUT=240 \
  -e STEAMPIPE_MAX_PARALLEL=10 \
  ghcr.io/devops-ia/steampipe:2.4.1 \
  steampipe service start --foreground --database-listen network

# Environment variable reference:
# STEAMPIPE_DATABASE_PASSWORD  - PostgreSQL password (default: random)
# STEAMPIPE_UPDATE_CHECK       - Disable update checking (default: false)
# STEAMPIPE_TELEMETRY          - Disable telemetry (default: none)
# STEAMPIPE_LOG_LEVEL          - Log verbosity: trace/debug/info/warn/error (default: warn)
# STEAMPIPE_MEMORY_MAX_MB      - Process memory soft limit in MB (default: 1024)
# STEAMPIPE_PLUGIN_MEMORY_MAX_MB - Per-plugin memory limit in MB (default: 1024)
# STEAMPIPE_CACHE              - Enable query result cache (default: true)
# STEAMPIPE_CACHE_TTL          - Cache TTL in seconds (default: 300)
# STEAMPIPE_QUERY_TIMEOUT      - Query timeout in seconds (default: 240)
# STEAMPIPE_MAX_PARALLEL       - Maximum parallel executions (default: 10)
```

## Memory Tuning for Large Datasets

Configure memory limits for querying large cloud accounts with many resources.

```bash
# Increase memory limits for heavy workloads
docker run -d --name steampipe \
  --memory=6g --memory-swap=6g \
  -p 9193:9193 \
  -e STEAMPIPE_MEMORY_MAX_MB=4096 \
  -e STEAMPIPE_PLUGIN_MEMORY_MAX_MB=2048 \
  -e STEAMPIPE_MAX_PARALLEL=20 \
  ghcr.io/devops-ia/steampipe:2.4.1 \
  steampipe service start --foreground --database-listen network
```

## Exporting Query Results

Export query results in JSON, CSV, or table format for integration with other tools.

```bash
# Export to JSON
docker exec steampipe steampipe query \
  "select * from aws_s3_bucket" --output json > buckets.json

# Export to CSV
docker exec steampipe steampipe query \
  "select * from aws_s3_bucket" --output csv > buckets.csv

# Output as formatted table
docker exec steampipe steampipe query \
  "select name, region from aws_s3_bucket limit 10" --output table
```

## Connecting from Python

Query Steampipe from Python applications using the psycopg2 PostgreSQL driver.

```python
import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=9193,
    dbname="steampipe",
    user="steampipe",
    password="your-password",
    sslmode="disable",
)
cur = conn.cursor()
cur.execute("SELECT name, region FROM aws_s3_bucket")
for row in cur.fetchall():
    print(row)
conn.close()
```

## Connecting from Node.js

Query Steampipe from Node.js applications using the pg PostgreSQL client.

```javascript
const { Client } = require("pg");

const client = new Client({
  host: "localhost",
  port: 9193,
  database: "steampipe",
  user: "steampipe",
  password: "your-password",
  ssl: false,
});

await client.connect();
const res = await client.query("SELECT name, region FROM aws_s3_bucket");
console.log(res.rows);
await client.end();
```

## Connecting from Go

Query Steampipe from Go applications using the lib/pq PostgreSQL driver.

```go
package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
)

func main() {
    db, _ := sql.Open("postgres",
        "host=localhost port=9193 dbname=steampipe user=steampipe password=your-password sslmode=disable")
    defer db.Close()

    rows, _ := db.Query("SELECT name, region FROM aws_s3_bucket")
    defer rows.Close()
    for rows.Next() {
        var name, region string
        rows.Scan(&name, &region)
        fmt.Printf("%s (%s)\n", name, region)
    }
}
```

## Kubernetes Helm Deployment

Deploy Steampipe to Kubernetes using the official Helm chart with custom configuration.

```bash
# Add the Helm repository
helm repo add devops-ia https://devops-ia.github.io/helm-charts
helm repo update

# Install with basic configuration
helm install steampipe devops-ia/steampipe \
  --set image.repository=ghcr.io/devops-ia/steampipe \
  --set image.tag=2.4.1 \
  --set bbdd.enabled=true \
  --set bbdd.listen=network \
  --namespace steampipe \
  --create-namespace

# Upgrade to a new version
helm upgrade steampipe devops-ia/steampipe \
  --set image.tag=2.5.0 \
  --reuse-values
```

## Kubernetes Custom Values Configuration

Configure Steampipe Helm deployment with custom resources, environment variables, and secrets.

```yaml
# values.yaml
image:
  repository: ghcr.io/devops-ia/steampipe
  tag: "2.4.1"

bbdd:
  enabled: true
  listen: network
  port: 9193

resources:
  requests:
    cpu: "250m"
    memory: "512Mi"
  limits:
    cpu: "2000m"
    memory: "2Gi"

env:
  - name: STEAMPIPE_MEMORY_MAX_MB
    value: "1536"
  - name: STEAMPIPE_PLUGIN_MEMORY_MAX_MB
    value: "1024"
  - name: STEAMPIPE_DATABASE_PASSWORD
    valueFrom:
      secretKeyRef:
        name: steampipe-credentials
        key: password

initContainer:
  plugins:
    - aws
    - azure
    - gcp
```

```bash
helm install steampipe devops-ia/steampipe -f values.yaml \
  --namespace steampipe --create-namespace
```

## Kubernetes Secrets for Credentials

Store cloud provider credentials in Kubernetes Secrets for secure injection into the Steampipe container.

```yaml
# secret.yaml
apiVersion: v1
kind: Secret
metadata:
  name: aws-credentials
  namespace: steampipe
type: Opaque
stringData:
  AWS_ACCESS_KEY_ID: "AKIAIOSFODNN7EXAMPLE"
  AWS_SECRET_ACCESS_KEY: "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
  AWS_DEFAULT_REGION: "us-east-1"
```

```yaml
# Reference in Helm values
envFrom:
  - secretRef:
      name: aws-credentials
```

## Kubernetes ConfigMap for Plugin Configuration

Mount plugin configuration files from ConfigMaps into the Steampipe container.

```yaml
# configmap.yaml
apiVersion: v1
kind: ConfigMap
metadata:
  name: steampipe-plugin-config
  namespace: steampipe
data:
  aws.spc: |
    connection "aws" {
      plugin  = "aws"
      regions = ["us-east-1", "eu-west-1"]
    }
```

```yaml
# Reference in Helm values
extraVolumes:
  - name: plugin-config
    configMap:
      name: steampipe-plugin-config

extraVolumeMounts:
  - name: plugin-config
    mountPath: /home/steampipe/.steampipe/config/aws.spc
    subPath: aws.spc
    readOnly: true
```

## Kubernetes Health Checks

Configure liveness and readiness probes for Kubernetes deployments using pg_isready.

```yaml
livenessProbe:
  exec:
    command:
      - pg_isready
      - -h
      - localhost
      - -p
      - "9193"
      - -U
      - steampipe
  initialDelaySeconds: 30
  periodSeconds: 10

readinessProbe:
  exec:
    command:
      - pg_isready
      - -h
      - localhost
      - -p
      - "9193"
      - -U
      - steampipe
  initialDelaySeconds: 15
  periodSeconds: 5
```

```bash
# Connect from another pod in the same namespace
psql -h steampipe -p 9193 -U steampipe -d steampipe

# Using full service DNS from any namespace
psql -h steampipe.steampipe.svc.cluster.local -p 9193 -U steampipe -d steampipe
```

## Enabling Debug Mode

Enable detailed logging for troubleshooting connection issues, query failures, or plugin problems.

```bash
# Run with debug logging
docker run -d --name steampipe \
  -p 9193:9193 \
  -e STEAMPIPE_LOG_LEVEL=debug \
  ghcr.io/devops-ia/steampipe:2.4.1 \
  steampipe service start --foreground --database-listen network

# Follow the logs
docker logs -f steampipe
```

## Waiting for Service Readiness

Script to wait for Steampipe service to be fully ready before running queries.

```bash
# Wait for PostgreSQL to accept connections
until docker exec steampipe pg_isready -h localhost -p 9193 -U steampipe; do
  echo "Waiting for Steampipe..."; sleep 5
done
echo "Steampipe is ready!"

# Now safe to run queries
docker exec steampipe steampipe plugin install aws
```

## Summary

Steampipe Docker images are ideal for cloud infrastructure teams performing security audits, compliance checks, and resource inventory across multiple cloud providers. The SQL interface enables integration with existing BI tools, dashboards, and automation pipelines without learning provider-specific APIs. Common use cases include finding misconfigured resources, generating asset inventories, comparing configurations across environments, and building compliance dashboards with Powerpipe.

The container integrates seamlessly into CI/CD pipelines for automated security scanning, Kubernetes clusters for persistent cloud querying services, and local development environments for ad-hoc exploration. Plugin credentials are managed through standard cloud authentication mechanisms (environment variables, mounted credentials files, or Kubernetes Secrets), and the PostgreSQL-compatible interface means any language or tool with PostgreSQL support can query cloud resources using familiar SQL syntax.