Right, let’s talk about the building blocks of your database’s social hierarchy: roles. Forget the clunky CREATE USER and CREATE GROUP commands you might see in older tutorials—they’re just aliases. Under the hood, PostgreSQL only has CREATE ROLE. A “user” is just a role with the LOGIN privilege, and a “group” is just a role without it. It’s a beautifully unified model, and once you get it, everything else clicks into place.

The basic incantation is simple, but the magic is in the attributes you slap onto it.

CREATE ROLE data_engineer;

Congratulations, you’ve just created a role that can do absolutely nothing. It’s the most secure, and also the most useless, role imaginable. It can’t even log in. This is your blank slate. The power comes from the optional keywords you add to the command, which are really just shortcuts for granting specific, powerful privileges.

The Big Three Attributes: LOGIN, SUPERUSER, CREATEDB

Let’s give our data_engineer a reason to exist.

CREATE ROLE jane_doe WITH LOGIN PASSWORD 'a_strong_password_please';
GRANT data_engineer TO jane_doe;

See what we did there? jane_doe is a login role (a “user”), and we’ve made her a member of the data_engineer group role. This is the classic pattern: create group roles with specific sets of permissions (data_engineer, read_only_analyst, billing_admin), then create login roles for actual humans and grant them the appropriate group roles. It’s clean, scalable, and makes permission changes a breeze.

Now, let’s talk about the big guns. These attributes are like handing out master keys—do it with extreme prejudice.

SUPERUSER is the absolute, unrestricted, root-level access. A superuser can do anything. They can read any table, terminate your connections, and even drop the entire database cluster. It bypasses every single permission check. You should have approximately one, maybe two, of these for your entire cluster, and they should be used for administration only, never for application connections. The sheer absurdity of connecting your web app with a superuser role should keep you up at night.

CREATEDB allows a role to, you guessed it, create databases. It’s a powerful attribute but scoped nicely. A role with CREATEDB can’t necessarily do anything inside a database it doesn’t own; it just gets to run the CREATE DATABASE command. This is perfect for your lead engineers or automation scripts that need to spin up new environments.

-- Create a powerful, but not all-powerful, admin role
CREATE ROLE cluster_admin WITH CREATEDB CREATEROLE REPLICATION BYPASSRLS LOGIN;
-- Notice we did NOT include SUPERUSER

The Fine Print: INHERIT, BYPASSRLS, and REPLICATION

The attributes don’t stop at the big three. Here are the subtler, often-misunderstood ones that will bite you if you ignore them.

INHERIT is the default behavior, and it’s crucial. When INHERit is set (which it is unless you use NOINHERIT), a role automatically has the privileges of all roles it’s a member of. This is almost always what you want. NOINHERIT is a weird edge case for when you want a role to be a member of another but not actually get its permissions—it’s like being on a team but refusing to use the team’s tools. I’ve literally never used it in production.

BYPASSRLS allows a role to bypass every Row Level Security (RLS) policy you’ve painstakingly set up on your tables. It’s a sledgehammer. If you have RLS, you probably have a few service roles (e.g., for batch jobs) that need to see all data to perform their function. That’s what this is for. Grant it carefully. A superuser automatically bypasses RLS, which is another reason you don’t hand that out.

REPLICATION is a special-purpose attribute needed for roles that are used for streaming replication or to use the pg_basebackup tool. It’s effectively a very powerful login privilege. Don’t grant this unless you know exactly why you need it.

The ALTER ROLE Reality

You will forget an attribute. You will need to change a password. The ALTER ROLE command is your friend.

-- Let's be more secure and make jane a superuser... just kidding.
ALTER ROLE jane_doe WITH SUPERUSER;
-- Actually, let's not. Let's just give her createdb.
ALTER ROLE jane_doe WITH CREATEDB NOSUPERUSER;

-- Force a password change on next login (a great practice for new users)
ALTER ROLE jane_doe WITH PASSWORD NULL VALID UNTIL '2024-01-01';

-- The most common command: resetting a forgotten password
ALTER ROLE jane_doe WITH PASSWORD 'new_strong_password';

The key takeaway? Start with the principle of least privilege. Create a login role that can’t do much, then grant it membership to more powerful group roles. Your default state should be “no,” and you consciously say “yes” to specific needs. It’s less exciting than playing with superuser powers, but it’s how you avoid becoming a cautionary tale.