# Audit log - PostgreSQL setup

How to create the PostgreSQL audit log database, including TLS connections.

This page shows how to prepare a PostgreSQL server for the [audit log](https://zato.io/docs/admin/audit-log/index.html). PostgreSQL 13 and later is supported.

## Creating the database and the role {#creating-the-database-and-the-role}

As an administrative user, create a login role named `zato_audit_log` with a password, and a `UTF8` database of the same name owned by that role. The audit log creates its table and indexes the first time a server connects.

Now, point the servers at the database:

```bash
export Zato_Audit_Log_DB_Type=postgresql
export Zato_Audit_Log_DB_Host=db.example.com
export Zato_Audit_Log_DB_Port=5432
export Zato_Audit_Log_DB_Username=zato_audit_log
export Zato_Audit_Log_DB_Password=my.secret.password
export Zato_Audit_Log_DB_Name=zato_audit_log
```

## Enabling SSL/TLS on the server {#enabling-ssltls-on-the-server}

Point PostgreSQL at its certificate files in `postgresql.conf`:

```ini
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'ca.crt'
```

The key file must belong to the user PostgreSQL runs as and must not be readable by anyone else:

```bash
chown postgres:postgres /var/lib/postgresql/data/server.key
chmod 600 /var/lib/postgresql/data/server.key
```

To refuse all unencrypted TCP connections, use `hostssl` instead of `host` in `pg_hba.conf`:

```text
# TYPE  DATABASE        USER            ADDRESS     METHOD
hostssl zato_audit_log  zato_audit_log  0.0.0.0/0   scram-sha-256
```

To additionally require a client certificate from the audit log role (mutual TLS):

```text
hostssl zato_audit_log  zato_audit_log  0.0.0.0/0   scram-sha-256  clientcert=verify-ca
```

Reload the PostgreSQL configuration afterwards.

With the server prepared, enable TLS on the client side:

```bash
export Zato_Audit_Log_DB_SSL=on
export Zato_Audit_Log_DB_SSL_CA_File=/path/to/ca.crt
```

And, when `clientcert` is in use:

```bash
export Zato_Audit_Log_DB_SSL_Cert_File=/path/to/client.crt
export Zato_Audit_Log_DB_SSL_Key_File=/path/to/client.key
```

All the client-side variables are described in the [audit log documentation](https://zato.io/docs/admin/audit-log/index.html).

## Learn more {#learn-more}

- [Production checklist](https://zato.io/docs/admin/production-checklist.html) - What to confirm before an environment takes real traffic
- [Audit log](https://zato.io/docs/admin/audit-log/) - Every request, response and error, and what you can do with them afterwards
- [Health checks](https://zato.io/docs/admin/health-checks.html) - Have Zato ping your outgoing connections instead of waiting for traffic to fail
- [Logging](https://zato.io/docs/admin/logging.html) - What is written where, and how to ship it to Splunk, Loki, Elastic or Datadog
- [GitOps with enmasse](https://zato.io/docs/admin/enmasse.html) - Keep the whole environment in git as YAML and import it anywhere