Every growing SaaS company hits the same wall. Your ops lead wants to know how many trials converted last week. Your support manager wants a breakdown of tickets by plan. Your CEO wants MRR by cohort. And all of those questions land in the same place: the one or two engineers who actually know the schema.
So you become a human query API. Someone Slacks you "can you pull X?", you context-switch, write a SELECT, paste a screenshot, and go back to what you were doing. Multiply that by five teammates and you've lost half a day to being a reporting middleman.
The obvious fix — "just give them database access" — is a trap. Non-technical users don't want a psql prompt, and even if they did, handing out raw credentials to production is how you end up with a DELETE that forgot its WHERE, or a support rep who can suddenly read every customer's payment token. This article walks through how to give non-technical people real, self-serve access to insights without giving them the keys to blow up your database.
Start with a read-only role, always
The single most important control is boring: a database role that can only read. Not "we trust them," not "they promised to be careful" — a role that is physically incapable of writing.
In PostgreSQL, you build it up from nothing:
-- 1. Create a login role for analytics
CREATE ROLE analytics_ro WITH LOGIN PASSWORD 'use-a-real-secret';
-- 2. Let it connect and see the schema, but nothing more
GRANT CONNECT ON DATABASE app_production TO analytics_ro;
GRANT USAGE ON SCHEMA public TO analytics_ro;
-- 3. Read-only on existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_ro;
-- 4. And on tables you create in the future
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO analytics_ro;
That last step trips up almost everyone. GRANT SELECT ON ALL TABLES only covers tables that exist right now. Ship a new invoices table next month and your analytics role can't see it. ALTER DEFAULT PRIVILEGES fixes that going forward.
A common gotcha worth calling out: PostgreSQL's permission layers stack, and a higher layer can quietly block a lower one. If analytics_ro has SELECT on a table but not USAGE on the schema that contains it, every query fails with a confusing permission error. Grant schema USAGE first, then table privileges.
Hide the columns that should never leave the database
Read-only stops writes, but it doesn't stop a support rep from running SELECT * FROM users and seeing password hashes, API keys, or PII they have no business seeing. Read-only is not the same as safe-to-read.
PostgreSQL supports column-level grants, so you can be surgical:
-- Revoke the broad grant on the sensitive table
REVOKE SELECT ON users FROM analytics_ro;
-- Re-grant only the safe columns
GRANT SELECT (id, email, plan, country, created_at)
ON users TO analytics_ro;
Now a query touching password_hash or stripe_customer_id fails outright, while the useful columns stay available. The catch is that SELECT * will now error for this role — which is actually a feature, because it forces explicit column lists.
For anything more complex than "hide a few columns," reach for a view. Views are the cleanest way to hand non-technical users a curated, friendly shape of the data:
CREATE VIEW reporting.active_customers AS
SELECT
u.id AS customer_id,
u.email,
u.plan,
s.status AS subscription_status,
s.mrr_cents / 100.0 AS mrr,
u.created_at::date AS signed_up_on
FROM users u
JOIN subscriptions s ON s.user_id = u.id
WHERE s.status = 'active';
GRANT SELECT ON reporting.active_customers TO analytics_ro;
The user queries active_customers and never touches the underlying tables, the joins, or the sensitive columns. You've turned a messy schema into a clean, self-documenting reporting surface.
Isolate each customer's data with row-level security
If your non-technical users are your customers (embedded analytics), read-only and column grants aren't enough — you need to guarantee customer A can never see customer B's rows. That's what row-level security (RLS) is for.
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.current_tenant')::int);
Your application sets the tenant per session:
SET app.current_tenant = '42';
-- From now on, every query on `orders` only returns tenant 42's rows,
-- even a bare SELECT * FROM orders.
The big win: isolation lives in the database, not in a WHERE clause your app might forget. One missing filter in application code has leaked entire customer tables before. With RLS, the guarantee holds even if the query is careless.
Give them an interface, not a SQL prompt
Locking down the data is half the job. The other half is that your ops lead is never going to write a window function. Non-technical users need a layer between them and SQL. There are three broad approaches:
| Approach | How it works | Best for |
|---|---|---|
| Prebuilt dashboards | You write the queries once; they filter and view | Recurring KPIs everyone watches |
| Semantic layer | You define metrics (MRR, churn) as reusable building blocks; users drag & drop | Teams that ask varied but related questions |
| AI / text-to-SQL | Users type "revenue by plan last quarter"; AI generates the SQL | Open-ended, ad-hoc exploration |
Text-to-SQL is the newest and most exciting of the three, but it changes your security model rather than replacing it. When an AI writes queries on behalf of a user, the read-only role and column grants become more important, not less — they're the guardrails that keep a hallucinated or over-broad query from doing damage. Point AI query tools at the restricted analytics_ro role and the curated reporting views, never at a superuser connection.
A quick sanity checklist
Before you hand any non-technical user (or AI agent) access to your data, run through this:
| Check | Why it matters |
|---|---|
| Connection is read-only | No writes, ever — physically enforced by the role |
| Sensitive columns revoked or hidden behind views | PII, secrets, and hashes never surface in a query |
| Multi-tenant tables have RLS enabled | Customers can't see each other's rows |
Query timeout set (e.g. statement_timeout) |
A runaway JOIN can't melt production |
| Access points at a replica, not primary | Heavy reporting queries don't slow down your app |
That fourth row deserves emphasis. A non-technical user doesn't know that a cross join over two big tables is expensive. Set a statement_timeout on the analytics role so a bad query cancels itself instead of pinning your CPU:
ALTER ROLE analytics_ro SET statement_timeout = '30s';
And if you can, run reporting against a read replica. That way even a perfectly legitimate but heavy dashboard refresh never competes with the queries your customers depend on.
Common mistakes to avoid
Giving out the app's database user. It's tempting to reuse the credentials your application already has. Don't — that role can write, and it can usually read everything. Always mint a dedicated, minimal role.
Relying on the frontend to hide data. If sensitive columns are filtered out only in your dashboard UI, anyone who inspects a network request or exports raw data can still reach them. Enforce restrictions at the database layer where they can't be bypassed.
Forgetting ALTER DEFAULT PRIVILEGES. New tables silently become invisible to your read-only role, and you'll waste an afternoon debugging "why can't they see the new report?"
Skipping the timeout. The first time a non-technical user accidentally runs an unbounded query, you'll wish you'd set one. Do it upfront.
Key takeaways
Safely exposing insights isn't about trust — it's about building a data access path where mistakes are structurally impossible. A dedicated read-only role stops writes. Column grants and reporting views hide what should stay hidden. Row-level security isolates tenants. A statement timeout and a read replica protect performance. And an interface — dashboards, a semantic layer, or AI text-to-SQL — meets non-technical users where they are.
Get those layers right and you stop being a human query API. Your teammates answer their own questions, your data stays protected, and you get your afternoons back.
How do you hand out data access at your company — read-only roles, a BI tool, an embedded dashboard, or something homegrown? Drop your setup (and your horror stories) in the comments. I'd love to hear what's worked and what's bitten you.
Top comments (0)