Log Disconnections Flag Not Enabled for PostgreSQL Instances

ID: gcloud-postgresql-log-disconnections-unenabled

Severity: medium

Author: princechaddha

Tags: cloud,devops,gcp,gcloud,google-cloud-sql,postgresql,gcp-cloud-config

Description

Ensure that the “log_disconnections” database flag is enabled for all your Google Cloud PostgreSQL database instances. When this flag is enabled, PostgreSQL database logs the end of each session. The log output provides information similar to the one generated by the “log_connections” flag, plus the duration of the session. The database flag can be changed at the session start, and it cannot be changed during a session.

YAML Source

id: gcloud-postgresql-log-disconnections-unenabled

info:
  name: Log Disconnections Flag Not Enabled for PostgreSQL Instances
  author: princechaddha
  severity: medium
  description: |
    Ensure that the "log_disconnections" database flag is enabled for all your Google Cloud PostgreSQL database instances. When this flag is enabled, PostgreSQL database logs the end of each session. The log output provides information similar to the one generated by the "log_connections" flag, plus the duration of the session. The database flag can be changed at the session start, and it cannot be changed during a session.
  impact: |
    Not enabling the "log_disconnections" flag may result in missing critical information about session terminations and their durations, which could be valuable for debugging and auditing.
  remediation: |
    Enable the "log_disconnections" flag for all Google Cloud PostgreSQL database instances by updating the database flag in the configuration settings.
  reference:
    - https://cloud.google.com/sql/docs/postgres/flags
  tags: cloud,devops,gcp,gcloud,google-cloud-sql,postgresql,gcp-cloud-config

flow: |
  code(1)
  for(let projectId of iterate(template.projectIds)){
    set("projectId", projectId)
    code(2)
    for(let sqlInstance of iterate(template.sqlInstances)){
      set("sqlInstance", sqlInstance)
      code(3)
    }
  }

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 sql instances list --project $projectId --filter='DATABASE_VERSION:POSTGRES*' --format="json(name)"

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

  - engine:
      - sh
      - bash
    source: |
      gcloud sql instances describe $sqlInstance --project $projectId --format=json | jq '.settings.databaseFlags[]? | select(.name=="log_disconnections") | .value'

    matchers:
      - type: word
        words:
          - 'off'

    extractors:
      - type: dsl
        dsl:
          - '"Log Disconnections flag is not enabled for PostgreSQL instance " + sqlInstance + " in project: " + projectId'
# digest: 4a0a0047304502210092e544f4997aa872b487ff6fb6c9d0ff14cae29d200243d904c00d0244cbb7fb02206f894c057484426029368ad56eb2568a5cc0e5c193ce3ec79721aea628cdbb1f:922c64590222798bb761d5b6d8e72950

Guide to check the vulnerabilities

This template is used to detect vulnerabilities in web applications. It can be used with the Nuclei tool to scan for specific patterns or behaviors.

$ nuclei -u "URL" -t "cloud/gcp/sql/gcloud-postgresql-log-disconnections-unenabled.yaml"

View on Github