Read-only Role in PostgreSQL: Correct Grants for Analytics and BI

Analysts and BI tools need to read your data without the power to change it. In PostgreSQL the clean answer is a dedicated read-only role. Here is a working recipe and the one gotcha that trips up almost everyone.

Create the role and grant schema access

A read-only role is usually a group role (no login) that real users are added to. Privileges live on the group; you add and remove people one by one.

CREATE ROLE readonly NOLOGIN;

CREATE ROLE analyst_anna LOGIN PASSWORD 'change_me';
GRANT readonly TO analyst_anna;

The first non-obvious step is USAGE on the schema. Without it the role cannot see a single table inside public, even when table-level grants are in place.

GRANT USAGE ON SCHEMA public TO readonly;

USAGE opens the schema so its contents are visible, but it does not let you do anything with the tables yet. That is the next grant.

SELECT on existing tables

Now grant read access to every table that already lives in the schema. One command via ALL TABLES covers them all.

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

After this, the role can read data but cannot write anything:

-- works for readonly
SELECT country, count(*) AS users_cnt
FROM users
GROUP BY country
ORDER BY users_cnt DESC;

-- fails: permission denied
UPDATE orders SET status = 'cancelled' WHERE amount < 0;

If the schema has sequences your reports need to inspect, grant them separately:

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;

The gotcha: new tables are not readable

This is where most setups break. GRANT ... ON ALL TABLES is a one-time snapshot: it grants only on the tables that exist at that moment. Create employees tomorrow and the analyst hits a permission error.

CREATE TABLE employees (
  id         bigint PRIMARY KEY,
  name       text NOT NULL,
  manager_id bigint,
  dept       text,
  salary     numeric(12,2)
);

-- readonly gets: permission denied for table employees
SELECT dept, avg(salary) FROM employees GROUP BY dept;

The fix is ALTER DEFAULT PRIVILEGES. It is a forward-looking rule: any table created from now on automatically grants SELECT to our role.

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

Important: default privileges are tied to the role that creates the objects. The rule above fires for tables created by the current role. If migrations run as a different user (say app_owner), set it explicitly: ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO readonly;. Otherwise new tables fall out of access again.

The correct order is: first ALTER DEFAULT PRIVILEGES for future tables, then GRANT ... ON ALL TABLES for the existing ones. Together they cover both past and future.

Analytics and BI access

BI tools (Metabase, Superset, Looker) connect as this role and build dashboards happily; they physically cannot write to the database. To cap the load, set a per-statement time limit:

ALTER ROLE readonly SET statement_timeout = '30s';

If access should be partial, do not expose raw tables in full. Hand out a view and grant SELECT on it:

CREATE VIEW orders_public AS
SELECT id, user_id, amount, status, created_at
FROM orders
WHERE status <> 'fraud';

GRANT SELECT ON orders_public TO readonly;

Differences in other databases

• MySQL: schemas and databases are the same thing, so there is no separate USAGE ON SCHEMA. You build a read-only role like this: GRANT SELECT ON shop.* TO 'readonly'@'%';. The shop.* pattern automatically covers future tables, so no ALTER DEFAULT PRIVILEGES equivalent is needed.
• ClickHouse: use GRANT SELECT ON shop.* TO readonly;, while restrictions are more often expressed through profiles and quotas (readonly = 1) rather than per-object grants.

Remember the core: a no-login group role, USAGE on the schema, SELECT on existing tables, and ALTER DEFAULT PRIVILEGES for future ones. Skip the last step and access quietly breaks on the next migration.