I wanted dashboards over my app's production data without giving a BI tool any way to change that data. Metabase plus a carefully-scoped database role does exactly that.

The principle

Never point BI at your app's main database user. Create a dedicated role that can read and nothing else:

CREATE ROLE metabase_ro LOGIN PASSWORD '********';
GRANT USAGE ON SCHEMA public TO metabase_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO metabase_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO metabase_ro;

That last statement is the one people leave out and then wonder why next month's tables are invisible. GRANT ... ON ALL TABLES is a one-time snapshot of what exists right now; ALTER DEFAULT PRIVILEGES is what makes the grant apply to tables created later. It also only covers objects created by the role that runs it, so if your migrations run as a different user, run it as that user too.

Two more things worth putting on the role while you are here:

ALTER ROLE metabase_ro SET default_transaction_read_only = on;
ALTER ROLE metabase_ro SET statement_timeout = '60s';
ALTER ROLE metabase_ro CONNECTION LIMIT 10;

The first is belt and braces on top of the grants. The second is the one that saves you: a dashboard with an unbounded query against a large table will otherwise sit there consuming a connection and holding a transaction open, and on a busy database that is how a reporting tool takes production down without writing a single byte.

The row-level-security wrinkle

My tables have row-level security policies written for the app's auth context, not a reporting role, so a plain read-only role would see almost nothing. Granting the role BYPASSRLS lets it read every row for analytics while still being unable to write. A deliberate trade: the BI role sees all data, but physically cannot mutate it.

Be honest with yourself about what that trade costs. BYPASSRLS turns off the mechanism that keeps one tenant's rows away from another, so anyone who can write SQL in Metabase can read every customer's data. That is fine for a solo operator looking at his own platform and it is not fine the moment somebody else gets an account. The better shape at that point is to leave BYPASSRLS off and grant the role SELECT on purpose-built aggregate views instead, so the reporting surface is a thing you designed rather than the whole schema.

Connecting

Metabase runs in its own container with its own metadata database and connects out as metabase_ro. Keep that metadata database separate from the one you are reporting on, since it holds your saved questions, users and the encrypted connection credentials. Dashboards build against real data; a compromised Metabase can, at worst, read.