XEIZE
/Vulnerability documentation/IaC/Ansible/GCP/Review PostgreSQL temporary-file logging coverage

Review PostgreSQL temporary-file logging coverage

Choose a PostgreSQL temporary-file logging threshold that fits operational needs, then check log volume and access controls.

Description

In Cloud SQL for PostgreSQL, log_temp_files determines the minimum size of temporary files to log. -1 disables this logging, 0 logs all temporary files, and a positive value specifies the minimum size in KB. A value of 1 therefore logs temporary files of at least 1 KB; it does not disable logging.

The log records a temporary file's name and size when the file is deleted. This helps investigate inefficient queries and increased disk use. It does not record the file's contents.

Potential impact

  • Disabled logging or an excessively high threshold can leave insufficient evidence for performance investigations.
  • Logging every temporary file can increase log volume and processing costs, depending on the workload.

Remediation

  • Set log_temp_files to 0 in settings.database_flags when all temporary files must be logged. Choose a suitable positive threshold if only larger files are relevant.
  • Review this threshold alongside other PostgreSQL logging settings and operational requirements.
  • Check actual logs and performance after the change, and manage 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

Use a supported PostgreSQL version and instance tier, actual project and resource names, and a service account JSON file. These excerpts compare logging thresholds.

Before

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

log_temp_files: 1 logs temporary files of at least 1 KB. Whether this is appropriate depends on the need to record smaller files.

After

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

log_temp_files: 0 logs all temporary files. Check the resulting log volume as coverage increases.

References

XEIZE DocumentationTechnical support