Run some queries (provide the cluster_addr as a WHERE clause parameter):
select name, namespace, uid, creationTimestamp from k8s.core_v1.service_account where cluster_addr ='35.244.65.136' and namespace ='kube-system' orderby name asc;
Welcome your feedback by getting in touch or raising issues at stackql/stackql-provider-registry, give us some ⭐️ love while you are there!
StackQL allows you to query and interact with your cloud and SaaS assets using a simple SQL framework
Understanding entitlements across a GCP org with a complex hierarchy is a challenge. I have taken and data-centric approach to this in this article.
Prerequisites include setting up a Jupyter environment with StackQL (done here using Docker): stackql-jupyter-demo. You will also need a service account and associated key with the roles/iam.securityReviewer role.
This step includes importing the required libraries (pandas etc.) and instantiating a StackQL client with the service account creds you created before. You will supply your root node here using the org_id and org_name variables.
Next we will create some helper functions; these will help us enumerate nodes in the GCP org resource hierarchy and fetch and unnest IAM policies.
Create a dataframe containing all nodes in the resource hierarchy, including the root node (the organization), each folder with its subfolders, and projects. The functions used will search each folder in the hierarchy to find its subfolders and projects using a depth-first search approach.
Inspecting the output, it looks like this:
Create a dataset including each node and its associated IAM policies
This step will fetch all of the policies applied at each node in the data structure we created in the previous step.
The IAM policies response from SELECT role, members FROM google.cloudresourcemanager.project_iam_policies ... presents some challenges as members is a nested list which we need to unnest (or explode) along with the associated role and conditions (if they exist).
This bit of massaging will give us a SQL-friendly model we can use for analysis and join with another data source (such as a list of identities from an identity provider).
Inspecting the Final Output
We can now peek at the final data set, which looks like this:
What's next? You could now join this with data from your IdP, or other SaaS services to correlate entitlements across your entire estate. You could also drill into specific service accounts, users, or groups. Queries are run in real-time, so you can refresh the data by simply rerunning the cells.
Pull the Netlify provider (you only need to do this the first time you use the provider or when you are updating), the following command can be run from the StackQL interactive shell (stackql shell) or using stackql exec:
stackql >> SELECT created_at, name, state, branch FROM netlify.deploys.deploy >> WHERE site_id = 'ad26d902-9cb1-43be-90d9-284e8c7ac687' >> ORDER BY created_at DESC LIMIT 2; +--------------------------+------------+-------+-------------------------+ | created_at | name | state | branch | |--------------------------|------------|-------|-------------------------| | 2022-05-04T22:46:43.015Z | stackql-io | ready | main | |--------------------------|------------|-------|-------------------------| | 2022-05-04T22:39:34.958Z | stackql-io | ready | feature/content-updates | +--------------------------+------------+-------+-------------------------+
You can also use StackQL to provision resources in Netlify, the methods you saw in step 7 that are prefixed by create or insert, can be accessed using INSERT statements in StackQL, similarly methods prefixed by delete or remove can be accessed using DELETE statements.
more providers coming soon, if there is anything you are interested in specifically, get in contact and let us know.
Welcome your feedback by getting in touch or raising issues at stackql/stackql-provider-registry, give us some ⭐️ love while you are there!
The GitHub provider for StackQL is now generally available. This can be used to query resources in GitHub Cloud or GitHub Enterprise, including orgs, teams, users, repositories, branches, pull requests, issues, workflows/actions and much more!
you can now enumerate services, resources, attributes and methods in the github provider using the SHOW and DESCRIBE meta commands, for instance:
show services in github from either the StackQL command shell or via stackql exec would return something like...
+----------------------------+---------------------+------------------------------------------+ | id | name | title | +----------------------------+---------------------+------------------------------------------+ | actions_enterprises:v0.1.0 | actions_enterprises | GitHub v3 REST API - actions_enterprises | | billing:v0.1.0 | billing | GitHub v3 REST API - billing | | repos:v0.1.0 | repos | GitHub v3 REST API - repos | | ... | ... | ... | +----------------------------+---------------------+------------------------------------------+
tip
Use the EXTENDED operator with the SHOW or DESCRIBE commands to get additional information about services, resources, attributes and methods, e.g. DESCRIBE EXTENDED github.repos.repos
show resources in github.repos would return something like...
Multi cloud visibility, SecOps, FinOps, DevOps made easy
Today marks a significant epoch in the evolution of the InfraQL/StackQL project. The StackQL provider registry allows contributors to add support for different providers (major cloud, alt cloud and SaaS providers) using a no-code approach. Developers simply add extensions to the providers OpenAPI spec using configuration documents (currently supporting yaml and json – with future support for toml and hcl). These extensions allow StackQL to map an ORM to provider services, resources, and methods.
For example, for a future AWS provider you could run discovery commands such as:
SHOW SERVICES IN aws; /* shows the available services in AWS */ SHOW RESOURCES IN aws.ec2; /* shows the available resources in the AWS EC2 service */ DESCRIBE aws.ec2.instances; /* show available attributes in the aws.ec2.instances resource schema */ SHOW METHODS IN aws.ec2.instances; /* shows available lifecycle methods – such as start, stop, etc which can be involved using the EXEC command */
Or create a new EC2 instance using:
INSERTINTO aws.ec2.instances SELECT …;
View and report on instances and their properties using:
SELECT col(s)FROM aws.ec2.instances WHERE …;
Or clean up resources using:
DELETEFROM aws.ec2.instances WHERE …;
The StackQL beta version supporting the provider registry is available for Mac (arm and amd) and Linux, with a Windows version coming in the next few weeks.
Providers are currently available for Google and Okta, see StackQL Provider Registry repo and Developer Guide. We are encouraging developers to contribute – we would be happy to assist, just raise an issue or a PR.
