How to Run CloudQuery with PostgREST
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.
ETL (Extract-Transform-Load) ingestion layer: CloudQuery (opens in a new tab)
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.
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)
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
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.
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!
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)
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.