Read-only role generator
The analyzer never needs to write. Give it a role that can connect, read every session's statistics through pg_read_all_stats, and SELECT from the tables so plans can be collected. The snippet below does exactly that and also pins the role's sessions to read-only transactions with a statement timeout.
SQL for your_database
Run it as a superuser, or as the owner role your provider gives you, connected to the database you want analysed.
-- Read-only role for the analyzer: it can see statistics and read -- tables, and nothing else. -- Run it as a superuser, or as the owner role your provider gives you, -- connected to the database you want analysed. CREATE ROLE "pgsenpai_reader" LOGIN PASSWORD 'paOWWv22rKFdjdODRtR-krOf'; -- Connect, and read every session's entry in pg_stat_statements. GRANT CONNECT ON DATABASE "your_database" TO "pgsenpai_reader"; GRANT pg_read_all_stats TO "pgsenpai_reader"; -- Read the tables so EXPLAIN can plan and statistics can be collected. GRANT USAGE ON SCHEMA "public" TO "pgsenpai_reader"; GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO "pgsenpai_reader"; ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO "pgsenpai_reader"; -- Belt and braces: sessions are read-only and cannot run for long. ALTER ROLE "pgsenpai_reader" SET default_transaction_read_only = on; ALTER ROLE "pgsenpai_reader" SET statement_timeout = '60s';
Then connect as pgsenpai_reader with the password from the first line, for example postgresql://pgsenpai_reader@host:5432/your_database. Add more schemas above if your tables live outside public; a schema the role cannot read only means those plans are skipped, never an error.
What each statement does
CREATE ROLE … LOGIN PASSWORD. A plain login role with no superuser, create or replication rights.GRANT CONNECT. Needed on databases that revoke the defaultPUBLICconnect privilege.GRANT pg_read_all_stats. Without itpg_stat_statementshides the text of other roles' statements, and the ranking would show only this role's own (empty) history.GRANT USAGEandSELECT.EXPLAINrequires the same privileges as running the statement;ALTER DEFAULT PRIVILEGEScovers tables created later.default_transaction_read_onlyandstatement_timeout. Defence in depth: the analyzer already runs inside read-only transactions with its own timeouts, and these settings hold even if someone reuses the role by hand.
Prefer not to create a role? The analyzer also works with any existing role that has pg_read_all_stats, including the master user on managed services. See the security page for how credentials are handled.