Skip to main content

· 4 min read

Excited to announce the release of the Netlify provider for StackQL.

StackQL allows you to query and interact with your cloud and SaaS assets using a simple SQL framework

The netlify provider can be used to query, provision, de-provision or update sites, builds, deploys, functions, identities, domain_names and more.

Here are the steps to get started with the Netlify provider:

Setup​

  1. Create a personal access token for Netlify (if you don't have one already), go to https://app.netlify.com/user/applications/personal.

  2. Export the token to a variable and supply this as the provider authentication for StackQL: 

export NETLIFY_TOKEN=your_personal_access_token
AUTH_STR='{ "netlify": { "type": "api_key", "valuePrefix": "Bearer ", "credentialsenvvar": "NETLIFY_TOKEN" } }'
./stackql shell --auth="${AUTH_STR}"

Install the netlify provider​

  1. 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: 
registry pull netlify v0.1.0;

Show available services in netlify​

  1. (Optional) Show the available services in the Netlify provider: 
stackql >> show services in netlify;

+----------------------+---------------+-----------------------+
|          id          |     name      |       title           |
|----------------------|---------------|-----------------------|
| builds:v0.1.0        | builds        | Netlify site builds   |
|----------------------|---------------|-----------------------|
| deploys:v0.1.0       | deploys       | Netlify site deploys  |
|----------------------|---------------|-----------------------|
| user_accounts:v0.1.0 | user_accounts | Netlify user accounts |
|----------------------|---------------|-----------------------|
| ...                  | ...           | ...                   |
+----------------------+---------------+-----------------------+

Explore resources in netlify​

  1. (Optional) Explore resources in a service in the Netlify provider: 
stackql  >> show resources in netlify.deploys;

+----------------+--------------------------------+
|      name      |               id               |
|----------------|--------------------------------|
| deploy         | netlify.deploys.deploy         |
|----------------|--------------------------------|
| deployKey      | netlify.deploys.deployKey      |
|----------------|--------------------------------|
| deployedBranch | netlify.deploys.deployedBranch |
+----------------+--------------------------------+
  1. (Optional) List the available fields (or properties) of a resource: 
stackql  >> describe netlify.deploys.deploy;

+--------------------+---------+
|        name        |  type   |
|--------------------|---------|
| id                 | string  |
|--------------------|---------|
| name               | string  |
|--------------------|---------|
| url                | string  |
|--------------------|---------|
| review_id          | number  |
|--------------------|---------|
| commit_url         | string  |
|--------------------|---------|
| commit_ref         | string  |
|--------------------|---------|
| ...                | ...     |
+--------------------+---------+
  1. (Optional) Show the available methods in a resource (the stuff you can do with a resource..): 
stackql  >> show methods in netlify.deploys.deploy;

+--------------------+--------------------+
|     MethodName     |   RequiredParams   |
|--------------------|--------------------|
| cancelSiteDeploy   | deploy_id          |
|--------------------|--------------------|
| createSiteDeploy   | site_id            |
|--------------------|--------------------|
| getSiteDeploy      | deploy_id, site_id |
|--------------------|--------------------|
| listSiteDeploys    | site_id            |
|--------------------|--------------------|
| lockDeploy         | deploy_id          |
|--------------------|--------------------|
| ...                | ...                |
+--------------------+--------------------+

::: tip Methods prefixed by list and get are exposed via SELECT verbs, for example: 

SELECT id, name FROM netlify.deploys.deploy
WHERE site_id = 'ad26d902-9cb1-43be-90d9-284e8c7ac687';

this query accesses the listSiteDeploys method :::

Run some queries!​

  1. Run some queries... 
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!

enjoy!

· 4 min read

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!

See available providers​

You can see the versions of GitHub Provider (and other providers) available using:

stackql registry list

or from the StackQL Command Shell (stackql shell) using:

REGISTRY LIST;

this would return a list of all the providers that are currently available, for example: 

+----------+---------+
| provider | version |
+----------+---------+
| github   | v0.1.0  |
| google   | v0.1.0  |
| okta     | v0.1.0  |
+----------+---------+

Pull the github provider​

To pull v0.1.0 of the github provider use:

stackql registry pull github v0.1.0

or

REGSITRY PULL github v0.1.0;

to see what providers are installed use:

SHOW PROVIDERS;

this would return something like... 

+--------+
|  name  |
+--------+
| github |
+--------+

Explore the github provider and query public resources​

The provider and public objects can be queried without authentication as shown here: 

AUTH_STR='{"github": { "type": "null_auth" }}'
stackql shell --auth="${AUTH_STR}"

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... 

+--------------+---------------------------+
|     name     |            id             |
+--------------+---------------------------+
| branches     | github.repos.branches     |
| commits      | github.repos.commits      |
| deployments  | github.repos.deployments  |
| environments | github.repos.environments |
| forks        | github.repos.forks        |
| releases     | github.repos.releases     |
| repos        | github.repos.repos        |
| statistics   | github.repos.statistics   |
| statuses     | github.repos.statuses     |
| traffic      | github.repos.traffic      |
| ...          | ...                       |
+--------------+---------------------------+

to see fields in a resource (which can be queried or updated) use DESCRIBE for example DESCRIBE github.repos.commits would return something like... 

+--------------+--------+
|     name     |  type  |
+--------------+--------+
| files        | array  |
| stats        | object |
| commit       | object |
| url          | string |
| html_url     | string |
| parents      | array  |
| node_id      | string |
| comments_url | string |
| committer    | object |
| sha          | string |
| author       | object |
+--------------+--------+

to see methods available in a resource use the SHOW METHODS command for example SHOW METHODS IN github.repos.commits would return something like... 

+-------------------------------------------+-------------------------+
|                MethodName                 |     RequiredParams      |
+-------------------------------------------+-------------------------+
| compare_commits                           | basehead, owner, repo   |
| get_commit                                | owner, ref, repo        |
| list_branches_for_head_commit             | commit_sha, owner, repo |
| list_commits                              | owner, repo             |
| list_pull_requests_associated_with_commit | commit_sha, owner, repo |
+-------------------------------------------+-------------------------+
tip

Methods beginning with list or get can usually be accessed via SELECT statements. For example, 

SELECT github.repos.commits.sha 
FROM github.repos.commits 
WHERE owner='${owner}' AND repo='${repo}';

Other methods can be accessed using the EXEC command (for more information see EXEC)

Query protected resources​

Accessing protected resources requires authentication using a Personal Access token as shown here: 

export GITHUB_CREDS=$(echo -n 'yourgithubusername:ghp_YOURPERSONALACCESSTOKEN' | base64)
AUTH_STR='{ "github": { "type": "basic", "credentialsenvvar": "GITHUB_CREDS" } }'
stackql shell --auth="${AUTH_STR}"

Now you are able to access protected resources, for example: 

select id, name, private 
from github.repos_orgs.repos_orgs 
where org = 'stackql';

which would return something like... 

+-----------+-------------------------+---------+
|    id     |          name           | private |
+-----------+-------------------------+---------+
| 443987542 | stackql                 | false   |
| 441087132 | stackqlproviderregistry | false   |
| 409393414 | fullstackchronicles.io  | false   |
| 435085734 | stackql.io              | true    |
| 443979486 | releases.stackql.io     | true    |
| 447890554 | stackqldevel            | true    |
|       ... | ...                     | ...     |
+-----------+-------------------------+---------+

Welcome your feedback by getting in touch or raising issues at stackql/stackql-provider-registry, ⭐️ us while you are there!

· 2 min read

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: 

INSERT INTO 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: 

DELETE FROM 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.

· 3 min read

Queries (particularly) repetitive queries that don't take advantage of results caching can lead to extraordinarily high bills.

StackQL, with it's backend SQL engine, allows you to query Big Query statistics in real time, including identifying queries which are not served from cache and understanding billable charges per query or time slice.

Here is a simple query to break down a time period into hours and show the total queries, queries served from cache and the total query charges per hour. 

SELECT
 STRFTIME('%H', DATETIME(SUBSTR(JSON_EXTRACT(statistics, '$.startTime'), 1, 10), 'unixepoch')) as hour,
 COUNT(*) as num_queries,
 SUM(JSON_EXTRACT(statistics, '$.query.cacheHit')) as using_cache,
 SUM(JSON_EXTRACT(statistics, '$.query.totalBytesBilled')*{{ .costPerByte }} ) as queryCost
FROM google.bigquery.jobs
 WHERE projectId = '{{ .projectId }}'
  AND allUsers = 'true'
  AND minCreationTime = '{{ .minCreationTime }}'
  AND maxCreationTime = '{{ .maxCreationTime }}'
  AND state = 'DONE'
  AND JSON_EXTRACT(statistics, '$.query') IS NOT null
GROUP BY STRFTIME('%H', datetime(SUBSTR(JSON_EXTRACT(statistics, '$.startTime'), 1, 10), 'unixepoch'));

Many more examples to come, including using this data to create visualisations in a Jupyter notebook, stay tuned!

· 4 min read

Understanding roles is integral to applying the principal of least privilege to GCP environments.

A quick primer on roles in GCP​

A Role in GCP is a collection of permissions to services and APIs on the platform. Roles are "bound" to principals or members (users, groups and service accounts).

These bindings are referred to as "policies" which are scoped at a particular level - organisation, folder, project, resource.

There are three types of roles - Primitive Roles, Predefined Roles and Custom Roles.

Primitive (or Basic) Roles​

These are legacy roles set at a GCP project level which include Owner, Editor, and Viewer. These are generally considered to be excessive in terms of permissions and their use should be minimised if not avoided altogether.

Predefined Roles​

These are roles with fine grained access to discrete services in GCP. Google has put these together for your convenience. In most cases predefined roles are the preferred mechanism to assign permissions to members.

Custom Roles​

Custom roles can be created with a curated collection of permissions if required, reasons for doing so include:

  • if the permissions in predefined roles are excessive for your security posture
  • if you want to combine permissions across different services, and cannot find a suitable predefined role although it is preferred to assign multiple predefined roles to a given member

Anatomy of an IAM Policy​

An IAM Policy is a collection of bindings of one or more members (user, group or service account) to a role (primitive, predefined or custom). Policies are normally expressed as JSON objects as shown here:

{
  "bindings": [
    {
      "members": [
        "group:project-admins@my-cloud-identity-domain.com"
      ],
      "role": "roles/owner"
    },
    {
      "members": [
        "serviceAccount:provisioner@my-project.iam.gserviceaccount.com",
        "user:javen@avensolutions.com"
      ],
      "role": "roles/resourcemanager.folderViewer"
    }
  ]
}

Groups are Google Groups created in Cloud Identity or Google Workspace (formerly known as G-Suite)

Application of policies is an atomic operation, which will overwrite any existing policy attached to an entity (org, folder, project, resource).

Querying Roles with StackQL​

Predefined and primitive roles are defined in the roles resource in StackQL (google.iam.roles) - which returns the following fields (as returned by DESCRIBE google.iam.roles):

NameDescription
nameName of the role in the format roles/[{service}.]{role}
for predefined or basic roles, or qualified for custom roles,
e.g. organizations/{org_id}/roles/[{service}.]{role}
descriptionAn optional, human-readable description for the role
includedPermissionsAn array of permissions this role grants (only displayed with
VIEW = 'full')
etagOutput only, used internally for consistency
titleAn optional, human-readable title for the role (visible in the
Console)
deletedA read only boolean field showing the current deleted state
of the role
stageThe current launch stage of the role, e.g. ALPHA

Get the name for a role​

Often, you may know the "friendly" title for a role like "Logs Bucket Writer", but you need the actual role name to use in an Iam policy - which is roles/logging.bucketWriter. A simple query to find this using StackQL is shown here: 

SELECT name
FROM google.iam.roles
WHERE title = 'Logs Bucket Writer';
/* RETURNS:
|----------------------------|
|            name            |
|----------------------------|
| roles/logging.bucketWriter |
|----------------------------|
*/

Conversely, if you have the name but want the friendly title you could use: 

SELECT title
FROM google.iam.roles
WHERE name = 'roles/logging.bucketWriter';
/* RETURNS:
|--------------------|
|       title        |
|--------------------|
| Logs Bucket Writer |
|--------------------|
*/

Wildcards can also be used with the LIKE operator, for example to get the name and title for each predefined role in the logging service you could run:

SELECT name, title
FROM google.iam.roles
WHERE name LIKE 'roles/logging.%';

Get the permissions for a role​

To return the includedPermissions you need to add the following WHERE clause:

WHERE view = 'FULL'

An example query to list the permissions for a given role is shown here: 

SELECT includedPermissions
FROM google.iam.roles
WHERE view = 'FULL' AND
name = 'roles/cloudfunctions.viewer';
/* RETURNS
["cloudbuild.builds.get","cloudbuild.builds.list",...]
*/

A more common challenge is that you know a particular permission such as cloudfunctions.functions.get and you want to know which roles contain this permission you could run the following query: 

SELECT name, title
FROM google.iam.roles
WHERE view = 'FULL'
AND includedPermissions LIKE '%cloudfunctions.functions.get%';

Creating custom roles and more...​

In forthcoming articles, we will demonstrate how you can create custom roles using StackQL INSERT operations, as well as how you can construct a simple IAM framework to manage and provision access to resources in GCP, stay tuned!