steampipe plugin install ocisteampipe plugin install oci

Oracle Cloud + Steampipe

Steampipe is an open-source zero-ETL engine to instantly query cloud APIs using SQL.

Oracle Cloud provides on-demand cloud computing platforms and APIs to authenticated customers on a metered pay-as-you-go basis.

For example:

select
name,
id,
is_mfa_activated
from
oci_identity_user;
+-----------------+------------------------+------------------+
| name | id | is_mfa_activated |
+-----------------+------------------------+------------------+
| pam_beesly | ocid1.user.oc1.aaaa... | false |
| creed_bratton | ocid1.user.oc1.aaaa... | true |
| stanley_hudson | ocid1.user.oc1.aaaa... | false |
| michael_scott | ocid1.user.oc1.aaaa... | false |
| dwight_schrute | ocid1.user.oc1.aaaa... | true |
+-----------------+------------------------+------------------+

Documentation

Get started

Install

Download and install the latest Oracle Cloud plugin:

steampipe plugin install oci

Credentials

ItemDescription
CredentialsCreate API keys for your user and add to default OCI configuration: ~/.oci/config
PermissionsUse policy builder to enable your group with following permissions:
  • Allow group {group_name} to read all-resources in tenancy
  • Allow group {group_name} to manage all-resources in tenancy where request.operation='GetConfiguration'
  • Note: Permission to manage GetConfiguration for all-resources is required for oci_identity_tenancy table.
    RadiusEach connection represents a single OCI Tenant.
    Resolution1. Static credentials in the configuration file with the tenancy_ocid, user_ocid, fingerprint and private_key_path arguments.
    2. Named profile from an OCI config file(~/.oci/config) with the config_file_profile argument.
    3. Named profile containing security token.
    4. Instance Principal based authentication. Note: this configuration will only work when run from an OCI instance.
    5. If no credentials are specified, the plugin will use the OCI Default Connection

    Configuration

    Installing the latest oci plugin will create a config file (~/.steampipe/config/oci.spc) with a single connection named oci:

    connection "oci_tenant_y" {
    plugin = "oci"
    # Name of the profile.
    #config_file_profile = "DEFAULT"
    # Path to config file
    #config_path = "~/.oci/config"
    # List of regions
    #regions = ["ap-mumbai-1", "us-ashburn-1"]
    # The maximum number of attempts (including the initial call) Steampipe will
    # make for failing API calls. Defaults to 9 and must be greater than or equal to 1.
    #max_error_retry_attempts = 9
    # The minimum retry delay in milliseconds after which retries will be performed.
    # This delay is also used as a base value when calculating the exponential backoff retry times.
    # Defaults to 25ms and must be greater than or equal to 1ms.
    #min_error_retry_delay = 25
    }
    • config_file_profile (Optional) OCI profile name to use for credentials.
    • config_path (Optional) Path of the config file where subjected profile is available.
    • max_error_retry_attempts (Optional) The maximum number of attempts (including the initial call) Steampipe will make for failing API calls. Defaults to 9 and must be greater than or equal to 1.
    • min_error_retry_delay (Optional) The minimum retry delay in milliseconds after which retries will be performed. This delay is also used as a base value when calculating the exponential backoff retry times. Defaults to 25ms and must be greater than or equal to 1ms.
    • regions (Optional) List of OCI regions Steampipe will connect to.

    Multi-Tenant Connections

    You may create multiple oci connections:

    connection "oci_dev" {
    plugin = "oci"
    config_file_profile = "oci_dev"
    regions = ["ap-mumbai-1", "us-ashburn-1"]
    }
    connection "oci_qa" {
    plugin = "oci"
    config_file_profile = "oci_qa"
    regions = ["sa-vinhedo-1", "ap-hyderabad-1"]
    }
    connection "oci_prod" {
    plugin = "oci"
    config_file_profile = "oci_prod"
    regions = ["ap-mumbai-1", "us-ashburn-1"]
    }

    Each connection is implemented as a distinct Postgres schema. As such, you can use qualified table names to query a specific connection:

    select
    *
    from
    oci_qa.oci_identity_user

    You can multi-account connections by using an aggregator connection. Aggregators allow you to query data from multiple connections for a plugin as if they are a single connection.

    connection "oci_all" {
    plugin = "oci"
    type = "aggregator"
    connections = ["oci_dev", "oci_qa", "oci_prod"]
    }

    Querying tables from this connection will return results from the oci_dev, oci_qa, and oci_prod connections:

    select
    *
    from
    oci_all.oci_identity_user

    Alternatively, can use an unqualified name and it will be resolved according to the Search Path. It's a good idea to name your aggregator first alphbetically, so that it is the first connection in the search path (i.e. oci_all comes before oci_dev):

    select
    *
    from
    oci_identity_user

    Steampipe supports the * wildcard in the connection names. For example, to aggregate all the OCI plugin connections whose names begin with oci_:

    connection "oci_all" {
    type = "aggregator"
    plugin = "oci"
    connections = ["oci_*"]
    }

    Advanced configuration options

    If you have an OCI profile setup for using the OCI CLI, Steampipe will just work with that connection.

    For users with multiple accounts and more complex authentication use cases, here are some examples of advanced configuration options:

    Use static credentials

    The OCI plugin allows you set static credentials with the tenancy_ocid, user_ocid, fingerprint and private_key_path arguments. You may select one or more regions with the regions argument.

    connection "oci_tenant_x" {
    plugin = "oci"
    tenancy_ocid = "ocid1.tenancy.oc1..aaaaaaaa111111111bbbbbbbetci3yjjnjqmfkr4pab12cd45gh56hm76cyljaq"
    user_ocid = "ocid1.user.oc1..aaaaaaaa111111111bbbbbbb2oixpabcd7a3jkl6yife75v7a7o6c5d6wclrsjia"
    fingerprint = "9a:a1:b2:c3:d4:e5:6f:7g:89:33:5f:ed:ab:ec:de:11"
    private_key_path = "~/.ssh/oci_private.pem" # Path to user's private key
    regions = ["ap-mumbai-1", "us-ashburn-1"] # List of regions to query resources
    }

    Using a named profile

    If you have an OCI config file(~/.oci/config) with multiple profiles setup, you can set the config_file_profile argument:

    connection "oci" {
    plugin = "oci"
    config_file_profile = "DEFAULT" # Name of the profile in the OCI config file
    config_path = "~/.oci/config" # Path to config file
    regions = ["ap-mumbai-1", "us-ashburn-1"] # List of regions to query resources
    }
    connection "oci_tenant_x" {
    plugin = "oci"
    config_file_profile = "tenant_x" # Name of the profile in the OCI config file
    config_path = "~/.oci/config" # Path to config file
    regions = ["ap-mumbai-1", "us-ashburn-1"] # List of regions to query resources
    }

    Using a named profile containing security token

    connection "oci_tenant_z" {
    plugin = "oci"
    auth = "SecurityToken" # Type of authentication
    config_file_profile = "tenant_z" # OCI Profile containing the details of the token
    regions = ["ap-mumbai-1"]
    }

    Instance principal based authentication

    This configuration will only work when run from an OCI instance. More information on using Instance Principals:

    connection "oci" {
    plugin = "oci"
    auth = "InstancePrincipal" # Type of authentication
    }

    Postgres FDW

    This plugin is available as a native Postgres FDW. Unlike Steampipe CLI, which ships with an embedded Postgres server instance, the Postgres FDW can be installed in any supported Postgres database version.

    You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_postgres_installer.sh script:

    /bin/sh -c "$(curl -fsSL https://steampipe.io/install/postgres.sh)" -- oci

    The installer will prompt you for the plugin name and version, download and install the appropriate files for your OS, system architecture, and Postgres version.

    To configure the Postgres FDW, you will create an extension, foreign server, and schema and import the foreign schema.

    CREATE EXTENSION IF NOT EXISTS steampipe_postgres_oci;
    CREATE SERVER steampipe_oci FOREIGN DATA WRAPPER steampipe_postgres_oci OPTIONS (config '<your_config>');
    CREATE SCHEMA oci;
    IMPORT FOREIGN SCHEMA oci FROM SERVER steampipe_oci INTO oci;

    SQLite Extension

    This plugin is available as a SQLite Extension, making the tables available as SQLite virtual tables.

    You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_sqlite_installer.sh script:

    /bin/sh -c "$(curl -fsSL https://steampipe.io/install/sqlite.sh)" -- oci

    The installer will prompt you for the plugin name, version, and destination directory. It will then determine the OS and system architecture, and it will download and install the appropriate package.

    To configure the SQLite extension, load the extension module and then run the steampipe_configure_oci function to configure it with plugin-specific options.

    $ sqlite3
    sqlite> .load ./steampipe_sqlite_extension_oci.so
    sqlite> select steampipe_configure_oci('<your_config>');

    Export

    This plugin is available as a standalone Export CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

    You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

    /bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- oci

    You can pass the configuration to the command with the --config argument:

    steampipe_export_oci --config '<your_config>' <table_name>