id: gcloud-unrestricted-postgresql-access

info:
  name: Check for Unrestricted PostgreSQL Database Access
  author: princechaddha
  severity: high
  description: |
    Ensure that Google Cloud VPC network firewall rules do not allow unrestricted access (0.0.0.0/0 on TCP port 5432). Restrict PostgreSQL traffic to trusted IP addresses or IP ranges to implement the Principle of Least Privilege (POLP) and reduce the attack surface.
  impact: |
    Allowing unrestricted access to TCP port 5432 exposes PostgreSQL database servers to potential brute-force or unauthorized access attacks.
  remediation: |
    Update your VPC firewall rules to allow PostgreSQL traffic only from trusted IP addresses or ranges.
  reference:
    - https://cloud.google.com/vpc/docs/firewalls
  tags: cloud,devops,gcp,gcloud,vpc,firewall,security,postgresql,gcp-cloud-config

flow: |
  code(1)
  for(let projectId of iterate(template.projectIds)){
    set("projectId", projectId)
    code(2)
    for(let networkName of iterate(template.networks)){
      set("networkName", networkName)
      code(3)
      set("firewallRules", template.firewallRules)
      javascript(1)
    }
  }

self-contained: true

code:
  - engine:
      - sh
      - bash
    source: |
      gcloud projects list --format="json(projectId)"

    extractors:
      - type: json
        name: projectIds
        internal: true
        json:
          - '.[].projectId'

  - engine:
      - sh
      - bash
    source: |
      gcloud compute networks list --project $projectId --format="json(name)"

    extractors:
      - type: json
        name: networks
        internal: true
        json:
          - '.[].name'

  - engine:
      - sh
      - bash
    source: |
      gcloud compute firewall-rules list --filter network=$networkName --sort-by priority --format="json(name,disabled,direction,sourceRanges,allowed[].ports)"

    extractors:
      - type: json
        name: firewallRules
        internal: true
        json:
          - '.[]'

javascript:
  - code: |
      let firewallRules = template.firewallRules;
      if (typeof firewallRules === "string") {
        firewallRules = JSON.parse(firewallRules);
      }

      let insecureRules = [];
      for (let rule of firewallRules) {
        if (
          rule.disabled === false &&
          rule.direction === "INGRESS" &&
          Array.isArray(rule.sourceRanges) && rule.sourceRanges.includes("0.0.0.0/0") &&
          Array.isArray(rule.allowed) &&
          rule.allowed.some(allowed =>
            Array.isArray(allowed.ports) &&
            allowed.ports.includes("5432")
          )
        ) {
          insecureRules.push(rule.name);
        }
      }

      if (insecureRules.length > 0) {
        Export(`The firewall rules in network ${template.networkName} of project ${template.projectId} allow unrestricted PostgreSQL access: ${insecureRules.join(", ")}`);
      }

    extractors:
      - type: dsl
        dsl:
          - response
# digest: 490a00463044022027d1cae5fa77df064034410ddb21bef1d12598ac300701def4bb3148b8c223530220703d1083bf23862d36e2b59375267adf56744d6e4adde648bb481ca490587b9d:922c64590222798bb761d5b6d8e72950