This page describes database auditing using thepgAudit extension, which helps you configure many ofthe logs often required to comply with government, financial, and ISOcertifications.

For general information about PostgreSQL extensions in Cloud SQL, seePostgreSQL extensions.

Overview

Database auditing in Cloud SQL for PostgreSQL is available through theopen-source pgAudit extension.

Using this extension, you can selectively record and track SQLoperations performed against a given database instance. The extension providesyou with auditing capabilities to monitor and record a select subset ofoperations.

The pgAudit extension applies to executed SQL commands and queries. In contrast,Cloud Audit Logs should be usedto audit administrative and maintenance operations done on a Cloud SQLinstance.

See theAudit logs pagefor more information about audit logging in Cloud SQL.

Set up database auditing in Cloud SQL

The steps for audit logging using the pgAudit extension include:

1. Enabling thecloudsql.enable_pgaudit flag in Cloud SQL.
2. Running a command to create the pgAudit extension.
3. Setting values for thepgaudit.log flag.

Important:Cloud SQL doesn't support usingTerraform to create the pgAudit extension. Use apsql client to create this extension.

The default value for thepgaudit.log flag isnone.To use the pgAudit extension, you mustsetvalues for this flag.

After you set up database auditing, you can view the logs and, if necessary,disable logging.

Set up auditing

This section describes the basics of setting up database auditing operations.

Initial flag to enable auditing

In Cloud SQL, you use database flags for many operations, including adjustingPostgreSQL parameters and configuring an instance. Thecloudsql.enable_pgaudit flag enables auditing for a givendatabase instance. You can change the value of thecloudsql.enable_pgauditflag through the Google Cloud console or through thegcloud command.

Use thestandard instructions for flags toenable thecloudsql.enable_pgaudit flag, setting the value toon. Forexample, to use thegcloud command, specify the following, substituting yourinstance name for[INSTANCE_NAME]:

gcloudsqlinstancespatch[INSTANCE_NAME]--database-flagscloudsql.enable_pgaudit=on

Thecloudsql.enable_pgaudit flag is listed with the othersupported flagsand it is specific to Cloud SQL.

Run the command to create the pgAudit extension

After enabling the database flag, run theCREATE EXTENSIONcommand using a compatible psql client. The following command createsthe pgAudit extension for all databases in a Cloud SQL instance:

CREATEEXTENSIONpgaudit;

Set values for thepgaudit.log flag

Use thestandard instructions for flags toset values for thepgaudit.log flag.

For example, to turn on auditing for all database operations on an instance,you can use the followinggcloud command:

gcloudsqlinstancespatch[INSTANCE_NAME]--database-flags \cloudsql.enable_pgaudit=on,pgaudit.log=all

Configure other settings for the database

To configure auditing settings for the database, follow the proceduresunder theCustomizing database audit loggingsection.

View database audit logs

To view audit logs,enableData Access audit logs for your project. The generated pgAudit logs for agiven instance are sent to Cloud Logging asData Access audit logs.Users can view the generated pgAudit logs through theLogs Explorer application.

In theLogs Explorerapplication, the pgAudit logs can be viewed by selecting thecloudaudit.googleapis.com/data_access log filter.

Alternatively, you can use the following query to show all pgAudit logs fora given Cloud SQL project:

resource.type="cloudsql_database"logName="projects/<your-project-name>/logs/cloudaudit.googleapis.com%2Fdata_access"protoPayload.request.@type="type.googleapis.com/google.cloud.sql.audit.v1.PgAuditEntry"

Log format for pgAudit

Each pgAudit log entry in theData Access audit logshas fields representing the information collected for a query.

Here is an example:

{  protoPayload: {    @type: "type.googleapis.com/google.cloud.audit.AuditLog"    methodName: "cloudsql.instances.query"    request: {      @type: "type.googleapis.com/google.cloud.sql.audit.v1.PgAuditEntry"      auditClass: "READ"      auditType: "SESSION"      chunkCount: "1"      chunkIndex: "1"      command: "SELECT"      database: "finance"      databaseSessionId: 2209692      parameter: "[not logged]"      statement: "SELECT * FROM revenue"      statementId: 2      substatementId: 1      user: "alice"    }  }}

The following are descriptions of the fields in the Data Access audit logs:

• auditClass. The type of the statement that is logged. Possiblevalues areREAD,WRITE,FUNCTION,ROLE,DDL,MISC,andMISC_SET.
• auditType.SESSION orOBJECT.
• chunkCount. Chunking can occur on the data provided in theparameter andstatement fields.ThechunkCount field indicates the total numberof chunks. Also see the description of thechunkIndex field.
• chunkIndex. Specifies the index number of the data chunks intheparameter andstatement fields (in the currentrequest container). The initial number is1.Also see the description of thechunkCount field.
• command. For example,ALTER TABLE orSELECT.
• parameter. ThechunkIndex field can determine the contentsof this field; see the description of thechunkIndex field.If the value forpgaudit.log_parameter is set, theparameterfield can contain the statement parameters as quoted CSV data.If there are no parameters, this field contains[none].Otherwise, this field contains[not logged].
• statement. Statement that was executed on the backend.ThechunkIndex field can determine the contentsof thestatement field; see the description of thechunkIndex field.
• statementId. Unique statement ID for this session. Each statement IDrepresents a backend call. Statement IDs are sequential, even if somestatements are not logged.
• substatementId. Sequential ID for each sub-statement within the mainstatement.

Some of these fields also are described in thepgAudit documentation.

Disable auditing

To disable database auditing, set the value of thecloudsql.enable_pgauditflag tooff. The value can be changed through the Google Cloud console or throughthegcloud command. Use thestandard instructions for flags todisable thecloudsql.enable_pgaudit flag.

Additionally, run theDROP EXTENSION command, using acompatible psql client, to remove the extension state:

DROPEXTENSIONpgaudit;

Customize database audit logging in Cloud SQL

This section describes ways to customize the auditing behavior of adatabase instance.

For additional capabilities of the extension, review thepgAudit documentation.

Requirement for superuser privileges

In Cloud SQL, extensions can only be created by users that are part of thecloudsqlsuperuser role. When you create a new PostgreSQL instance, the defaultPostgreSQL user is created for you (although you must set the user's password).The default PostgreSQL user is part of thecloudsqlsuperuser role. For moreinformation, seePostgreSQL users.

Configure auditing for all database operations on the instance

To configure auditing for all databases in an instance, you must apply pgAuditsettings at the system level. The system-level audit parameters can be setonly as database flags through the Google Cloud console or thegcloud command.For example, to turnon auditing for all database operations on an instance, you can use thefollowinggcloud command:

gcloudsqlinstancespatch[INSTANCE_NAME]--database-flags \cloudsql.enable_pgaudit=on,pgaudit.log=all

Configure specific operations on all instance databases

For auditing on all instance databases, you can use theGoogle Cloud console or thegcloud command. For example,to turn on auditing for only read and write operations on the instance, you canuse the followinggcloud command. This example uses alist-based syntax for specifyingmultiple values:

gcloudsqlinstancespatch[INSTANCE_NAME]\--database-flags ^:^cloudsql.enable_pgaudit=on:pgaudit.log=read,write

The command overwrites the existing database flags.

Configure auditing for a specific database

To configure auditing for a specific database, set the pgAudit parameters at thedatabase-level. For example, the following SQL command can be used to turn onread/write auditing for a database namedfinance:

finance=>ALTERDATABASEfinanceSETpgaudit.log='read,write';

Configure auditing for a relation

Auditing for a relation is narrower than auditing for a specific database.

When you audit for a relation, a unique auditor role is assignedto thepgaudit.role parameter. Any object or relation that is granted to thisrole is logged.

For example, to configure auditing for allSELECT queries on thesalary relation within theemployee database, you can use these commands:

employee=>CREATEROLEauditorWITHNOLOGIN;employee=>ALTERDATABASEemployeeSETpgaudit.role='auditor';employee=>GRANTSELECTONsalaryTOauditor;

You also can audit a subset of columns for a given relation.

For example, the following command configures audit logging to occur onlywhen the columnsincome andtax_status are accessed from thesalary relation:

employee=>GRANTSELECT(income,tax_status)ONsalaryTOauditor;

Configure auditing for a database user

You can turn on auditing for a specific user by setting thepgaudit.log parameter on a perROLE level.

For example, the following SQL command sets auditing for all databaseoperations executed by the userAlice:

finance=>ALTERROLEaliceSETpgaudit.log='all';

Tips for audit management in Cloud SQL

When you customize audit behavior, remember the following:

• When the database flagcloudsql.enable_pgaudit is turned off, auditlogging is immediately stopped. However, the applied pgAudit settings (forexample, thepgaudit.log parameter settings) are preserved, unlessthey are explicitly removed.
• The database instance is restarted whenever the database flag value forcloudsql.enable_pgaudit is changed.
• Database users created through explicitCREATE ROLE commandslack the privilege to modify audit settings. Only databaseusers created through the Google Cloud console and thegcloud command canmodify audit settings.
• When you enable both session audit logging and object audit logging,statements pertaining to both are added to logs. Session logging and objectlogging don't cancel or modify each other.

Limitations of the pgAudit extension in Cloud SQL for PostgreSQL

Audit logs are temporarily written to the disk of their instance, taking up diskspace before the logs are sent to Cloud Logging. Therefore,review all of the following information before using this feature:

• The log ingestion rate is 4 MB per second. When the load from log generationexceeds the ingestion rate, the following can occur:
  • Unwanted growth in disk usage can occur.
  • Disk space can be exhausted.
• If you have enabled this feature and you run many queries that meet your auditcriteria, disk usage can grow too fast.
• Before using this feature, plan to:
  • Enable automatic storage increases.
  • Monitor the overall disk usage; the load from log generation cannotseparately be monitored. Use thecloudsql.googleapis.com/database/disk/utilizationmetric in theMetrics explorer.
  • If necessary, reduce disk usage by running fewer queries or reducingauditing.
• If the available disk space is exhausted, audit logs for some queries may belost.