Postgres Plugin for Steampipe

[irthomasthomas]

• Postgres Plugin for Steampipe

Postgres Plugin for Steampipe

DESCRIPTION:

Apache-2.0 license
Postgres Plugin for Steampipe

Use SQL to query data from plain PostgreSQL databases.

This repo contains a Steampipe plugin that exposes plain PostgreSQL databases as Steampipe tables, much like the CSV plugin does for CSV files, or like a reverse proxy does for HTTP. This can be used to join API data with semi-static data that is hosted on databases.

See below for an example that mixes data from a static DB (contact information for the teams that own Kubernetes namespaces) and data from Kubernetes (which namespaces have Failed pods). This may be part of an automated alerting system that runs periodically and sends emails.

URL: https://github.com/jreyesr/steampipe-plugin-postgres

Suggested labels

{'label-name': 'Database Integration', 'label-description': 'Tools and plugins for integrating databases into Steampipe queries.', 'gh-repo': 'https://github.com/jreyesr/steampipe-plugin-postgres', 'confidence': 57.69}

[irthomasthomas]

Related issues

#461: Running Steampipe extensions in sqlite-utils and Datasette | Simon Willison’s TILs

### Details

Similarity score: 0.88 - [ ] [Running Steampipe extensions in sqlite-utils and Datasette | Simon Willison’s TILs](https://til.simonwillison.net/sqlite/steampipe#user-content-running-extensions-in-datasette)

Running Steampipe extensions in sqlite-utils and Datasette

Steampipe is a build software that enables you to query different APIs directly from SQL databases. The original product was designed for PostgreSQL, but last week, they announced support for SQLite, along with ports of 100 of their existing extensions.

Here's the full list of SQLite extensions they released.

To install and use these extensions, you can either use their CLI tool or access the .so files directly via GitHub releases.

This guide will demonstrate how to run these extensions on a Mac using both sqlite-utils and Datasette.

Downloading an extension

Let's start with the Hacker News API plugin, turbot/steampipe-plugin-hackernews. This plugin doesn't require an API key, making it a great choice for this example.

First, grab the latest release of the extension. For an M2 MacBook, download the steampipe_sqlite_hackernews.darwin_arm64.tar.gz file:

curl -OL https://github.com/turbot/steampipe-plugin-hackernews/releases/download/v0.8.1/steampipe_sqlite_hackernews.darwin_arm64.tar.gz
tar -xzvf steampipe_sqlite_hackernews.darwin_arm64.tar.gz

Now, you have a steampipe_sqlite_hackernews.so file.

Loading extensions with sqlite-utils

With sqlite-utils installed, you can run the following command:

sqlite-utils memory --load-extension steampipe_sqlite_hackernews.so \
  'select id, title, time from hackernews_top limit 3'

This will produce a warning box due to the unsigned binary. To bypass this error, open the system Security preferences pane, find the option to allow unsigned binaries, and click "Allow Anyway". Then, try running the command again.

After allowing the extension, you'll see another dialog. Click "Open" to run the script correctly.

Now, you can run the command again:

sqlite-utils memory --load-extension steampipe_sqlite_hackernews.so \
  'select id, title, time from hackernews_top limit 3'

You should receive the response:

[
  {
    "id": 38706914,
    "title": "Gameboy Music and Sound Archive for MIDI",
    "time": "2023-12-20 09:45:05"
  },
  {
    "id": 38717114,
    "title": "Show HN: Talk to any ArXiv paper just by changing the URL",
    "time": "2023-12-21 04:48:20"
  },
  {
    "id": 38716075,
    "title": "OpenAI Begins Tackling ChatGPT Data Leak Vulnerability",
    "time": "2023-12-21 01:38:10"
  }
]

You've successfully queried the Hacker News API using SQL!

Running extensions in Datasette

After enabling an extension, it can be used directly with Datasette as well. Let's try using the turbot/steampipe-plugin-crtsh plugin for querying certificate transparency logs.

Download the latest steampipe_sqlite_crtsh.darwin_arm64.tar.gz file for macOS:

curl -OL https://github.com/turbot/steampipe-plugin-crtsh/releases/download/v0.4.0/steampipe_sqlite_crtsh.darwin_arm64.tar.gz
tar -xzvf steampipe_sqlite_crtsh.darwin_arm64.tar.gz

Now, load both extensions using Datasette:

datasette \
  --load-extension steampipe_sqlite_crtsh.so \
  --load-extension steampipe_sqlite_hackernews.so \
  --setting sql_time_limit_ms 20000

The --setting sql_time_limit_ms 20000 line increases the default time limit on SQL queries from 1s to 20s, which is useful for some of these API calls since they can be a little slow.

You can now query the certificate transparency log with SQL:

select
  dns_names,
  not_after
from
  crtsh_certificate
where
  query = 'datasette.io'
order by not_after desc;

Here's a more complex query demonstrating a CTE, JSON processing, and a join across two virtual tables:

with post_ids as (
  select
    value
  from
    json_each(submitted),
    hackernews_user
  where
    hackernews_user.id = 'simonw'
  limit
    20
)

select
  *
from
  hackernews_item
where
  hackernews_item.id in (
    select
      value
    from
      post_ids
  )
order by time desc

For more information on the kind of queries you can run, refer to the following documentation:

Tables in crt.sh
Tables in Hacker News

Suggested labels

{ "label-name": "Steampipe", "description": "Querying APIs from SQL databases with Steampipe extensions", "confidence": 97.71 }

#537: Steampipe Hub | GitHub plugin | github_issue table

### Details

Similarity score: 0.87 - [ ] [github_issue table | GitHub plugin | Steampipe Hub](https://hub.steampipe.io/plugins/turbot/github/tables/github_issue)

TITLE: github_issue table | GitHub plugin | Steampipe Hub

DESCRIPTION: Table: github_issue - Query GitHub Issues using SQL

GitHub Issues is a feature within GitHub that allows users to track bugs, enhancements, or other requests. It provides a platform for users to collaborate on problems, discuss complex details, and manage updates on ongoing issues. GitHub Issues helps in tracking individual tasks within a project, linking tasks to each other, and organizing tasks into manageable units.

Table Usage Guide

The table provides insights into issues within GitHub repositories. As a project manager or developer, explore issue-specific details through this table, including status, assignees, labels, and associated metadata. Utilize it to uncover information about issues, such as those that are overdue, the collaboration between team members on certain issues, and the overall progress of issues within a project.

Important Notes

• You must specify the (owner/repository) column in or clause to query the table.
• The pull requests are technically also issues in GitHub, however we do not include them in the table; You should use the table to query PRs.

Examples

List the issues in a repository

Explore the status and assignment of issues within a specific GitHub repository to better manage project tasks and responsibilities. This can help in tracking task progress and identifying bottlenecks in the project workflow.

Error:
No DBURL given

sql [-hnr] [--table-size] [--db-size] [-p pass-through] [-s string] dburl [command]

List the unassigned open issues in a repository

Identify instances where there are open issues in a specific repository that have not been assigned to anyone. This is useful to ensure all issues are being addressed and no task is left unattended.

Error:
No DBURL given

sql [-hnr] [--table-size] [--db-size] [-p pass-through] [-s string] dburl [command]

URL: https://hub.steampipe.io/plugins/turbot/github/tables/github_issue

Suggested labels

{'label-name': 'issue-management', 'label-description': 'Tracking and managing project tasks and responsibilities using GitHub Issues.', 'confidence': 69.63}