How to Query CloudQuery with PostGraphile

Name

Yevgeny Pats

Twitter

@yevgenypats

In this how-to guide, we will walk you through how to set up CloudQuery (opens in a new tab) to build your cloud asset inventory in PostgreSQL and build a GraphQL API query layer with PostGraphile (opens in a new tab) on top of it. this can be used to build different use cases on from search to security, cost and infrastructure automation.

General Architecture

• ETL (Extract-Transform-Load) ingestion layer: CloudQuery (opens in a new tab)
• Datastore: PostgreSQL
• API Access Layer: PostGraphile (opens in a new tab) and GraphiQL (opens in a new tab)

What You Will Get

• Raw SQL access to all your cloud asset inventory to create views or explore any questions or connections between resources.
• Multi-Cloud Asset Inventory: Ingest configuration from all your clouds to a single datastore with a unified structure.
• GraphQL Endpoint to access and query all your cloud configurations.

Walkthrough

Step 1: Install or Deploy CloudQuery (fetch)

If it’s your first time using CloudQuery we suggest you first run it locally to get familiar with the tool. Take a look at our quickstart guide.

If you are already familiar with CloudQuery, take a look at how to deploy it to AWS on RDS Aurora and EKS at github.com/cloudquery/terraform-aws-cloudquery (opens in a new tab) .

Step 2: Install PostGraphile

For full details, check out the PostGraphile (opens in a new tab) docs. If you are running locally will need Node.js and you can install PostGraphile globally via npm i -g postgraphile or (brew install PostGraphile)

To run PostGraphile locally all you need to do is the following (adjust the Postgres URL accordingly):

postgraphile -c "postgres://postgres:pass@localhost:5432/postgres" --enhance-graphiql --skip-plugins graphile-build:NodePlugin --simple-collections only -p 6060

Step 3: Query and Profit!

That’s it! The output of a successful run is presented below:

PostGraphile v4.12.9 server listening on port 6060 🚀

  ‣ GraphQL API:         http://localhost:6060/graphql
  ‣ GraphiQL GUI/IDE:    http://localhost:6060/graphiql
  ‣ Postgres connection: postgres://postgres:[SECRET]@localhost/postgres
  ‣ Postgres schema(s):  public
  ‣ Documentation:       https://graphile.org/postgraphile/introduction/
  ‣ Node.js version:     v18.3.0 on darwin arm64
  ‣ Join PostHog in supporting PostGraphile development: https://graphile.org/sponsor/

* * *

Open the browser with the http://localhost:6060/graphiql endpoint to see the GraphiQL UI where you can compose any query you want interactively:

Step 4: Create New Views

By default PostGraphile exposes all tables and relationships of the existing tables but let’s say you want to create a new view. All you need to do is to create a new view and PostGraphile will automatically generate the model for that. For example, check out this blog (opens in a new tab) on how to create a unified AWS resource view (opens in a new tab) (or GCP View (opens in a new tab)). And just like that you can now query and search all your resources by arn, tags, name with GraphQL!

Step 5: Deploying in production

If you want to expose PostGraphile publicly please see PostGraphile (opens in a new tab) Security or expose it privately and use either a bastion host or something like Tailscale on Kubernetes (opens in a new tab) together with our helm charts. (opens in a new tab)

Summary

In this post we showed you how to build an open-source cloud asset inventory with CloudQuery as the ETL (Extract-Transform-Load) / data-ingestion layer and PostGraphile (opens in a new tab) as the API layer to expose the data for your internal team/users or any other downstream processing in the most convenient/preferred way.