Post cover image

July 30, 2026

A Production-Ready RBAC Model for PostgreSQL Access Management

Introduction

By Ganesh Gaikwad

9 min read

Introduction

A new developer joins your team and can't query a single table. Three months later, you can't remove the developer who left — because half the database is owned by their account.

If you've been running PostgreSQL in any team environment for more than a few months, this scene will feel familiar.

They try to query a table and get:

ERROR:  permission denied for schema public

You grant them access. A week later they can't insert into a table someone else created last sprint, because permissions were only ever granted to the original owner's session. Eventually someone leaves the company, and when you try to clean up:

ERROR:  cannot drop role "jane.dev" because other objects depend on it

Now you're stuck running \dn+, \dt+, and a pile of REASSIGN OWNED BY statements just to remove one person from the database.

None of this is a PostgreSQL bug. It's what happens when permissions are granted to individuals instead of to a design. Every developer who ever ran CREATE TABLE with their personal login becomes a permission dependency you now have to untangle by hand.

This article walks through a role-based access control (RBAC) approach that fixes this at the root — not by granting more permissions, but by granting fewer, smarter ones.

Why Traditional Permission Management Doesn't Scale

Here's what usually happens in a database that's been alive for a year or two without a permission strategy:

Object ownership ends up scattered across a dozen different logins. Someone's personal developer account owns half the orders schema because they ran the original migration. Another set of tables belongs to whoever happened to run a one-off script during a hotfix.

Permissions get granted with individual GRANT SELECT ON table_x TO user_y statements, usually reactively, usually because someone hit a permission error and asked for it to be fixed "real quick." Nobody's tracking which of these grants are still needed.

New tables get created and nobody remembers to grant access to them, so the application breaks in a subtle way — reads work, writes silently fail, or vice versa — until someone notices and files a ticket.

LDAP or SSO users join and leave constantly, and every off-boarding turns into an archaeology project: what does this person own, what do they have access to, and what breaks if we revoke it.

None of this is anyone's fault individually. It's what happens by default when permissions are ad hoc. The fix isn't more discipline — it's a structure that makes the disciplined thing also the easy thing.

Introducing the RBAC Design

The core idea is simple: stop granting permissions to people. Grant permissions to roles, and put people into roles.

Instead of every developer, application, and reporting tool getting individual object-level grants, we define a small number of reusable PostgreSQL roles that represent what you're allowed to do, not who you are.

For a typical application database, three roles cover almost every real-world need:

None

deployer_user — the only role that owns anything

This role owns every table, sequence, view, and function in the database. It's the role your migration tool runs as — Liquibase, Flyway, Sqitch, or a DBA running a manual DDL script during a release. Nobody logs in interactively as deployer_user for day-to-day work.

The reason this matters more than it sounds: if all objects are owned by one dedicated role, dropping a developer later never touches object ownership. There's nothing to reassign, because the developer never owned anything in the first place.

app_user — what your application runs as

This is the runtime identity for your backend services. It gets exactly what an application needs to function: SELECT, INSERT, UPDATE, DELETE, and EXECUTE on functions it calls. It does not get DDL rights — an application should never be able to DROP TABLE, even accidentally, even with a bug in an ORM migration path.

read_user — reporting and analytics

Read-only access for BI tools, reporting dashboards, and analysts. SELECT is the core of this role. EXECUTE on functions is worth pausing on before you grant it blanket, because it's a common place teams over-grant. A function can contain arbitrary logic, including writes, depending on how it's defined. Handing out EXECUTE to a read-only role without checking what those functions actually do can quietly undermine the "read-only" guarantee you think you have. If your reporting layer doesn't call functions, leave EXECUTE out for this role entirely rather than granting it "just in case."

Best Practice: Treat EXECUTE as a privileged grant, not a default. A SECURITY DEFINER function can do far more than the calling role's own privileges would normally allow.

Example Environment

To keep this concrete, we'll use a retail application:

  • Database: retaildb
  • Schemas: public, orders, users

This structure isn't special — the same three-role pattern applies whether you have one schema or fifteen. What changes is how many times you repeat the grant statements below, not the underlying design.

Implementation

Here's the full setup, in the order you'd actually run it.

1. Create the roles

-- Deployment / migration role - owns all objects
CREATE ROLE deployer_user WITH LOGIN PASSWORD 'change_me' NOSUPERUSER NOCREATEDB NOCREATEROLE;
-- Application runtime role
CREATE ROLE app_user WITH LOGIN PASSWORD 'change_me' NOSUPERUSER NOCREATEDB NOCREATEROLE;
-- Read-only reporting role
CREATE ROLE read_user WITH LOGIN PASSWORD 'change_me' NOSUPERUSER NOCREATEDB NOCREATEROLE;

Note: In most production setups, app_user and read_user shouldn't hold LOGIN directly if you're authenticating through LDAP or SSO groups mapped to PostgreSQL roles. Adjust LOGIN/NOLOGIN to match your authentication model — the grant structure below stays the same either way.

2. Set up the database and schema ownership

-- Create the database with deployer_user as owner
CREATE DATABASE retaildb OWNER deployer_user;
\c retaildb
-- Create schemas, explicitly owned by deployer_user
CREATE SCHEMA IF NOT EXISTS orders AUTHORIZATION deployer_user;
CREATE SCHEMA IF NOT EXISTS users  AUTHORIZATION deployer_user;
-- public schema already exists; make ownership explicit
ALTER SCHEMA public OWNER TO deployer_user;

Schema ownership matters here for a practical reason: deployer_user needs to be the owner (or have sufficient privileges) to run ALTER DEFAULT PRIVILEGES scoped to that schema in the next step. If ownership is scattered, default privileges silently won't apply the way you expect.

3. Grant privileges, role by role

This is the script that actually runs in our environment — one block per role, immediate grants on existing objects plus ALTER DEFAULT PRIVILEGES for everything created afterward, all in one place so there's no hunting across files to see what a role can do.

deployer_user — owns everything, full rights (RWEA: read, write, execute, alter)

-- RWEA: deployer_user
GRANT USAGE, CREATE ON SCHEMA public, orders, users TO deployer_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public, orders, users TO deployer_user;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public, orders, users TO deployer_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public, orders, users TO deployer_user;

ALTER DEFAULT PRIVILEGES FOR ROLE deployer_user IN SCHEMA public, orders, users GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO deployer_user;
ALTER DEFAULT PRIVILEGES FOR ROLE deployer_user IN SCHEMA public, orders, users GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO deployer_user;
ALTER DEFAULT PRIVILEGES FOR ROLE deployer_user IN SCHEMA public, orders, users GRANT EXECUTE ON FUNCTIONS TO deployer_user;

CREATE on the schema is what actually lets deployer_user run DDL — without it, USAGE alone only lets the role see the schema, not build inside it. The table/sequence/function grants back to deployer_user itself look redundant on paper, since object owners already have full rights on what they own — but they're harmless, and they keep the script self-consistent if ownership ever needs to be reassigned or audited later.

app_user — application runtime (RWE: read, write, execute)

-- RWE: apps_user
GRANT USAGE ON SCHEMA public, orders, users TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public, orders, users TO app_user;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public, orders, users TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public, orders, users TO app_user;

ALTER DEFAULT PRIVILEGES FOR ROLE deployer_user IN SCHEMA public, orders, users GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE deployer_user IN SCHEMA public, orders, users GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE deployer_user IN SCHEMA public, orders, users  GRANT EXECUTE ON FUNCTIONS TO app_user;

Notice app_user only gets USAGE on schemas, never CREATE. That's the line that keeps DDL out of the application's hands. Also notice the ALTER DEFAULT PRIVILEGES block is registered FOR ROLE deployer_user, not FOR ROLE app_user — future privileges are always defined against whichever role actually creates the objects, which in this design is always deployer_user.

read_user — read-only reporting

-- Read-Only: read_user
GRANT USAGE ON SCHEMA public, orders, users TO read_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public, orders, users TO read_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public, orders, users TO read_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public, orders, users TO read_user;

ALTER DEFAULT PRIVILEGES FOR ROLE deployer_user IN SCHEMA public, orders, users GRANT SELECT ON TABLES TO read_user;
ALTER DEFAULT PRIVILEGES FOR ROLE deployer_user IN SCHEMA public, orders, users GRANT SELECT ON SEQUENCES TO read_user;
ALTER DEFAULT PRIVILEGES FOR ROLE deployer_user IN SCHEMA public, orders, users GRANT EXECUTE ON FUNCTIONS TO read_user;

Warning: This script grants EXECUTE to read_user by default, across every function in every schema. That's a deliberate call some teams make for convenience, but it's worth revisiting per environment — a function isn't guaranteed to be read-only just because the role calling it is. If none of your reporting tools actually call functions, drop the EXECUTE lines for read_user entirely rather than granting them "just in case." See the callout under read_user above for why this matters.

This covers everything that already exists in the database at the moment you run it, and everything deployer_user creates from this point forward — that's the job the paired ALTER DEFAULT PRIVILEGES statement does in each block above.

Why ALTER DEFAULT PRIVILEGES Matters

This is the step that turns RBAC from "a nice idea" into something that actually holds up over time.

Without it, every new table your migration tool creates needs a fresh GRANT before the application can touch it. In practice, that means either your CI/CD pipeline has to run explicit grants after every migration, or you get the 2 a.m. page where a new feature deployed cleanly but the app can't write to its own table.

With default privileges configured correctly, the flow looks like this:

None

A new table shows up already correctly permissioned, because the role that created it had a standing instruction on file: "anything you create in this schema, app_user gets CRUD on it, read_user gets read access." You configure that instruction once per schema, and it applies indefinitely.

User On-boarding

This is where the design pays for itself immediately.

An LDAP user joins and needs application-level database access. Instead of working through what tables exist, what a similar teammate has, and writing a dozen GRANT statements, on-boarding becomes:

GRANT app_user TO "john.doe";

Need someone set up for reporting instead?

GRANT read_user TO "reporting_user";

That's it. No object-level permissions are granted directly, ever. The person inherits exactly what the role defines — nothing more, nothing accidentally missing.

User Off-boarding

Off-boarding is where most permission systems fall apart, and where this one quietly shines.

REVOKE app_user FROM "john.doe";
DROP ROLE "john.doe";

Because john.doe never owned a single table, sequence, or function — deployer_user did — there's no REASSIGN OWNED BY step, no dependency errors, no half-hour spent figuring out what breaks if this login goes away. The role membership is removed, the login is dropped, and the database doesn't notice.

None

Best Practices

A few rules worth holding onto once this is in place:

Never let developers own production objects with personal logins. If a table is owned by jane.dev, you've already reintroduced the problem this whole design exists to avoid.

Keep the deployment role separate from the application role. deployer_user should never be the identity your backend service authenticates as — DDL rights and runtime application access should never live in the same login.

Always define ALTER DEFAULT PRIVILEGES for every schema where objects get created. If you add a fourth schema next year, this step is easy to forget — put it in your schema-creation runbook.

Grant users role membership, not object-level permissions, without exception. The moment someone gets a one-off GRANT SELECT ON table_x TO their_username, you've created a dependency that off-boarding has to account for again.

Follow least privilege deliberately, especially with EXECUTE. Don't grant it to read_user by default — only when a real reporting need requires calling a function, and only for the functions that need it.

Review privileges periodically, not just when something breaks. A quarterly check of pg_roles, information_schema.role_table_grants, and \ddp (default privileges) catches drift before it becomes an incident.

Tip: Run \ddp in psql periodically — it shows exactly which default privileges are configured per schema, which is the fastest way to confirm this setup is still intact after a schema change.

Conclusion

None of this makes PostgreSQL permissions more complicated — it makes them boring, in the best sense of the word. Three roles, defined once, cover on-boarding, off-boarding, deployments, and reporting access for a database that might run for years and outlive half the team that built it.

The traditional approach breaks down because it optimizes for right now — get this one developer unblocked, fix this one permission error. RBAC optimizes for later — the off-boarding six months from now, the new schema next quarter, the audit nobody's looking forward to.

Design your permissions once. Manage people, not objects, forever.