Auditing for PostgreSQL using pgAudit

This page describes database auditing using the pgAudit extension, which helps you configure many of the logs often required to comply with government, financial, and ISO certifications.

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

Overview

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

Using this extension, you can selectively record and track SQL operations performed against a given database instance. The extension provides you with auditing capabilities to monitor and record a select subset of operations.

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

Setting up database auditing in Cloud SQL

The steps for audit logging using the pgAudit extension include:

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

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

Setting 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 adjusting PostgreSQL parameters and configuring an instance. The cloudsql.enable_pgaudit flag enables auditing for a given database instance. You can change the value of the cloudsql.enable_pgaudit flag through the Google Cloud Console or through the gcloud command.

Use the standard instructions for flags to enable the cloudsql.enable_pgaudit flag, setting the value to on. For example, to use the gcloud command, specify the following, substituting your instance name for [INSTANCE_NAME]:

gcloud sql instances patch [INSTANCE_NAME] --database-flags cloudsql.enable_pgaudit=on

The cloudsql.enable_pgaudit flag is listed with the other supported flags and it is specific to Cloud SQL.

Running the command to create the pgAudit extension

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

CREATE EXTENSION pgaudit;

Setting values for the pgaudit.log flag

Use the standard instructions for flags to set values for the pgaudit.log flag.

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

$ gcloud sql instances patch [INSTANCE_NAME] --database-flags \
  cloudsql.enable_pgaudit=on,pgaudit.log=all

Configuring other settings for the database

To configure auditing settings for the database, follow the procedures under the Customizing database audit logging section.

Viewing database audit logs

To view audit logs, enable Data Access audit logs for your project. The generated pgAudit logs for a given instance are sent to Cloud Logging as Data Access audit logs. Users can view the generated pgAudit logs through the Logs Viewer application.

In the Logs Viewer (Classic) application, the pgAudit logs can be viewed by selecting the cloudaudit.googleapis.com/data_access log filter, as shown here:


This image shows how users view the generated pgAudit logs through the Logs Viewer application.

Alternatively, you can use the following query to show all pgAudit logs for a given Cloud SQL project through the Advanced Filter interface.

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 the Data Access audit logs has 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"
      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 being logged. Possible values are READ, WRITE, FUNCTION, ROLE, DDL, MISC, and MISC_SET.
  • auditType. SESSION or OBJECT.
  • command. For example, ALTER TABLE or SELECT.
  • parameter. If the value for pgaudit.log_parameter is set, this field contains the statement parameters as quoted CSV, or <none> if there are no parameters. Otherwise, the field is <not logged>.
  • statement. Statement executed on the backend.
  • statementId. Unique statement ID for this session. Each statement ID represents a backend call. Statement IDs are sequential even if some statements are not logged.
  • substatementId. Sequential ID for each sub-statement within the main statement.

These fields are described in the pgAudit documentation.

Disabling auditing

To disable database auditing, set the value of the cloudsql.enable_pgaudit flag to off. The value can be changed through the Google Cloud Console or through the gcloud command. Use the standard instructions for flags to disable the cloudsql.enable_pgaudit flag.

Additionally, run the DROP EXTENSION command, using a compatible psql client, to remove the extension state:

DROP EXTENSION pgaudit;

Customizing database audit logging in Cloud SQL

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

For additional capabilities of the extension, review the pgAudit documentation.

Requirement for superuser privileges

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

Configuring auditing for all database operations on the instance

To configure auditing for all databases in an instance, pgAudit settings need to be applied at the system-level. The system-level audit parameters can be set only as database flags through the Google Cloud Console or the gcloud command. For example, to turn on auditing for all database operations on an instance, you can use the following gcloud command:

$ gcloud sql instances patch [INSTANCE_NAME] --database-flags \
  cloudsql.enable_pgaudit=on,pgaudit.log=all

Configuring specific operations on all instance databases

For auditing on all instance databases, you can use the Google Cloud Console or the gcloud command. For example, to turn on auditing for only read and write operations on the instance, you can use the following gcloud command. This example uses a list-based syntax for specifying multiple values:

$ gcloud sql instances patch [INSTANCE_NAME] \
  --database-flags ^:^cloudsql.enable_pgaudit=on:pgaudit.log=read,write

The command overwrites the existing database flags.

Configuring auditing for a specific database

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

finance=> ALTER DATABASE finance SET pgaudit.log = 'read,write';

Configuring 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 assigned to the pgaudit.role parameter. Any object or relation that is granted to this role is logged.

For example, to configure auditing for all SELECT queries on the salary relation within the employee database, you can use these commands:

employee=> CREATE ROLE auditor WITH NOLOGIN;
employee=> ALTER DATABASE employee SET pgaudit.role = 'auditor';
employee=> GRANT SELECT ON salary TO auditor;

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

For example, the following command configures audit logging to occur only when the columns income and tax_status are accessed from the salary relation:

employee=> GRANT SELECT(income, tax_status) ON salary TO auditor;

Configuring auditing for a database user

You can turn on auditing for a specific user by setting the pgaudit.log parameter on a per ROLE level.

For example, the following SQL command sets auditing for all database operations executed by the user Alice:

finance=> ALTER ROLE alice SET pgaudit.log = 'all';

Tips for audit management in Cloud SQL

When you customize audit behavior, remember the following:

  • When the database flag cloudsql.enable_pgaudit is turned off, audit logging is immediately stopped. However, the applied pgAudit settings (for example, the pgaudit.log parameter settings) are preserved, unless they are explicitly removed.
  • The database instance is restarted whenever the database flag value for cloudsql.enable_pgaudit is changed.
  • Database users created through explicit CREATE ROLE commands lack the privilege to modify audit settings. Only database users created through the Google Cloud Console and the gcloud command can modify audit settings.