Lorixen.
EN

A read-only database user for reports and BI tools

Short answer: When you connect a reporting or BI tool to your app's database, give it its own login with read-only access, never the app's own credentials. In PostgreSQL, create a dedicated role, grant SELECT only on the tables or views it needs, set default privileges so new tables follow the same rule, add a statement timeout, and ideally point it at a read replica. Expose sensitive data through views that leave out columns like passwords, tokens and full personal details.

Why not just reuse the app's login?

It's tempting to paste the app's database URL into a dashboard tool and start building charts. The risks:

  • Accidental changes. The app's login can usually insert, update and delete. A mistyped query in a SQL editor, or a tool's "edit row" feature, can change live data.
  • Too much visibility. The app can read every column, including password hashes, API tokens and personal details that reporting doesn't need.
  • Performance. A heavy report runs with the same priority as customer requests and can slow the app.
  • No separation. If the reporting tool's credentials leak, the attacker has full app access, and you can't revoke the tool without breaking the app.

A dedicated read-only role solves all four.

Create a read-only role

In PostgreSQL, a minimal setup looks like this, with names as placeholders:

CREATE ROLE reporting LOGIN PASSWORD 'use-a-long-generated-password';
GRANT CONNECT ON DATABASE shop TO reporting;
GRANT USAGE ON SCHEMA public TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;

That grants read access to tables that exist now. Store the password in your secrets manager or the BI tool's credential store, not in a shared document.

Cover tables created later

GRANT SELECT ON ALL TABLES only applies to existing tables. PostgreSQL's documentation for ALTER DEFAULT PRIVILEGES explains that it sets privileges for objects created in the future and doesn't affect existing ones. So add:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO reporting;

Here app_owner is the role that creates tables, usually the one your migrations run as. Default privileges are tied to the creating role, so if migrations run as a different role, set them for that one.

The quick option: pg_read_all_data

PostgreSQL also has predefined roles. Its documentation describes pg_read_all_data as allowing reading all data, tables, views and sequences, as if having SELECT rights on those objects and USAGE on all schemas. Granting it to a reporting role is quick:

GRANT pg_read_all_data TO reporting;

The trade-off is that it really is all data, in every schema, including tables with sensitive columns. For most businesses, explicit grants on chosen tables or views are the better fit.

Hide sensitive columns with views

Reports rarely need everything. Create a separate schema with views that show only what's useful:

CREATE SCHEMA reporting;
CREATE VIEW reporting.customers AS
  SELECT id, created_at, city, country, marketing_opt_in
  FROM public.customers;
GRANT USAGE ON SCHEMA reporting TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO reporting;

Then grant the reporting role access to the reporting schema only, not to public. Staff building dashboards see a tidy, safe set of tables; password hashes, tokens, full addresses and notes stay out of reach.

Views are also a good place to rename confusing columns, join lookup tables and convert status codes into words, which makes self-service reporting easier.

Limit query impact

Reporting queries can be heavy. Protect the app:

  • Set a statement timeout for the role, such as ALTER ROLE reporting SET statement_timeout = '60s';, adjusted to your reports.
  • Limit connections with ALTER ROLE reporting CONNECTION LIMIT 5; or similar, so a misconfigured tool can't exhaust the database.
  • Use a read replica if you have one. PostgreSQL's hot standby documentation describes standbys that accept strictly read-only connections. Reports run there won't compete with customer traffic, with the caveat that replica data lags slightly behind the primary.

Managed database services usually make replicas a few clicks away; check your provider's pricing.

Consider summary tables for common reports

If the same heavy report runs every morning, such as revenue by day or jobs by region, it can be cheaper to compute it once. A scheduled job, or a materialised view refreshed overnight, stores the results in a small table the reporting role can read instantly. Dashboards load faster, and the live tables are queried far less often.

Restrict where connections come from

Even with a read-only role, limit network access:

  • Allow connections only from the BI tool's published IP addresses, or through a private network link.
  • Require TLS for connections.
  • Avoid opening the database to the whole internet just so a dashboard can reach it.

Review access regularly

Every few months:

  1. Check which roles exist and what they can read.
  2. Remove tools and people who no longer need access.
  3. Rotate the reporting password, especially after staff changes.
  4. Look at the slowest reporting queries and add indexes or summary tables where they help.

A worked example

Picture a hypothetical Canberra cleaning-services company whose operations manager connected a dashboard tool using the app's main database login. A test query in the tool's editor accidentally updated a table of job statuses, and customers received confusing notifications. The developer created a reporting role with access only to a set of views that excluded contact details and internal notes, set default privileges, a statement timeout and a connection limit, and pointed the tool at a read replica. Dashboards kept working, and the tool can no longer change anything.

FAQ

Does read-only access stop people seeing personal data?

Only if you limit what they can read. Use views or column-level grants to keep sensitive fields out.

Can a read-only user still slow down my database?

Yes, with heavy queries. Timeouts, connection limits and a replica reduce the risk.

Do I need a separate role per tool?

It's good practice. Separate roles let you revoke one tool without affecting others.

If you're connecting reporting tools to your app's database, ask us to set up safe access.

Sources

WhatsAppBook a call