Logging

The Supabase Platform includes a Logs Explorer that allows log tracing and debugging. Log retention is based on your project's pricing plan.

Product Logs#

Supabase provides a logging interface specific to each product. You can use simple regular expressions for keywords and patterns to search log event messages. You can also export and download the log events matching your query as a spreadsheet.

Logging Postgres Queries#

By default, query logs are disabled for new Supabase projects, as they can reveal metadata about the contents of your database (such as table and column names).

To enable query logs:

1. Enable the pgAudit extension.
2. Restart your project using the Fast database reboot option.
3. Configure pgaudit.log (see below). Perform a fast reboot if needed.
4. View your query logs under Logs > Postgres Logs.

Configuring pgaudit.log#

The stored value under pgaudit.log determines the classes of statements that are logged by pgAudit extension. Refer to the pgAudit documentation for the full list of values.

To enable logging for function calls/do blocks, writes, and DDL statements for a single session, execute the following within the session:

-- temporary single-session config update
set pgaudit.log = 'function, write, ddl';

To permanently set a logging configuration (beyond a single session), execute the following, then perform a fast reboot:

-- equivalent permanent config update.
alter role postgres set pgaudit.log to 'function, write, ddl';

To reset system-wide settings, execute the following, then perform a fast reboot:

-- resets stored config.
alter role postgres reset pgaudit.log

Logging Realtime Connections#

Realtime doesn't log new WebSocket connections or Channel joins by default. Enable connection logging per client by including an info log_level parameter when instantiating the Supabase client.

import { createClient } from '@supabase/supabase-js'

const options = {
  realtime: {
    log_level: 'info',
  },
}
const supabase = createClient('https://xyzcompany.supabase.co', 'public-anon-key', options)

Logs Explorer#

The Logs Explorer exposes logs from each part of the Supabase stack as a separate table that can be queried and joined using SQL.

You can access the following logs from the Sources drop-down:

• auth_logs: GoTrue server logs, containing authentication/authorization activity.
• edge_logs: Edge network logs, containing request and response metadata retrieved from Cloudflare.
• function_edge_logs: Edge network logs for only edge functions, containing network requests and response metadata for each execution.
• function_logs: Function internal logs, containing any console logging from within the edge function.
• postgres_logs: Postgres database logs, containing statements executed by connected applications.
• realtime_logs: Realtime server logs, containing client connection information.
• storage_logs: Storage server logs, containing object upload and retrieval information.

Querying with the Logs Explorer#

The Logs Explorer uses BigQuery and supports all available SQL functions and operators.

Timestamp Display and Behavior#

Each log entry is stored with a timestamp as a TIMESTAMP data type. Use the appropriate timestamp function to utilize the timestamp field in a query.

Raw top-level timestamp values are rendered as unix microsecond. To render the timestamps in a human-readable format, use the DATETIME() function to convert the unix timestamp display into an ISO-8601 timestamp.

-- timestamp column without datetime()
select timestamp from ....
--  1664270180000

-- timestamp column with datetime()
select datetime(timestamp) from ....
-- 2022-09-27T09:17:10.439Z

Unnesting Arrays#

Each log event stores metadata an array of objects with multiple levels, and can be seen by selecting single log events in the Logs Explorer. To query arrays, use unnest() on each array field and add it to the query as a join. This allows you to reference the nested objects with an alias and select their individual fields.

For example, to query the edge logs without any joins:

select timestamp, metadata from edge_logs as t;

The resulting metadata key is rendered as an array of objects in the Logs Explorer. In the following diagram, each box represents a nested array of objects:

Perform a cross join unnest() to work with the keys nested in the metadata key.

To query for a nested value, add a join for each array level:

select timestamp, request.method, header.cf_ipcountry
from
  edge_logs as t
  cross join unnest(t.metadata) as metadata
  cross join unnest(metadata.request) as request
  cross join unnest(request.headers) as header;

This surfaces the following columns available for selection:

This allows you to select the method and cf_ipcountry columns. In JS dot notation, the full paths for each selected column are:

• metadata[].request[].method
• metadata[].request[].headers[].cf_ipcountry

LIMIT and Result Row Limitations#

The Logs Explorer has a maximum of 1000 rows per run. Use LIMIT to optimize your queries by reducing the number of rows returned further.

Best Practices#

1. Include a filter over timestamp

Querying your entire log history might seem appealing. For Enterprise customers that have a large retention range, you run the risk of timeouts due additional time required to scan the larger dataset.

2. Avoid selecting large nested objects. Select individual values instead.

When querying large objects, the columnar storage engine selects each column associated with each nested key, resulting in a large number of columns being selected. This inadvertently impacts the query speed and may result in timeouts or memory errors, especially for projects with a lot of logs.

Instead, select only the values required.

-- ❌ Avoid doing this
select
  datetime(timestamp),
  m as metadata -- <- metadata contains many nested keys
from
  edge_logs as t
  cross join unnest(t.metadata) as m;

-- ✅ Do this
select
  datetime(timestamp),
  r.method -- <- select only the required values
from
  edge_logs as t
  cross join unnest(t.metadata) as m
  cross join unnest(m.request) as r;

Examples and Templates#

The Logs Explorer includes Templates (available in the Templates tab or the dropdown in the Query tab) to help you get started.

For example, you can enter the following query in the SQL Editor to retrieve each user's IP address:

select datetime(timestamp), h.x_real_ip
from
  edge_logs
  cross join unnest(metadata) as m
  cross join unnest(m.request) as r
  cross join unnest(r.headers) as h
where h.x_real_ip is not null and r.method = "GET";

Log Source Reference#

Refer to the full field reference for each available source below. Do note that in order to access each nested key, you would need to perform the necessary unnesting joins