XEIZE
/Vulnerability documentation/IaC/Ansible/GCP/Review PostgreSQL statement-duration logging thresholds

Review PostgreSQL statement-duration logging thresholds

Choose a PostgreSQL duration-logging threshold that balances diagnostic needs and log volume, and protect logs containing SQL text.

Description

In Cloud SQL for PostgreSQL, log_min_duration_statement logs completed SQL statements according to their execution time. 0 logs every completed statement, a positive value specifies the minimum duration in milliseconds, and -1 disables this feature.

An excessively low threshold increases log volume. A high threshold or disabled logging can remove evidence needed to investigate performance problems. -1 is not a universally appropriate security setting.

Potential impact

  • Excessive logging can increase storage use, analysis costs, and database processing overhead.
  • Insufficient coverage can make slow queries and performance degradation harder to investigate.
  • Logged SQL statements can contain sensitive information, requiring controls on log access and retention.

Remediation

  • Choose a duration threshold for the workload and diagnostic requirements. Consider a suitable positive value to identify slow queries.
  • Explicitly set the intended value in settings.database_flags and review coverage alongside other logging settings.
  • Check actual log volume and performance after the change, and restrict log access and retention.

The SQL module in google.cloud 1.14.0 does not support updates to existing instances. These examples configure creation; change flags on existing instances through supported Cloud SQL tooling or APIs.

Examples

These examples compare logging every SQL statement with disabling this feature. Supply a supported PostgreSQL version and instance tier, actual project and resource names, and service account credentials separately.

Before

yaml
- name: Cloud SQL 인스턴스 생성
  google.cloud.gcp_sql_instance:
    auth_kind: serviceaccount
    name: "{{ resource_name }}-db"
    project: test_project
    region: us-central1
    database_version: POSTGRES_13
    settings:
      tier: db-custom-1-3840
      database_flags:
        - name: log_min_duration_statement
          value: 0
    state: present

log_min_duration_statement: 0 logs the duration and text of every completed SQL statement. Review both diagnostic needs and logging overhead.

After

yaml
- name: Cloud SQL 인스턴스 생성
  google.cloud.gcp_sql_instance:
    auth_kind: serviceaccount
    name: "{{ resource_name }}-db"
    project: test_project
    region: us-central1
    database_version: POSTGRES_13
    settings:
      tier: db-custom-1-3840
      database_flags:
        - name: log_min_duration_statement
          value: -1
    state: present

log_min_duration_statement: -1 disables this duration-based logging. It does not disable all other logging settings, but the diagnostic records provided by this feature are lost. Use a positive threshold instead when needed.

References

XEIZE DocumentationTechnical support