Skip to main content

14 posts tagged with "gcp"

View All Tags

· One min read

The latest google provider for stackql is available now, and includes a new oracledatabase service, including resources for cloud_vm_clusters, db_nodes, db_servers, cloud_exadata_infrastructures, entitlements, and more.

Summary stats for the new google provider:

Versionv24.09.00254
Total services168
Total resources1941


Let us know what you think! ⭐ us on GitHub.

· 2 min read

Analyzing firewall rules is crucial for maintaining security in your cloud infrastructure. Using StackQL, you can efficiently query and analyze Google Cloud firewall configurations to ensure that your security policies are correctly implemented and that there are no unexpected open ports or protocols that might pose a security risk. Below is a simple query that retrieves important details about the ingress firewall rules for a specific network in a Google Cloud project.

SELECT 
    name, 
    source_range,
    ip_protocol,
    allowed_ports,
    direction
FROM (
    SELECT 
    name,
    source_ranges.value as source_range,  
    JSON_EXTRACT(allowed.value, '$.IPProtocol') as ip_protocol,
    JSON_EXTRACT(allowed.value, '$.ports') as allowed_ports,
    direction
    FROM google.compute.firewalls, json_each(sourceRanges) as source_ranges, json_each(allowed) as allowed
    WHERE project = 'stackql-k8s-the-hard-way-demo' 
    AND network = 'https://www.googleapis.com/compute/v1/projects/stackql-k8s-the-hard-way-demo/global/networks/kubernetes-the-hard-way-dev-vpc'
) t 
WHERE 
source_range = '0.0.0.0/0' 
and direction = 'INGRESS';

This query provides a comprehensive list of all ingress firewall rules that apply to any IP address (0.0.0.0/0) within the specified Google Cloud project and network. The results include the firewall rule name, the source IP range, the protocol, the allowed ports, and the direction of the traffic, an example is shown below:

|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
|                     name                      | source_range | ip_protocol | allowed_ports | direction |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
| default-allow-icmp                            | 0.0.0.0/0    | icmp        | null          | INGRESS   |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
| default-allow-rdp                             | 0.0.0.0/0    | tcp         | ["3389"]      | INGRESS   |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
| default-allow-ssh                             | 0.0.0.0/0    | tcp         | ["22"]        | INGRESS   |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
| kubernetes-the-hard-way-dev-allow-external-fw | 0.0.0.0/0    | tcp         | ["22"]        | INGRESS   |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
| kubernetes-the-hard-way-dev-allow-external-fw | 0.0.0.0/0    | tcp         | ["6443"]      | INGRESS   |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
| kubernetes-the-hard-way-dev-allow-external-fw | 0.0.0.0/0    | icmp        | null          | INGRESS   |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|

You can use this query to help quickly identify potential security vulnerabilities. Regularly auditing these rules ensures that your cloud environment remains secure and that only the necessary ports and protocols are open to the internet.

Give us your feedback! ⭐ us here!

· One min read

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!

· 3 min read

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:

GCP Nodes

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:

GCP Nodes with IAM Policies

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!

· 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!