tutorial

How to Run CloudQuery with PostgREST

Yevgeny Pats • August 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 fully automated RESTful API query layer with PostgREST. This can be used as a base for many custom use cases: from infrastructure search to security, cost and infrastructure automation.

General Architecture

ETL (Extract-Transform-Load) ingestion layer: CloudQuery
Datastore: PostgreSQL
API Access Layer: PostgREST and any of your favorite API browsers if you wish (like Postman or any other)

What You Will Get

Raw SQL access to all of 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.
Rest API Endpoint to access and query all your cloud configurations.

Walkthrough

Step 1: Install or Deploy CloudQuery

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 https://github.com/cloudquery/terraform-aws-cloudquery , or GCP and Cloud SQL at https://github.com/cloudquery/terraform-gcp-cloudquery

Step 2: Install PostgREST

Full full details, checkout PostgREST docs. If you are on mac you can install it via brew install postgrest

To run it locally, all you need is the following cq.conf file as input for PostgREST (adjust the PG URL accordingly):

db-uri = "postgres://postgres:pass@localhost:5432/postgres"
db-schemas = "public"
db-anon-role = "postgres"

and run the following

postgrest cq.confg

Step 3: Query and Profit!

That’s it! You should see something like the following in the output if all is well:

12/Jun/2022:23:36:20 +0300: Attempting to connect to the database...
12/Jun/2022:23:36:20 +0300: Connection successful
12/Jun/2022:23:36:20 +0300: Listening on port 3000
12/Jun/2022:23:36:20 +0300: Config re-loaded
12/Jun/2022:23:36:20 +0300: Listening for notifications on the pgrst channel
12/Jun/2022:23:36:20 +0300: Schema cache loaded

Now you can query the endpoint with curl or any other API browser/UI, Swagger UI.

curl "http://localhost:3000/aws_ec2_instances"

You can also use any filter on any of the fields which PostgREST automatically exposes.

curl "http://localhost:3000/aws_ec2_instances?arn=eq.arnsomething"

Step 4: Create New Views

By default, PostgREST exposes all tables and relationships of the existing table. But let’s say you want to create a new view. All you need to do is create the new view, and PostgREST will automatically generate the model for that. For example, checkout 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, or name using GraphQL!

Step 5: Deploying in production

If you want to expose PostgREST publicly please see PostgREST Security. Or, expose it privately and use either a bastion host or something like Tailscale 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 PostgREST 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