> ## Documentation Index
> Fetch the complete documentation index at: https://cloudanix.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Sql configuration logging remediation

### Triage and Remediation

<Tabs>
  <Tab title="Remediation">
    ### Remediation

    <AccordionGroup>
      <Accordion title="Using Console" defaultOpen="true">
        To remediate “SQL configuration logging” for GCP Cloud SQL via the GCP Console, you want to ensure that Cloud Audit Logs are enabled for Cloud SQL so that configuration and metadata changes (creates, updates, deletes, etc.) are captured.

        Follow these steps:

        1. **Open Audit Logs settings**
           * In the Google Cloud Console, go to:\
             `IAM & Admin` → `Audit Logs`.

        2. **Select the correct project**
           * At the top project selector, make sure you’re on the project where your Cloud SQL instances reside.

        3. **Choose the Cloud SQL service**
           * On the left side, under “Audit Logs Configuration,” find and select:\
             **Cloud SQL Admin API** (`sqladmin.googleapis.com`).

        4. **Enable the log types**
           * On the right pane, you’ll see checkboxes for:
             * **Admin Read**
             * **Data Read**
             * **Data Write**
           * At minimum for configuration logging, ensure **Admin Read** is enabled.
           * If your policy requires full tracking (often recommended), also enable **Data Read** and **Data Write**.

        5. **Apply the settings**
           * Click **Save** at the bottom of the page.

        6. **Verify logs are being produced**
           * Go to `Logging` → `Logs Explorer`.
           * Set:
             * **Resource type**: `Cloud SQL Database`
             * Or filter by:
               ```text theme={null}
               resource.type="cloudsql_database"
               logName="projects/PROJECT_ID/logs/cloudaudit.googleapis.com%2Factivity"
               ```
           * Perform a configuration change on a test Cloud SQL instance (e.g., edit flags or settings) and confirm you see corresponding audit log entries.

        Once these audit logs are enabled and verified, configuration changes for Cloud SQL (instance create/update/delete, flags changes, etc.) are logged and can be monitored or exported as required.
      </Accordion>

      <Accordion title="Using CLI">
        Below is how to enable/adjust SQL configuration logging on **Cloud SQL for GCP using gcloud**. I’ll cover MySQL and PostgreSQL, which are the usual targets for this control.

        ***

        ## 1. Identify your instance

        ```bash theme={null}
        INSTANCE_ID="your-instance-id"
        PROJECT_ID="your-project-id"

        gcloud config set project "$PROJECT_ID"
        gcloud sql instances describe "$INSTANCE_ID"
        ```

        Check the `databaseVersion` in the output to know whether it’s MySQL or PostgreSQL.

        ***

        ## 2. MySQL: Enable connection / general / slow / error logging

        Typical CIS-style hardening wants:

        * `log_output = FILE`
        * `general_log = ON`
        * `log_connections = ON` (via `log_warnings` or default)
        * `slow_query_log = ON`
        * Reasonable `long_query_time`

        ### 2.1 See current flags

        ```bash theme={null}
        gcloud sql instances describe "$INSTANCE_ID" \
          --format="flattened(settings.databaseFlags)"
        ```

        ### 2.2 Patch flags

        Replace or extend `--database-flags` as needed:

        ```bash theme={null}
        gcloud sql instances patch "$INSTANCE_ID" \
          --database-flags \
            log_output=FILE,\
            general_log=on,\
            slow_query_log=on,\
            long_query_time=1
        ```

        Notes:

        * If you already have flags set, you must **re-specify them all**; this command replaces the full flag list.
        * Adjust `long_query_time` (in seconds) to your policy.

        Instance will restart if required; confirm prompt.

        ***

        ## 3. PostgreSQL: Enable connection / error / statement / slow logging

        Typical settings:

        * `log_connections = on`
        * `log_disconnections = on`
        * `log_statement = all` (or at least `mod`)
        * `log_min_duration_statement = 1000` (1s; adjust per policy)

        ### 3.1 See current flags

        ```bash theme={null}
        gcloud sql instances describe "$INSTANCE_ID" \
          --format="flattened(settings.databaseFlags)"
        ```

        ### 3.2 Patch flags

        ```bash theme={null}
        gcloud sql instances patch "$INSTANCE_ID" \
          --database-flags \
            log_connections=on,\
            log_disconnections=on,\
            log_statement=all,\
            log_min_duration_statement=1000
        ```

        Again, include any other existing flags you need to preserve.

        ***

        ## 4. Verify after change

        ```bash theme={null}
        gcloud sql instances describe "$INSTANCE_ID" \
          --format="flattened(settings.databaseFlags)"
        ```

        Confirm each flag is set as intended.

        ***

        ## 5. Ensure logs reach Cloud Logging

        Cloud SQL writes logs to Cloud Logging by default when logging flags are on. Verify:

        ```bash theme={null}
        gcloud logging logs list --project="$PROJECT_ID" \
          --filter="logName:projects/$PROJECT_ID/logs/cloudsql.googleapis.com"
        ```

        You should see logs like `cloudsql.googleapis.com/mysql.err`, `cloudsql.googleapis.com/postgres.log`, etc.

        ***

        If you tell me whether your instance is MySQL or PostgreSQL (and its version), I can give you an exact `gcloud sql instances patch` command you can paste and run.
      </Accordion>

      <Accordion title="Using Python">
        Below is how to remediate “SQL Configuration Logging” for Cloud SQL in GCP using Python, by **enabling audit logging for Cloud SQL at the project level** (so configuration changes on Cloud SQL instances are logged).

        In GCP, Cloud SQL configuration changes are captured by **Cloud Audit Logs**.\
        You need to ensure **Data Access audit logs** are enabled for the `cloudsql.googleapis.com` service on your project.

        ***

        ## 1. Prerequisites

        1. **Enable APIs**
           * Cloud Resource Manager API
           * Cloud SQL Admin API
           * Cloud Logging API

        2. **Service account / identity**
           The identity running the script must have:
           * `roles/resourcemanager.projectIamAdmin` (or a custom role that can `get` and `setIamPolicy` on the project)

        3. **Install dependencies**

        ```bash theme={null}
        pip install google-api-python-client google-auth google-auth-httplib2 google-auth-oauthlib
        ```

        And authenticate (e.g. via Application Default Credentials):

        ```bash theme={null}
        gcloud auth application-default login
        ```

        ***

        ## 2. What we’re enabling

        You will:

        * Edit the **project IAM policy** to add an `auditConfigs` entry for `cloudsql.googleapis.com` that enables:
          * `DATA_READ`
          * `DATA_WRITE`
          * (Admin Activity logs are on by default and can’t be disabled, so you don’t need to set that.)

        This ensures Cloud SQL configuration changes and access are logged.

        ***

        ## 3. Python code: enable Cloud SQL audit logging

        Replace `YOUR_PROJECT_ID` with your GCP project ID.

        ```python theme={null}
        from googleapiclient.discovery import build
        from googleapiclient.errors import HttpError
        from google.oauth2 import service_account  # Optional if you use ADC

        PROJECT_ID = "YOUR_PROJECT_ID"

        def enable_cloudsql_audit_logs(project_id: str):
            service = build("cloudresourcemanager", "v1")

            # 1. Get current IAM policy
            policy = (
                service.projects()
                .getIamPolicy(
                    resource=project_id,
                    body={"options": {"requestedPolicyVersion": 3}}
                )
                .execute()
            )

            audit_configs = policy.get("auditConfigs", [])

            service_name = "cloudsql.googleapis.com"
            wanted_log_types = ["DATA_READ", "DATA_WRITE"]

            # 2. Find existing auditConfig for Cloud SQL (if any)
            existing_config = None
            for ac in audit_configs:
                if ac.get("service") == service_name:
                    existing_config = ac
                    break

            if not existing_config:
                existing_config = {
                    "service": service_name,
                    "auditLogConfigs": []
                }
                audit_configs.append(existing_config)

            existing_log_types = {c["logType"] for c in existing_config.get("auditLogConfigs", [])}

            # 3. Add missing logTypes for all principals ("allUsers")
            for log_type in wanted_log_types:
                if log_type not in existing_log_types:
                    existing_config["auditLogConfigs"].append(
                        {
                            "logType": log_type,
                            # Omit exemptedMembers to log for everyone
                        }
                    )

            policy["auditConfigs"] = audit_configs

            # 4. Set updated IAM policy
            try:
                updated_policy = (
                    service.projects()
                    .setIamPolicy(
                        resource=project_id,
                        body={"policy": policy}
                    )
                    .execute()
                )
                print("Updated IAM policy with Cloud SQL audit logging:")
                print(updated_policy.get("auditConfigs", []))
            except HttpError as e:
                print(f"Error updating IAM policy: {e}")
                raise

        if __name__ == "__main__":
            enable_cloudsql_audit_logs(PROJECT_ID)
        ```

        ***

        ## 4. Verify logging

        1. Go to **Cloud Logging → Logs Explorer**.
        2. Use a query like:

        ```text theme={null}
        logName:"cloudaudit.googleapis.com" 
        resource.type="cloudsql_database"
        ```

        3. Make a configuration change to a Cloud SQL instance (e.g., modify settings) and confirm that a new audit log entry appears.

        ***

        If you tell me your Cloud SQL engine (PostgreSQL / MySQL / SQL Server) and what “configuration logging” control you’re mapping to (e.g., CIS benchmark section), I can give a more tailored Python example (including instance-level flags if needed).
      </Accordion>

      <Accordion title="Using Terraform">
        ```hcl theme={null}
        # Enable Cloud Audit Logs for Cloud SQL configuration and access
        # Replace PROJECT_ID with your GCP project ID.

        resource "google_project_iam_audit_config" "cloud_sql_audit_logging" {
          project = "PROJECT_ID" # <-- replace with your GCP project ID
          service = "sqladmin.googleapis.com"

          # Admin Activity logs for config changes are always on,
          # but you must explicitly enable Data Access logs (reads/writes).

          audit_log_config {
            log_type = "DATA_READ"
          }

          audit_log_config {
            log_type = "DATA_WRITE"
          }

          # Optional: restrict logging to specific principals by adding exempted_members
          # audit_log_config {
          #   log_type = "DATA_READ"
          #   exempted_members = [
          #     "user:EXEMPT_USER@example.com",
          #   ]
          # }
        }
        ```

        This does not force replacement of existing Cloud SQL instances; it changes project-level audit logging for the Cloud SQL Admin API.

        For verification, `terraform plan` should show one `google_project_iam_audit_config.cloud_sql_audit_logging` to be created (or updated) with `service = "sqladmin.googleapis.com"` and `audit_log_config` blocks for `DATA_READ` and `DATA_WRITE`.
      </Accordion>
    </AccordionGroup>
  </Tab>
</Tabs>