Every production database runs into the same question sooner or later: who should be allowed to do what? And the answer is almost never “everyone can do everything.”

An analyst running weekly reports shouldn’t be able to delete data from the payments table. A backend service that only reads user profiles shouldn’t be able to drop tables. A cron job importing tracks definitely shouldn’t have access to encrypted payment tokens.

These boundaries are enforced through the database’s permission system, built on users, roles, and privileges.

In this chapter, we’ll explore how PostgreSQL models access, how to design roles that match real-world responsibilities, and how to apply the principle of least privilege in a system like StreamFlow.

To follow along, insert this data into your StreamFlow database:

The payments table is the one we will treat as sensitive throughout the chapter. The rule of thumb is simple: anyone who does not strictly need to see payment amounts should not be able to see them.

If you have used older database tutorials, you have probably seen the word "user" everywhere. A user is an account you log in with. You create it, give it a password, and grant it some privileges. That model works, but it does not scale well once you have dozens of accounts and hundreds of tables.

PostgreSQL solves this by unifying users and groups into a single concept called a role. A role is any named identity that can own database objects and receive privileges. A role may or may not have a password. A role may or may not be able to log in. If it can log in, we colloquially call it a user. If it cannot log in, we colloquially call it a group. Under the hood, they are the same thing.

This matters because it changes how you think about access control. Instead of granting privileges directly to every login account, you grant privileges to a role that represents a job function (say, "read-only analyst") and then make login accounts members of that role. When a new analyst joins the team, you create their login, add them to the streamflow_analyst role, and you are done. You never have to touch individual GRANT statements again.

The login accounts on the left are the people and services. The group roles in the middle represent job functions. The privileges attach to the group roles, not to the logins. Each login inherits whatever privileges its group roles have.

In PostgreSQL, CREATE USER and CREATE ROLE are almost the same command. The difference is that CREATE USER implies LOGIN by default, while CREATE ROLE does not.

You can change a role later with ALTER ROLE:

And clean up with DROP ROLE:

One gotcha: you cannot drop a role that still owns objects or has privileges granted on objects. PostgreSQL will refuse until you reassign ownership and revoke the privileges. The helper command REASSIGN OWNED BY alice TO postgres; DROP OWNED BY alice; DROP ROLE alice; handles the full teardown.

MySQL and SQL Server distinguish users and roles more strictly. In MySQL, CREATE USER 'alice'@'localhost' creates a login account, and CREATE ROLE 'analyst' creates a group you then assign with GRANT 'analyst' TO 'alice'@'localhost'. SQL Server has server-level logins and database-level users, with database roles on top. The mental model is the same across all three: separate the identity from the job function.

Once you have roles, you grant privileges to them. The GRANT command takes three things: what kind of action, on which object, to which role.

The privileges you will see most often on tables are:

On schemas you have USAGE (can reference objects in the schema) and CREATE (can create new objects in it). On functions you have EXECUTE. On sequences you have USAGE, SELECT, and UPDATE.

A role also needs USAGE on the schema that contains the table before it can access that table. This trips up many people who grant SELECT on a table and then get a permission error because the caller never got USAGE on the schema.

Granting on Many Objects at Once

Listing every table is tedious. PostgreSQL gives you two shortcuts.

First, ALL TABLES IN SCHEMA applies the grant to everything currently in the schema:

This only affects existing tables. If you create a new table tomorrow, the analyst will not have access to it. To fix that, you set DEFAULT PRIVILEGES for future objects:

Now any new table created in public by the current role (or by whichever role you specify with FOR ROLE) automatically gets SELECT granted to streamflow_analyst. Combining the two gives you coverage for both existing and future objects.

Column-Level Grants

You can narrow a grant to specific columns, which is useful when a table has a mix of safe and sensitive columns:

Now streamflow_analyst can run SELECT user_id, username, country FROM users but a query that touches email or any other column will fail. We will explore column-level security in more detail in the next chapter.

REVOKE is the inverse of GRANT:

Easy enough. Where it gets interesting is when a grant was made with WITH GRANT OPTION:

Now streamflow_app can itself grant SELECT on tracks to other roles. If you later revoke SELECT from streamflow_app, PostgreSQL will either cascade the revoke to anyone streamflow_app had granted it to (with CASCADE) or refuse entirely if the dependent grants exist (the default, RESTRICT).

For production systems you almost never want WITH GRANT OPTION. It makes the permission graph hard to reason about because privileges can be propagated by accounts you did not expect to be handing them out.

This is the part that makes roles powerful. A role can be a member of another role, and when it is, it inherits the parent's privileges. That is what lets you build a hierarchy.

Alice logs in and inherits SELECT on every table in public through streamflow_readonly. She does not have any directly granted privileges. If you later decide analysts should also see a new reports schema, you grant once to streamflow_readonly and every analyst picks it up automatically.

By default, a role inherits the privileges of roles it is a member of. You can flip this with NOINHERIT, which requires the user to call SET ROLE explicitly before they can use the parent's privileges. NOINHERIT is useful for admin-style accounts where you want permissions to be opt-in per session, but for most application roles the default INHERIT behavior is what you want.

Role Membership vs Direct Grants

There are two ways to give alice SELECT on tracks:

Direct grants are simpler for one-off cases but become unmanageable at scale. Role membership is the right long-term approach because it keeps the permission graph shallow: privileges attach to a handful of functional roles, and logins are just attached to the right roles.

A common interview mistake is mixing the two styles haphazardly. Pick a convention (roles own privileges, logins are role members) and stick to it.

The phrase "least privilege" gets thrown around a lot, and it means exactly what it sounds like: every account should have the minimum set of privileges it needs to do its job, and nothing more. If the analytics team only needs to read four tables, they should not have SELECT on the whole database. If the backend service only inserts into streams, it should not have DELETE anywhere.

Why does this matter so much? Two reasons.

First, mistakes. A developer running DELETE FROM users WHERE email LIKE '%test%' in a staging-looking terminal that turns out to be prod has just made a career-limiting error. If their account did not have DELETE on users in the first place, the mistake would have been a permission error, not an incident.

Second, breaches. If an attacker steals the backend service's database credentials (through a leaked config, a compromised server, or SQL injection), the blast radius is exactly the privileges that account had. A service with ALL PRIVILEGES on everything hands over the entire database. A service with SELECT, INSERT, UPDATE on three tables hands over three tables worth of damage.

The rule of thumb: every role should answer two questions clearly. "What does this role exist to do?" and "What is the smallest set of privileges required to do that?" If the answer to the first question is "I don't know, it's for everything," the role is too broad.

Application Users Should Not Be Superusers

This one needs to be said out loud because it is the single most common mistake. Applications connect to the database as some role. In hobby projects and quickstart guides, that role is often the same superuser that installed PostgreSQL. Never do this in production.

A superuser bypasses every permission check, owns every object, and can execute any function. If your application accidentally runs a query like DROP TABLE users, a superuser connection will cheerfully drop the users table. A least-privileged application role will fail with a permission error, which is exactly what you want.

The safe pattern: create a role specifically for the application, grant it only the object privileges it needs, and point your connection string at that role. The superuser account is used only for migrations, backups, and emergency repairs.

Let us put all of this together. StreamFlow has three kinds of actors that need database access:

Here is a role design that fits:

Now we attach login accounts:

Verifying the Design

The worst thing you can do with permission design is write the GRANTs and assume they work. Always verify from the role's perspective by switching to it.

SET ROLE is your testing harness. It changes the current role for the rest of the session without needing a new connection. When you think a permission setup is right, you run through the cases as the target role and watch which ones succeed and which ones fail.

One pattern worth calling out: always separate the read-only reporting role from the read-write application role, even if the same people use both at different times. The reason is risk. A report that is supposed to be read-only can be run safely in a read-only session. The same report run through an account that also has DELETE is one typo away from catastrophe.

BI tools like Looker, Metabase, and Tableau should always connect through the read-only role. That way a misconfigured dashboard cannot UPDATE anything. The backend service connects through the application role. Only the migration pipeline and the DBA ever touch the admin role.