We have released the latest StackQL provider for Google, which includes:

• 14 new services (including alloydb, apphub, biglake, bigquerydatapolicy, looker and more)
• 231 new resources
• 1,185 new methods

More information is available here. Run the following to install or update the Google provider:

-- run from stackql shell
REGSITRY PULL google;

or

# from the command line
stackql registry pull google

Give us your feedback! ⭐ us here!

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.

I've broken the notebook bits down to explain...

Setup​

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.

Get all nodes in the resource hierarchy​

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.

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

Enjoy!

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'));

// variables

local projectId = 'my-project-id';
local costPerTb = 6.5;
local startTimeMs = 1634734801000;

{
	projectId: projectId,
	minCreationTime: std.toString(startTimeMs),
	maxCreationTime: std.toString(startTimeMs+86400000),
	costPerByte: costPerTb*(1/std.pow(1024, 4)),
}

|------|-------------|-------------|------------------------|
| hour | num_queries | using_cache |       queryCost        |
|------|-------------|-------------|------------------------|
|   00 |         182 |           0 |      70.73793411254883 |
|------|-------------|-------------|------------------------|
|   01 |          88 |           0 |      34.20295715332031 |
|------|-------------|-------------|------------------------|
|   02 |           2 |           0 |     0.7773399353027344 |
|------|-------------|-------------|------------------------|
|   03 |         267 |           0 |     103.77488136291504 |
|------|-------------|-------------|------------------------|
|   04 |         216 |           0 |      83.95271301269531 |
|------|-------------|-------------|------------------------|
|   05 |          47 |           0 |     18.267488479614258 |
|------|-------------|-------------|------------------------|
|   06 |         122 |           0 |       47.4177360534668 |
|------|-------------|-------------|------------------------|
|   07 |         195 |           0 |       75.7906436920166 |
|------|-------------|-------------|------------------------|
|   08 |         186 |           0 |       72.2926139831543 |
|------|-------------|-------------|------------------------|
|   09 |          75 |           0 |      29.15024757385254 |
|------|-------------|-------------|------------------------|
|   10 |          62 |           0 |     24.097537994384766 |
|------|-------------|-------------|------------------------|
|   11 |          56 |           0 |     21.765518188476562 |
|------|-------------|-------------|------------------------|
|   12 |          89 |           0 |      34.59162712097168 |
|------|-------------|-------------|------------------------|
|   15 |           3 |           0 | 0.00018596649169921875 |
|------|-------------|-------------|------------------------|
|   22 |           1 |           0 |     0.3886699676513672 |
|------|-------------|-------------|------------------------|
|   23 |          35 |           0 |     13.603448867797852 |
|------|-------------|-------------|------------------------|

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

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):

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!

I grappled with Terraform for the better part of a day trying to provision a GKE Autopilot cluster in a Shared VPC service project, I was able to do this with StackQL in 2 minutes, this is how...

Before starting you will need the following to use GKE Autopilot in your Shared VPC:

• control plane IP address range
• control plane authorized networks (if desired)
• the host network and node subnet you intend to use
• pod and services secondary CIDR ranges

(all of the above would typically be pre-provisioned in the Shared VPC design and deployment)

Step 1: Using the GCP console, navigate to your service project, go to Kubernetes Engine --> Clusters --> Create --> GKE Autopilot --> Configure. Enter in all of the desired configuration options (including the network configuration specified above). Do not select CREATE.

Step 2: At the bottom of the dialog used to configure the cluster in the console, use the Equivalent REST button to generate the GKE Autopilot API request body.

Step 3: Supply this as input data to an StackQL INSERT command, either via an iql file, on as inline configuration. Optionally you can convert this to Jsonnet and parameterise for use in other environments.

<<<json
{
  "cluster": {
	..from equivalent REST command..
  }
}
>>>

INSERT INTO google.container.`projects.locations.clusters`(
  parent,
  data__cluster
)
SELECT 'projects/my-svc-project/locations/australia-southeast1',
  '{{ .cluster }}'
;

easy!