Log inStart free

insufficient privilege on pg_stat_statements

Why pg_stat_statements shows "<insufficient privilege>" instead of query text, how to check what your role can see, and how to find slow queries for one SupaHost project.

What you see

You query pg_stat_statements to find slow queries, and many rows have no query text:

 calls | mean_ms |          query
-------+---------+--------------------------
  9124 |   48.31 | <insufficient privilege>
   311 |  212.07 | <insufficient privilege>
    12 |    0.84 | select * from public.recipes where id = $1

Resetting the statistics may also fail:

ERROR:  permission denied for function pg_stat_statements_reset

Why it happens

pg_stat_statements records statistics for every role, but it only shows the query text (and queryid) to:

  • the role that ran the query,
  • superusers, and
  • members of the built-in pg_read_all_stats role.

Requests through the Data API, Auth and Storage don't run as postgres. PostgREST, for example, logs in as authenticator. So when you look as postgres without pg_read_all_stats, the queries your app actually sends are the ones you can't read. The numbers (calls, mean_exec_time, rows) are still correct; only the text is hidden.

pg_stat_statements_reset() is restricted to superusers unless someone grants execute on it.

How to fix it

  1. Make sure the extension exists in this project's database:
create extension if not exists pg_stat_statements with schema extensions;

If querying the view then fails with "pg_stat_statements must be loaded via shared_preload_libraries", the module isn't running on the server and this approach won't work; use the explain approach in step 4.

  1. Check what your role can see:
select pg_has_role(current_user, 'pg_read_all_stats', 'member') as can_read_all_stats,
       has_function_privilege('extensions.pg_stat_statements_reset(oid, oid, bigint, boolean)', 'execute') as can_reset;

If can_read_all_stats is true, you see every query's text.

  1. Filter to your project and sort by total time. On SupaHost every project is a database on one shared server, and the view covers all of them:
select s.calls,
       round(s.mean_exec_time::numeric, 2) as mean_ms,
       round(s.total_exec_time::numeric) as total_ms,
       r.rolname,
       s.query
from extensions.pg_stat_statements s
join pg_roles r on r.oid = s.userid
where s.dbid = (select oid from pg_database where datname = current_database())
order by s.total_exec_time desc
limit 20;

The rolname column tells you who ran each query even when the text is hidden: authenticator is the Data API, supabase_auth_admin is Auth, supabase_storage_admin is Storage.

  1. If you can't grant pg_read_all_stats yourself (grant pg_read_all_stats to postgres; fails with "permission denied to grant role"), you can still find slow API queries another way. Take the query your app sends, run it with explain (analyze, buffers) as the authenticated role, and check the plan. RLS performance best practices shows how. If you need full pg_stat_statements access, ask SupaHost support.

On SupaHost

  • Studio's SQL editor connects as postgres. Studio's Reports and Logs pages aren't available on SupaHost, so the SQL above is the way to see query statistics.
  • On Business you can also run these queries with sudo supahost-psql app-one over SSH.

Updated 11 November 2026 · View as Markdown · Something wrong or missing? Tell us