Right, let’s get this sorted because frankly, the way PostgreSQL handles users and roles is simultaneously brilliant and a bit of a head-scratcher. You’re going to hear a phrase a lot: “In PostgreSQL, there is no distinction between users and roles.” This is technically true, but it’s also a massive oversimplification that will get you into trouble if you don’t understand the practical distinction that everyone uses.

Here’s the deal: CREATE USER and CREATE ROLE are, behind the curtain, the exact same command. I’m not kidding. CREATE USER is literally an alias for CREATE ROLE with one tiny, default difference. The only thing CREATE USER does by default that a plain CREATE ROLE doesn’t is give the new entity the LOGIN privilege. That’s it. That’s the whole secret.

Think of a “Role” as a container for permissions. It can represent a person, a group of people, or even an application. A “User,” in practical PostgreSQL parlance, is just a role that has been granted the ability to log in. It’s a role with the LOGIN attribute. This is why you’ll see me use the term “role” for everything—it’s the more accurate, fundamental concept.

The NOLOGIN By Default Trap

This is the first and most common pitfall. Let’s say you’re being a good, security-conscious admin. You read that you should have roles for groups of permissions. So you run:

CREATE ROLE read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

Perfect. Now you create a user who should have that access:

CREATE USER bob WITH PASSWORD 'supersecret';

Bob tries to connect… and he can’t. You get a fatal error. Why? Because you never granted the read_only role to Bob! The read_only role is just a floating bucket of permissions with no one to hold it. You have to explicitly do:

GRANT read_only TO bob;

Now Bob can connect and he inherits the SELECT permission from the read_only role. This separation is a feature, not a bug. It lets you build complex permission structures without tying them to individual users, making your life infinitely easier when Bob gets promoted and Carol takes his place.

Inheritance and the SET ROLE Power Move

By default, when a user logs in, they have all the privileges of every role they’ve been granted, plus their own. This is because the INHERIT attribute is on by default for both CREATE USER and CREATE ROLE. This is almost always what you want.

But sometimes, you need to temporarily “become” another role, especially if you’re an admin. This is where SET ROLE comes in. Let’s say you’re logged in as bob, but you need to do something that requires the powers of the read_write role that you also belong to. You can’t do it as Bob because his login role doesn’t have that power directly. So you run:

SET ROLE read_write;

Now, for this session, you are effectively the read_write role. Your original role (bob) is hidden. This is incredibly useful for testing permissions: “If I were this service account, could I actually run this query?” To go back to being your original self, you just run:

RESET ROLE;

Do not confuse this with SET SESSION AUTHORIZATION, which is a much more powerful superuser-only command that lets you actually become another user. You probably don’t need that.

The PUBLIC Role is Not Your Friend

This is the biggest security foot-gun in all of PostgreSQL, and it’s enabled by default. PUBLIC is a special role that every single role in the system is automatically a member of. Any privilege you grant to PUBLIC is granted to everyone. Everyone. Past, present, and future.

Never, ever do this on a production system:

-- NEVER DO THIS. SERIOUSLY.
GRANT ALL ON SCHEMA public TO PUBLIC;
GRANT ALL ON ALL TABLES IN SCHEMA public TO PUBLIC;

The default public schema already has some permissions granted to PUBLIC, which is why new users can create tables there. This is convenient for tutorials and absolutely bonkers for anything else. Your first act on a new database should be to revoke these privileges and lock it down.

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Now, only users you explicitly grant CREATE privilege to can make tables in the public schema. You should also consider using custom schemas for your application data and being very deliberate about what, if anything, you grant to the PUBLIC role. Assume anything granted to PUBLIC is world-readable.

Best Practice: A Role-Based Hierarchy

The power of this system is building a logical hierarchy. A well-designed system looks less like a flat list of users and more like an org chart.

  1. Group Roles: Create roles with NOLOGIN for permission groups (read_only, read_write, app_user, analyst).
  2. Grant Permissions: Grant specific privileges (SELECT, INSERT, etc.) to these group roles, not to individual users.
  3. Service Accounts: Create roles with LOGIN for applications (my_app_prod, reporting_service). These are your “users.”
  4. Human Users: Create roles with LOGIN for people (alice, bob).
  5. Associate: Grant the group roles to the login roles. GRANT read_write TO my_app_prod; GRANT analyst TO alice;

This way, when a permission needs to change, you change it in one place (the group role) and it instantly propagates to everyone who has that role. When Bob leaves, you just run DROP ROLE bob; and all his permissions are cleanly revoked. No messy digging through individual grants. It’s clean, scalable, and sane. Just the way we like it.