permission denied to create extension "pg_stat_statements"

[Startouf]

Describe the bug

Trying to setup a new chatwoot server, and running either db:chatwoot_prepare or db:schema:load

rails aborted!
ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR:  permission denied to create extension "pg_stat_statements"
HINT:  Must be superuser to create this extension.
/home/chatwoot/chatwoot/db/schema.rb:16:in `block in <main>'
/home/chatwoot/chatwoot/db/schema.rb:13:in `<main>'

Caused by:
PG::InsufficientPrivilege: ERROR:  permission denied to create extension "pg_stat_statements"
HINT:  Must be superuser to create this extension.

To Reproduce

My PG Setup

CREATE USER chatwoot NOINHERIT LOGIN PASSWORD '<password>';
CREATE DATABASE chatwoot_production OWNER chatwoot;
GRANT ALL PRIVILEGES ON DATABASE chatwoot_production to chatwoot;

Expected behavior

A clear indication on what roles to give to chatwoot in the readme. Also, expecting chatwoot to get superadmin privilegies seems a bit excessive. Is there no other way to configure what you need ?

Screenshots

N/A

Browser logs

N/A

Server logs

N/A

Environment

Self hosted chatwoot, Linux VM deployment release/2.2.1

Desktop (please complete the following information):

• OS: Debian 13

Smartphone (please complete the following information): N/A

Additional context

Add any other context about the problem here.

[Startouf]

How to fix :

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

Restart your DB server login to psql and create the extension (not sure if it had to be done on the admin db or the chatwoot db or both)

CREATE EXTENSION pg_stat_statements; 
\c chatwoot_production
CREATE EXTENSION pg_stat_statements;

This should be added somewhere in the doc

[sojan-official]

@Startouf Thanks for reporting this. We will take a look and have it added to the documentation.

[shivshav]

Had to do something similar for our setup. We actually needed pgcrypto too

CREATE EXTENSION pg_stat_statements; 
CREATE EXTENSION pgcrypto; 

As a sidenote: As part of the documentation, it'd be nice for administrators to know what these extensions are used for in the application if possible 😄