Read-Only Metabase Over a Production Database
Dashboards over real data, with zero write access — and the RLS wrinkle.
Never point a BI tool at the app’s main database user. A report can then change data, and a leaked Metabase credential becomes a leaked app credential. I give Metabase its own role that can only read.
The role
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;GRANT SELECT ON ALL TABLES covers tables that exist now. Tables created later need a default privilege.
The default privilege, corrected
My first version used this:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO metabase_ro;That has a flaw. Default privileges apply to objects created by the role that runs the command. If a different role creates the tables, such as a migration role, those tables never become readable. The statement needs FOR ROLE:
ALTER DEFAULT PRIVILEGES FOR ROLE migration_role IN SCHEMA public
GRANT SELECT ON TABLES TO metabase_ro;Run the statement once for each role that creates tables. The Postgres documentation for ALTER DEFAULT PRIVILEGES describes the rule.
A quick test: have the migration role create a scratch table, then connect as metabase_ro and select from it.
Row-level security
If the app uses row-level security with policies written for its own auth context, a reporting role sees no rows. The policies match a user the reporting role does not have.
The fix I used was BYPASSRLS:
ALTER ROLE metabase_ro BYPASSRLS;The role can then read every row while still being unable to write. The Postgres documentation on row security covers the details.
The widest read grant
BYPASSRLS plus SELECT on everything is the widest read grant possible. If the Metabase password leaks, the whole database is readable. I should have said so in the first version.
Limit the grant to the schemas BI needs:
GRANT USAGE ON SCHEMA reporting TO metabase_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO metabase_ro;Better still, point the role at a reporting schema holding views that expose only the columns BI needs, and leave public ungranted. That is a design choice, not a documented step.
Metabase in its own container
Metabase runs in its own container with its own metadata database. Its saved questions, users and settings live there, apart from the data it reads. A Metabase upgrade or a corrupt metadata store then cannot touch the production data.
The Metabase documentation for production use recommends a separate application database such as Postgres in place of the default H2 file.
Connecting
In Metabase, add the database with host, port and the metabase_ro credentials. Use the least privileged network path available. Put the host on an internal network where you can.
Test:
- Run
SELECT count(*)on a table in Metabase. - Run
UPDATEin the native query editor. It should fail with a permission error.
Both outcomes follow from the grants above.