Enable pgAudit

To enable auditing on an AlloyDB for PostgreSQL instance, you perform two steps. First, you enable the alloydb.enable_pgaudit flag on the instance. Then, you connect to the cluster's primary instance and create the pgaudit extension in the databases. Depending on the scope of statements that you configure pgAudit to log and your workload volume, enabling extensive audit logging can affect query performance.

Note: Only users who are members of the alloydbsuperuser role can create extensions. The postgres user role that is created when you create a new cluster is a member of the alloydbsuperuser role.

1. Enable pgAudit on the instance:

   Console

   1. In the Google Cloud console, go to the Clusters page.

      Go to Clusters

   2. Click a cluster in the Resource Name column.
   3. In the Overview page, go to Instances in your cluster, select an instance, and then click Edit.
   4. Add the alloydb.enable_pgaudit flag on your instance:
      1. Click Add flag.
      2. Select the alloydb.enable_pgaudit flag from the New database flag list.
      3. Select on from the Value list.
      4. Click Done.
   5. Click Update instance.

   gcloud

   Enable pgAudit on an instance by setting that instance's alloydb.enable_pgaudit flag to on. For more information on setting an instance's database flags using the Google Cloud CLI, see Configure an instance's database flags.

   Note that AlloyDB automatically restarts the instance after you update this flag.

Note: The default value for the pgaudit.log flag is none. To use the pgAudit extension, you must set values for this flag.
2. Connect to the primary instance and create the extension in each database. You must perform the following steps on the primary instance even if you are enabling auditing on a read pool instance:
   1. Connect a psql client to the cluster's primary instance, as described in Connect a psql client to an instance.
   2. At the psql command prompt, connect to the database and create the extension:

          \c DB_NAME
          CREATE EXTENSION IF NOT EXISTS pgaudit;
          

      Replace DB_NAME with the name of the database.

   3. Repeat the previous two steps to connect to other databases and create the extension in each one of them.