In this blog post, 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.
ETL (Extract-Transform-Load) ingestion layer: CloudQuery
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.
Step 1: Install or Deploy CloudQuery
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 , 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:[email protected]:5432/postgres"
db-schemas = "public"
db-anon-role = "postgres"
and run the following
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.
You can also use any filter on any of the fields which PostgREST automatically exposes.
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
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.