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.
See the Audit logs page for 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:
- Enabling the
cloudsql.enable_pgaudit
flag in Cloud SQL. - Running a command to create the pgAudit extension.
- Setting values for the
pgaudit.log
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 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.
Run 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;
Set 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
Configure other settings for the database
To configure auditing settings for the database, follow the procedures under the Customizing database audit logging section.
View 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 Explorer application.
In the Logs Explorer application, the pgAudit logs can be viewed by selecting the cloudaudit.googleapis.com/data_access log filter.
Alternatively, you can use the following query to show all pgAudit logs for a 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 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" 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. Possible
values are
READ
,WRITE
,FUNCTION
,ROLE
,DDL
,MISC
, andMISC_SET
. - auditType.
SESSION
orOBJECT
. - chunkCount. Chunking can occur on the data provided in the
parameter
andstatement
fields. ThechunkCount
field indicates the total number of chunks. Also see the description of thechunkIndex
field. - chunkIndex. Specifies the index number of the data chunks in
the
parameter
andstatement
fields (in the currentrequest
container). The initial number is1
. Also see the description of thechunkCount
field. - command. For example,
ALTER TABLE
orSELECT
. - parameter. The
chunkIndex
field can determine the contents of this field; see the description of thechunkIndex
field. If the value forpgaudit.log_parameter
is set, theparameter
field 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.
The
chunkIndex
field can determine the contents of thestatement
field; see the description of thechunkIndex
field. - 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.
Some of these fields also are described in the pgAudit documentation.
Disable 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;
Customize 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.
Configure auditing for all database operations on the instance
To configure auditing for all databases in an instance, you must apply pgAudit
settings 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
Configure 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.
Configure 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';
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 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;
Configure 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, thepgaudit.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 thegcloud
command can modify audit settings. - When you enable both session audit logging and object audit logging, statements pertaining to both are added to logs. Session logging and object logging 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 disk space 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 generation
exceeds 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 audit criteria, 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 cannot separately be monitored. Use the cloudsql.googleapis.com/database/disk/utilization metric in the Metrics explorer.
- If necessary, reduce disk usage by running fewer queries or reducing auditing.
- If the available disk space is exhausted, audit logs for some queries may be lost.