How to Run CloudQuery with PostgREST

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 fully automated Restful API query layer with PostgREST (opens in a new tab). 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 (opens in a new tab)

• Datastore: PostgreSQL

• API Access Layer: PostgREST (opens in a new tab) and any of your favorite API browsers if you wish (like Postman (opens in a new tab) 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 github.com/cloudquery/terraform-aws-cloudquery (opens in a new tab) , or GCP and Cloud SQL at https://github.com/cloudquery/terraform-gcp-cloudquery (opens in a new tab)

Step 2: Install PostgREST

Full full details, checkout PostgREST (opens in a new tab) 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 (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, or name using GraphQL!

Step 5: Deploying in production

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