# Enable audit logging

> **Only available for postgreSQL in GCP**
> The cli used for this configuration can only detect sql instances in GCP.

This guide describes how to enable audit logging in your postgreSQL database.

The following steps need to be taken to enable the logging.

1. [Configure database flags](#1-configure-database-flags-for-your-sql-instance), and configure replication etc.
2. [Configure database internals](#2-use-the-nais-cli-to-configure-database-internals) to be the primary.

> **Replacing an instance or database**
> After replacing an instance or database, verify its audit configuration. Reapply the settings and create the pgaudit extension if needed.

The pgaudit logs will require disk space, monitor disk usage and [enable automatic storage increase](https://doc.nais.io/workloads/application/reference/application-spec/#gcpsqlinstancesdiskautoresize) if necessary.

For more information on audit logging, see the [official documentation](https://cloud.google.com/sql/docs/postgres/pg-audit).

## 1. Configure database flags for your sql instance

> **Changing audit settings**
> Changing the audit flags cloudsql.enable_pgaudit, pgaudit.log or pgaudit.log_parameter after first-time setup, will restart the postgreSQL instance.

The following database flags must be set before we can enable audit logging.

```text
cloudsql.enable_pgaudit
pgaudit.log
```

The flags can be set in the application spec (these parameters are the recommended settings):

```yaml
spec:
  gcp:
    sqlInstances:
    - name: myapp
      flags:
        - name: "cloudsql.enable_pgaudit"
          value: "on"
        - name: "pgaudit.log"
          value: "write,ddl,role"
        - name: "pgaudit.log_parameter"
          value: "on"
        - name: "pgaudit.log_relation"
          value: "on"
```

The `cloudsql.enable_pgaudit` flag enables the pgaudit logging in the database. The `pgaudit.log` flag specifies what to log. 
Possible values for `pgaudit.log` are as follows (and all combinations of these):

- `read` - Log select and copy commands.
- `write` - Log insert, update, delete, truncate and copy commands.
- `ddl` - Log all data definition language commands not included in `role`.
- `role` - Log role and permission changes (grant, revoke, create/alter/drop role).
- `misc` - Log miscellaneous commands (discard, fetch, checkpoint, vacuum, set).
- `misc_set` - Log miscellaneous set commands.
- `function` - Log function calls and do-blocks operations.
- `all` - Log all commands.

Other flags that are available for pgaudit are documented [here](https://github.com/pgaudit/pgaudit/blob/main/README.md#settings).

## 2. Use the nais cli to configure database internals

> **This command requires personal access to the database (e.g. Cloud SQL IAM User)**
> Verify that the user running this command has the correct permissions in IAM, 
> to allow cloud-sql-proxy connection to the postgreSQL instance.

The nais cli can be used to configure the database internals. This will fail if the necessary database flags have not been configured.
The cli will log on to your database and create the pgaudit extension and also disable logging for the application user.

```shell
$ nais cloudsql enable-audit --team <team> --environment <environment> <application>
```

The application, team, and environment flags are all required.

For more information on the nais cli, see the [official documentation](https://doc.nais.io/cli).