tutorial

How to Query CloudQuery with PostGraphile

Yevgeny Pats • Aug 15, 2021

In this how-to guide, we will walk you through how to set up CloudQuery to build your cloud asset inventory in PostgreSQL and build a GraphQL API query layer with PostGraphile 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
Datastore: PostgreSQL
API Access Layer: PostGraphile and GraphiQL

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 .

Step 2: Install PostGraphile

For full details, check out the PostGraphile 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 on how to create a unified AWS resource view (or GCP View). 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 Security or expose it privately and use either a bastion host or something like Tailscale on Kubernetes together with our helm charts.

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 as the API layer to expose the data for your internal team/users or any other downstream processing in the most convenient/preferred way.

On this page