Audit logs

MySQL   |  PostgreSQL   |  SQL Server

Note: A recommended method for auditing in Cloud SQL for PostgreSQL is the pgAudit extension; see Auditing for PostgreSQL using pgAudit. The information on that page includes examples of gcloud commands.

This document describes the audit logs created by Cloud SQL for PostgreSQL as part of Cloud Audit Logs.

Overview

Google Cloud services write audit logs to help you answer the questions, "Who did what, where, and when?" within your Google Cloud resources.

Your Google Cloud projects contain only the audit logs for resources that are directly within the Google Cloud project. Other Google Cloud resources, such as folders, organizations, and billing accounts, contain the audit logs for the entity itself.

For a general overview of Cloud Audit Logs, see Cloud Audit Logs overview. For a deeper understanding of the audit log format, see Understand audit logs.

Available audit logs

The following types of audit logs are available for Cloud SQL for PostgreSQL:

• Admin Activity audit logs

  Includes "admin write" operations that write metadata or configuration information.

  You can't disable Admin Activity audit logs.

• Data Access audit logs

  Includes "admin read" operations that read metadata or configuration information. Also includes "data read" and "data write" operations that read or write user-provided data.

  To receive Data Access audit logs, you must explicitly enable them.

• System Event audit logs

  Identifies automated Google Cloud actions that modify the configuration of resources.

  You can't disable System Event audit logs.

For fuller descriptions of the audit log types, see Types of audit logs.

Audited operations

The following table summarizes which API operations correspond to each audit log type in Cloud SQL for PostgreSQL:

Audit logs category	Cloud SQL for PostgreSQL operations
Admin Activity audit logs	Cloud SQL Admin API operations that modify instance metadata (DATA_WRITE): cloudsql.backupRuns.create cloudsql.backupRuns.delete cloudsql.instances.addServerCa cloudsql.instances.clone cloudsql.instances.connect cloudsql.instances.create cloudsql.instances.createTagBinding cloudsql.instances.delete cloudsql.instances.deleteTagBinding cloudsql.instances.demoteMaster cloudsql.instances.failover cloudsql.instances.promoteReplica cloudsql.instances.resetSslConfig cloudsql.instances.restart cloudsql.instances.restoreBackup cloudsql.instances.rotateServerCa cloudsql.instances.startReplica cloudsql.instances.stopReplica cloudsql.instances.truncateLog cloudsql.instances.update cloudsql.sslCerts.create cloudsql.sslCerts.delete
Data Access audit logs	Cloud SQL Admin API operations that read instance metadata (DATA_READ): cloudsql.backupRuns.get cloudsql.backupRuns.list cloudsql.databases.create cloudsql.databases.delete cloudsql.databases.get cloudsql.databases.list cloudsql.databases.update cloudsql.instances.executeSql cloudsql.instances.export cloudsql.instances.get cloudsql.instances.import cloudsql.instances.list cloudsql.instances.listEffectiveTags cloudsql.instances.listServerCas cloudsql.instances.listTagBindings cloudsql.instances.login cloudsql.sslCerts.get cloudsql.sslCerts.list cloudsql.users.create cloudsql.users.delete cloudsql.users.get cloudsql.users.list cloudsql.users.update
System Event audit logs	Cloud SQL for PostgreSQL system actions performed on the instance: cloudsql.instances.automatedBackup cloudsql.instances.automaticStorageIncrease

Audit log format

Audit log entries include the following objects:

• The log entry itself, which is an object of type LogEntry. Useful fields include the following:

  • The logName contains the resource ID and audit log type.
  • The resource contains the target of the audited operation.
  • The timeStamp contains the time of the audited operation.
  • The protoPayload contains the audited information.

• The audit logging data, which is an AuditLog object held in the protoPayload field of the log entry.

• Optional service-specific audit information, which is a service-specific object. For earlier integrations, this object is held in the serviceData field of the AuditLog object; later integrations use the metadata field.

For other fields in these objects, and how to interpret them, review Understand audit logs.

Proto payload

protoPayload contains the operation data associated with an audited event. Cloud SQL generates protoPayload when the update, automatic storage increase, and automated backup operations complete.

The following tables describe the audit log metadata fields of these operations:

Update: cloudsql.instances.update

Metadata field
intents	A list of intents and their properties' old and new values.
intent	The type of update operation. The value can be UPDATE_TIER, UPDATE_ZONE, INCREASE_STORAGE_SIZE, ENABLE_HIGH_AVAILABILITY, DISABLE_HIGH_AVAILABILITY, UPDATE_FLAGS, or UPDATE_BACKUP_TRANSACTION_LOG_RETENTION_DAY.
property	The intent property that's updated.
oldValue	The value of the property before the update operation starts.
newValue	The value of the property after the update operation completes.

Automatic storage increase: cloudsql.instances.automaticStorageIncrease

Metadata field
storageIncreaseStartTime	The start time of the automatic storage increase operation.
storageIncreaseCompletionTime	The end time of the automatic storage increase operation.
oldStorageSizeGb	The storage size before the automatic storage increase operation starts.
newStorageSizeGb	The storage size after the automatic storage increase operation completes.

Automated backup: cloudsql.instances.automatedBackup

Metadata field
windowStartTime	The start time of the automated backup window.
windowEndTime	The end time of the automated backup window.
backupStartTime	The start time of the automated backup operation.
backupCompletionTime	The end time of the automated backup operation.
windowStatus	The status of a backup created during the backup window.

For more information, see View audit logs for automated backups.

Log name

Cloud Audit Logs log names include resource identifiers indicating the Google Cloud project or other Google Cloud entity that owns the audit logs, and whether the log contains Admin Activity, Data Access, Policy Denied, or System Event audit logging data.

The following are the audit log names, including variables for the resource identifiers:

   projects/PROJECT_ID/logs/cloudaudit.googleapis.com%2Factivity
   projects/PROJECT_ID/logs/cloudaudit.googleapis.com%2Fdata_access
   projects/PROJECT_ID/logs/cloudaudit.googleapis.com%2Fsystem_event
   projects/PROJECT_ID/logs/cloudaudit.googleapis.com%2Fpolicy

   folders/FOLDER_ID/logs/cloudaudit.googleapis.com%2Factivity
   folders/FOLDER_ID/logs/cloudaudit.googleapis.com%2Fdata_access
   folders/FOLDER_ID/logs/cloudaudit.googleapis.com%2Fsystem_event
   folders/FOLDER_ID/logs/cloudaudit.googleapis.com%2Fpolicy

   billingAccounts/BILLING_ACCOUNT_ID/logs/cloudaudit.googleapis.com%2Factivity
   billingAccounts/BILLING_ACCOUNT_ID/logs/cloudaudit.googleapis.com%2Fdata_access
   billingAccounts/BILLING_ACCOUNT_ID/logs/cloudaudit.googleapis.com%2Fsystem_event
   billingAccounts/BILLING_ACCOUNT_ID/logs/cloudaudit.googleapis.com%2Fpolicy

   organizations/ORGANIZATION_ID/logs/cloudaudit.googleapis.com%2Factivity
   organizations/ORGANIZATION_ID/logs/cloudaudit.googleapis.com%2Fdata_access
   organizations/ORGANIZATION_ID/logs/cloudaudit.googleapis.com%2Fsystem_event
   organizations/ORGANIZATION_ID/logs/cloudaudit.googleapis.com%2Fpolicy

Note: The part of the log name following /logs/ must be URL-encoded. The forward-slash character, /, must be written as %2F.

Service name

Cloud SQL for PostgreSQL audit logs use the service name cloudsql.googleapis.com.

For a list of all the Cloud Logging API service names and their corresponding monitored resource type, see Map services to resources.

Resource types

Cloud SQL for PostgreSQL audit logs use the resource type cloudsql_database for all audit logs.

For a list of all the Cloud Logging monitored resource types and descriptive information, see Monitored resource types.

Caller identities

The IP address of the caller is held in the RequestMetadata.caller_ip field of the AuditLog object. Logging might redact certain caller identities and IP addresses.

For information about what information is redacted in audit logs, see Caller identities in audit logs.

Enable audit logging

System Event audit logs are always enabled; you can't disable them.

Admin Activity audit logs are always enabled; you can't disable them.

Data Access audit logs are disabled by default and aren't written unless explicitly enabled (the exception is Data Access audit logs for BigQuery, which can't be disabled).

For information about enabling some or all of your Data Access audit logs, see Enable Data Access audit logs.

Permissions and roles

IAM permissions and roles determine your ability to access audit logs data in Google Cloud resources.

When deciding which Logging-specific permissions and roles apply to your use case, consider the following:

• The Logs Viewer role (roles/logging.viewer) gives you read-only access to Admin Activity, Policy Denied, and System Event audit logs. If you have just this role, you cannot view Data Access audit logs that are in the _Default bucket.

• The Private Logs Viewer role(roles/logging.privateLogViewer) includes the permissions contained in roles/logging.viewer, plus the ability to read Data Access audit logs in the _Default bucket.

  Note that if these private logs are stored in user-defined buckets, then any user who has permissions to read logs in those buckets can read the private logs. For more information about log buckets, see Routing and storage overview.

For more information about the IAM permissions and roles that apply to audit logs data, see Access control with IAM.

View logs

You can query for all audit logs or you can query for logs by their audit log name. The audit log name includes the resource identifier of the Google Cloud project, folder, billing account, or organization for which you want to view audit logging information. Your queries can specify indexed LogEntry fields. For more information about querying your logs, see Build queries in the Logs Explorer

The Logs Explorer lets you view filter individual log entries. If you want to use SQL to analyze groups of log entries, then use the Log Analytics page. For more information, see:

• Query and view logs in Log Analytics.
• Sample queries for security insights.
• Chart query results.

Most audit logs can be viewed in Cloud Logging by using the Google Cloud console, the gcloud CLI, or the Logging API. However, for audit logs related to billing, you can only use the gcloud CLI or the Logging API.

Console

In the Google Cloud console, you can use the Logs Explorer to retrieve your audit log entries for your Google Cloud project, folder, or organization:

Note: You can't view audit logs for Cloud Billing accounts in the Google Cloud console. You must use the API or the gcloud CLI.

1. In the Google Cloud console, go to the Logs Explorer page:

   Go to Logs Explorer

   If you use the search bar to find this page, then select the result whose subheading is Logging.

2. Select an existing Google Cloud project, folder, or organization.

3. To display all audit logs, enter either of the following queries into the query-editor field, and then click Run query:

   logName:"cloudaudit.googleapis.com"
   

   protoPayload."@type"="type.googleapis.com/google.cloud.audit.AuditLog"
   

4. To display the audit logs for a specific resource and audit log type, in the Query builder pane, do the following:

   • In Resource type, select the Google Cloud resource whose audit logs you want to see.

   • In Log name, select the audit log type that you want to see:

     • For Admin Activity audit logs, select activity.
     • For Data Access audit logs, select data_access.
     • For System Event audit logs, select system_event.
     • For Policy Denied audit logs, select policy.

   • Click Run query.

   If you don't see these options, then there aren't any audit logs of that type available in the Google Cloud project, folder, or organization.

   If you're experiencing issues when trying to view logs in the Logs Explorer, see the troubleshooting information.

   For more information about querying by using the Logs Explorer, see Build queries in the Logs Explorer.

gcloud

The gcloud CLI provides a command-line interface to the Logging API. Supply a valid resource identifier in each of the log names. For example, if your query includes a PROJECT_ID, then the project identifier you supply must refer to the currently selected Google Cloud project.

To read your Google Cloud project-level audit log entries, run the following command:

gcloud logging read "logName : projects/PROJECT_ID/logs/cloudaudit.googleapis.com" \
    --project=PROJECT_ID

To read your folder-level audit log entries, run the following command:

gcloud logging read "logName : folders/FOLDER_ID/logs/cloudaudit.googleapis.com" \
    --folder=FOLDER_ID

To read your organization-level audit log entries, run the following command:

gcloud logging read "logName : organizations/ORGANIZATION_ID/logs/cloudaudit.googleapis.com" \
    --organization=ORGANIZATION_ID

To read your Cloud Billing account-level audit log entries, run the following command:

gcloud logging read "logName : billingAccounts/BILLING_ACCOUNT_ID/logs/cloudaudit.googleapis.com" \
    --billing-account=BILLING_ACCOUNT_ID

Add the --freshness flag to your command to read logs that are more than 1 day old.

For more information about using the gcloud CLI, see gcloud logging read.

REST

When building your queries, supply a valid resource identifier in each of the log names. For example, if your query includes a PROJECT_ID, then the project identifier you supply must refer to the currently selected Google Cloud project.

For example, to use the Logging API to view your project-level audit log entries, do the following:

1. Go to the Try this API section in the documentation for the entries.list method.

2. Put the following into the Request body part of the Try this API form. Clicking this prepopulated form automatically fills the request body, but you need to supply a valid PROJECT_ID in each of the log names.

   {
     "resourceNames": [
       "projects/PROJECT_ID"
     ],
     "pageSize": 5,
     "filter": "logName : projects/PROJECT_ID/logs/cloudaudit.googleapis.com"
   }
   

3. Click Execute.

Route audit logs

You can route audit logs to supported destinations in the same way that you can route other kinds of logs. Here are some reasons you might want to route your audit logs:

• To keep audit logs for a longer period of time or to use more powerful search capabilities, you can route copies of your audit logs to Cloud Storage, BigQuery, or Pub/Sub. Using Pub/Sub, you can route to other applications, other repositories, and to third parties.

• To manage your audit logs across an entire organization, you can create aggregated sinks that can route logs from any or all Google Cloud projects in the organization.

• If your enabled Data Access audit logs are pushing your Google Cloud projects over your log allotments, you can create sinks that exclude the Data Access audit logs from Logging.

For instructions about routing logs, see Route logs to supported destinations.

Pricing

For more information about pricing, see Cloud Logging pricing summary.

Note: Sometimes multiple audit logs are emitted for a single operation. For example, instance creation emits three audit logs. However, in such cases, only one of the emitted logs contains an operation and you can filter for that operation.