RBAC in PostgreSQL: A Field Guide for Data Engineers

It was a Tuesday

A new analyst joined the data team. They needed "access to the warehouse." A well-meaning engineer — mid-onboarding, mid-standup, mid-caffeine — ran:

GRANT ALL ON SCHEMA warehouse TO new_analyst;
GRANT ALL ON ALL TABLES IN SCHEMA warehouse TO new_analyst;

Three months later, that same analyst kicked off a one-off DELETE in a Jupyter notebook against what they thought was a dev schema. It wasn't. Two million rows in dim_customer went on vacation. They came back from restore, but the dashboard trust took longer.

The postmortem landed on a single uncomfortable finding: the analyst never needed DELETE. Or DROP. Or ALTER. They needed SELECT on six tables. What they got was the database equivalent of a master key taped under the doormat, because at the time, that was the easiest thing to grant.

This is not a story about a careless analyst. It's a story about a careless permission model. And it's the reason every data engineer should understand RBAC — Role-Based Access Control — in whatever Postgres they happen to be standing in front of.

A note on scope: the syntax throughout this article is PostgreSQL, but the ideas are not. Snowflake, Redshift, BigQuery, SQL Server, and most modern warehouses implement some flavor of roles, grants, and inherited privileges — the commands differ, the mental model doesn't. Learn it once here, and you'll recognize it everywhere.

Why RBAC, not just "users with passwords"

The naive model is one user, one set of grants. That works beautifully until you have more than about three users. Then this happens:

• Onboarding someone means remembering twenty grants to apply.
• Offboarding means hoping you caught all twenty.
• Auditing "who can write to payments" means running queries against pg_user for every user.
• Changing a policy means editing every user.

Or worse — the version that actually happens under deadline pressure — everyone just gets the same one do-everything role, because provisioning four people with individually reasoned grants is slower than provisioning one:

Four very different actors — a human doing analysis, a human writing code, a service ingesting data, an automated job transforming it — all indistinguishable from each other the moment something goes wrong, because they're all just "the role." That flattening is the actual cost of the naive model. It's not that granting ALL is lazy (though it is); it's that it erases the one piece of information you need most during an incident: which kind of actor did this, and what should it have been allowed to do?

The fix is a layer of indirection — and if you've ever reached for a function instead of copy-pasting code, you already understand why. Group the permissions, give the group a name, and attach humans (and service accounts) to the name. Now onboarding is one statement, offboarding is one statement, and "who can write to payments" has one answer: the role, and here are its members.

That's RBAC. It's not a feature, it's a way of organizing your fears.

The mental model: everything is a role

In Postgres, a user is just a role with the LOGIN attribute. There's no separate user object. A "group" is also just a role — one without LOGIN. Roles can be members of roles, which can be members of roles, forming a single inheritance tree. Privileges flow down.

-- This is a "user":
CREATE ROLE alice LOGIN PASSWORD '...';

-- This is a "group" (no LOGIN):
CREATE ROLE developer;

-- alice becomes a member of the group:
GRANT developer TO alice;

The beauty of this is uniformity. The cost is that the word "role" is doing a lot of work, and the distinction between "who" and "what they can do" lives entirely in attribute flags.

Two attributes worth knowing cold:

• INHERIT (default ON) — when a role is a member of another role, it automatically has the privileges of the parent. If you flip this off with CREATE ROLE x NOINHERIT, the role has to explicitly SET ROLE to use those privileges. Useful for "break-glass" admin roles; confusing for everything else. Leave it on. (One version note: as of Postgres 16, INHERIT can also be set per membership grant — GRANT role TO member WITH INHERIT TRUE/FALSE — rather than only as a role-level attribute. If you don't specify it, the grant falls back to the member role's own INHERIT setting, so the simple mental model above still holds for the common case.)
• LOGIN — the only thing that separates "a user" from "a group." Without LOGIN, the role can't connect; it exists purely to be a permission bundle that other roles inherit.

The four basic roles every data team needs

Most data platforms end up needing roughly this shape: one role per function, not per person, scoped to exactly what that function requires and nothing past it. Four schemas set the stage:

• raw — data as it lands from source systems, unmodeled.
• staging — the working area where transformations happen.
• marts — the final, BI-facing output. What dashboards and analysts actually query.
• sandbox — scratch space for humans to build and test things without touching anything real.

The picture worth noticing: only two roles can write to production data, and they're both non-human. loader writes to raw; automation writes to staging and marts. Every role a person actually connects with — developer, analyst — is read-only outside its own sandbox. That's not an accident; it's the whole point of the exercise.

1. loader — for your ingestion service account

The loader pulls data from source systems into raw. It needs to create and update tables there — schemas drift, source systems add columns — but it has no business anywhere downstream.

CREATE ROLE loader LOGIN PASSWORD '...' CONNECTION LIMIT 10;

GRANT USAGE, CREATE ON SCHEMA raw TO loader;
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE
    ON ALL TABLES IN SCHEMA raw TO loader;
ALTER DEFAULT PRIVILEGES FOR ROLE loader IN SCHEMA raw
    GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO loader;

Don't forget sequences. If your raw tables have IDENTITY or SERIAL columns (auto-incrementing primary keys), writes to those columns consume from an underlying sequence. You need:

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA raw TO loader;
ALTER DEFAULT PRIVILEGES FOR ROLE loader IN SCHEMA raw
    GRANT USAGE, SELECT ON SEQUENCES TO loader;

Without this, inserts fail with permission denied for sequence. It's an easy thing to miss the first time you wire up a loader, and an annoying one to debug the first time it bites you.

What loader conspicuously does not have: any grant at all on staging or marts. That absence is the control. A compromised or misbehaving loader can make a mess of raw — which you can reload from source — and nothing else.

2. developer — for team members building and testing

Developers need two things that pull in opposite directions: they need to see real production data to build against, and they need somewhere to make mistakes that doesn't matter. Give them both, cleanly separated.

CREATE ROLE developer;

-- Read access to real data, everywhere:
GRANT USAGE ON SCHEMA raw, staging, marts TO developer;
GRANT SELECT ON ALL TABLES IN SCHEMA raw, staging, marts TO developer;
ALTER DEFAULT PRIVILEGES FOR ROLE loader IN SCHEMA raw
    GRANT SELECT ON TABLES TO developer;
ALTER DEFAULT PRIVILEGES FOR ROLE automation IN SCHEMA staging, marts
    GRANT SELECT ON TABLES TO developer;

-- Full control over a scratch schema — but only that schema:
GRANT USAGE, CREATE ON SCHEMA sandbox TO developer;
GRANT SELECT, INSERT, UPDATE, DELETE
    ON ALL TABLES IN SCHEMA sandbox TO developer;
ALTER DEFAULT PRIVILEGES FOR ROLE developer IN SCHEMA sandbox
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO developer;

Notice what's absent: no CREATE on raw, staging, or marts, and no INSERT/UPDATE/DELETE there either. A developer can read every production table and prototype freely in sandbox, but they cannot create or modify a single production-level table. That boundary is the entire role.

One design decision worth making explicitly: is sandbox one shared schema, or one schema per developer (sandbox_alice, sandbox_bob)? A shared schema is simpler to set up but means developers can see (and accidentally collide with) each other's scratch tables. Per-developer schemas avoid that at the cost of one extra CREATE SCHEMA + grant per onboarding. For a small team, shared is usually fine; past five or six developers, the collisions start to outweigh the convenience.

3. automation — for the pipeline that actually builds production

This is the role that creates and maintains everything in staging and marts — your dbt runs, your orchestrated transformation jobs, your migrations. The load-bearing decision here isn't the grant list, it's that no human connects as this role. It exists so that "what changed production" always has one answer: a CI job, not whoever happened to be at a keyboard.

CREATE ROLE automation;

GRANT USAGE ON SCHEMA raw TO automation;
GRANT SELECT ON ALL TABLES IN SCHEMA raw TO automation;

GRANT USAGE, CREATE ON SCHEMA staging, marts TO automation;
GRANT SELECT, INSERT, UPDATE, DELETE
    ON ALL TABLES IN SCHEMA staging, marts TO automation;
ALTER DEFAULT PRIVILEGES IN SCHEMA staging, marts
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO automation;

Then a dedicated login that carries the role, connected to only by your pipeline runner:

CREATE ROLE automation_runner LOGIN PASSWORD '...' CONNECTION LIMIT 4;
GRANT automation TO automation_runner;

# dbt profiles.yml — or wherever your orchestrator reads credentials from
warehouse:
  target: prod
  outputs:
    prod:
      type: postgres
      host: db.internal
      user: automation_runner
      password: "{{ env_var('DBT_PASSWORD') }}"
      port: 5432
      dbname: warehouse
      schema: staging
      threads: 4

Keep the password out of the file (the env_var pattern above), and keep threads modest — every thread is a concurrent connection, and a wide transformation project can saturate a small instance's connection pool faster than you'd expect. That's also why automation_runner above carries CONNECTION LIMIT 4.

If you need a separate, more tightly-gated path for schema-breaking changes — dropping a column, renaming a table, anything that isn't a routine dbt run — consider a second, human-triggered role for that specifically, with its own review step before anything connects as it. The principle: the thing that runs your daily pipeline should not be the same thing that can DROP SCHEMA marts CASCADE without someone looking at the diff first.

4. analyst — for humans and BI tools reading the finished product

The narrowest role on purpose. Analysts, Looker, Metabase, your CFO's pivot table — none of them need to see raw or staging. They need the marts, and nothing upstream of them.

CREATE ROLE analyst;

GRANT USAGE ON SCHEMA marts TO analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA marts TO analyst;

-- The magic line. Tables created later are auto-granted:
ALTER DEFAULT PRIVILEGES FOR ROLE automation IN SCHEMA marts
    GRANT SELECT ON TABLES TO analyst;

That ALTER DEFAULT PRIVILEGES line is the most important sentence in this whole article. Skip it and every new table automation creates in marts is invisible to analysts until someone complains in Slack.

Three forms of ALTER DEFAULT PRIVILEGES worth knowing, since the syntax is easy to get subtly wrong:

• ALTER DEFAULT PRIVILEGES ... GRANT ... TO role; — applies to tables created by you, the user running the statement. Almost never what you want.
• ALTER DEFAULT PRIVILEGES FOR ROLE automation ... — applies to tables created by automation. This is the form you usually want, because your pipeline role is the one creating new objects.
• ALTER DEFAULT PRIVILEGES IN SCHEMA marts ... — scopes the rule to one schema, regardless of who creates in it.

You can combine them, as in the analyst grant above: "any table automation creates in marts, analysts can read."

Possible Pitfalls

"The service account that could"

Every ETL tool ships with docs that say something like "create a user with the SUPERUSER role, or grant all privileges." This is the path of least resistance, and it is the wrong path. A loader does not need to drop databases. Read the tool's "minimum required privileges" page — it exists, they wrote it for a reason — and grant exactly that.

In Postgres, the trap looks like CREATE ROLE automation_runner WITH SUPERUSER; or broad GRANT ALL. Don't.

The forgotten ALTER DEFAULT PRIVILEGES

You set up analyst. You grant SELECT ON ALL TABLES in marts. Analysts are happy. Two weeks later, automation creates fct_orders. Analysts file a ticket: "permission denied." You forgot the default-privileges line in the original setup. Now you're patching it and backfilling grants:

-- Fix it going forward:
ALTER DEFAULT PRIVILEGES FOR ROLE automation IN SCHEMA marts
    GRANT SELECT ON TABLES TO analyst;
-- Fix what's already broken:
GRANT SELECT ON ALL TABLES IN SCHEMA marts TO analyst;

Row-Level Security that isn't FORCEd

You enable RLS on customers, write a policy that filters by region, and feel good about yourself. Then the table owner (often your automation role) queries it and sees every row, because by default RLS does not apply to the table owner. Fix it:

ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE customers FORCE ROW LEVEL SECURITY;  -- applies to owner too

FORCE is the difference between "we have RLS" and "we have RLS that works."

GRANT ALL when you meant GRANT USAGE

In Postgres, accessing objects in a schema requires USAGE on the schema. GRANT ALL ON SCHEMA x TO role grants CREATE too — meaning the role can now make tables in your schema. That's exactly the kind of grant that quietly turns developer or analyst into something with write access nobody intended. Read what ALL means before you type it.

Sequences on SERIAL / IDENTITY columns

Already mentioned above under loader, repeating because it's so common: granting INSERT on a table is not enough if the table has an auto-incrementing column. Grant USAGE, SELECT ON SEQUENCES too, or use the DEFAULT PRIVILEGES form to handle future tables.

Forgetting that pg_hba.conf is a layer below RBAC

Postgres has two access-control systems and people often conflate them. RBAC (roles, grants) decides what an authenticated user can do. pg_hba.conf (host-based authentication) decides who is allowed to authenticate from where. You can have a perfectly-scoped role that still can't connect because pg_hba.conf doesn't permit their IP. When debugging "permission denied" at connection time, check pg_hba.conf first — it's almost certainly not an RBAC issue.

The principle, restated

Least privilege isn't paranoia. It's a recognition that the blast radius of a mistake is set by the permissions, not by the intent. Good people make mistakes; good permission models make those mistakes shallow.

Audit yourself regularly. Once a quarter, run:

-- Who can do what?
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles ORDER BY rolname;

-- What's been granted on a specific schema?
SELECT grantee, table_schema, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema IN ('raw', 'staging', 'marts')
ORDER BY grantee, table_schema;

If the lists are short and boring, you did it right. If loader or analyst shows up with write access to marts, go back and read the section about the service account that could.

RBAC is one of those topics that feels like paperwork until the day it isn't. Four roles, four schemas, and an afternoon spent scoping them properly is a small price for never having to explain a two-million-row DELETE in a postmortem.