View PostgreSQL logs in Supabase

Supabase doesn't yet provide a way to see postgresql logs from the admin interface.

But there is a way to see the logs from within PostgreSQL, using SQL.

The file_fdw extension

The file_fdw extension allows to wrap an external data source into a table using COPY FROM under the hood.

Supabase's PostgreSQL comes with the file_fdw extension, and since 26th of July 2021 , Supabase's PostgreSQL logs into a CSV file.

So the CSV logs file can be mapped into a PostgreSQL table as described here.

How to

Currently the logs file path is pg_log/postgresql.csv

But to be sure of the logs file path in your instance you can run :

select pg_current_logfile();

Then, to setup the logs table, run :

CREATE EXTENSION file_fdw;

CREATE SERVER logserver FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE pglog (
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  application_name text,
  backend_type text
) SERVER logserver
OPTIONS ( filename 'pg_log/postgresql.csv', format 'csv' );

If you query the whole table it might fail depending on the client you are using (it fails in app.supabase.io) since the amount of rows can be huge.
The logrotate treshold for postgresql.csv is currently set to a size of 50MB , this is a lot of rows...
To avoid problems with the result size you can filter or limit the results with SQL.