Login vs group roles in PostgreSQL - Visual Side-by-Side Comparison

Concept Flow - Login vs group roles

Create Login Role

↓

Create Group Role

↓

Assign Login Role to Group Role

↓

Login Role inherits Group Role permissions

↓

Login Role can connect and act with combined permissions

↓

Login Role can be granted direct permissions too

↓

Group Role manages shared permissions for multiple logins

This flow shows how login roles and group roles are created, linked, and how permissions are inherited in PostgreSQL.

Execution Sample

PostgreSQL

CREATE ROLE alice LOGIN PASSWORD 'pass';
CREATE ROLE developers NOLOGIN;
GRANT developers TO alice;

Create a login role 'alice', a group role 'developers', then assign 'alice' to 'developers' to inherit permissions.

Execution Table

Step	Action	Object	Result	Notes
1	Create login role	alice	Role 'alice' created with LOGIN privilege	alice can connect to DB
2	Create group role	developers	Role 'developers' created without LOGIN	Group role for permission grouping
3	Grant group role to login role	developers -> alice	'alice' inherits 'developers' permissions	alice gains permissions of developers
4	Check alice permissions	alice	Has LOGIN and inherits developers' permissions	alice can connect and act as developer
5	Exit	-	No more steps	Process complete

💡 All roles created and linked; login role inherits group role permissions

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
alice LOGIN	false	true	true	true	true
developers LOGIN	false	false	false	false	false
alice inherits developers	false	false	false	true	true

Key Moments - 3 Insights

Why does 'developers' role have NOLOGIN?

'developers' is a group role meant only to hold permissions for others; it cannot login itself. See execution_table step 2.

How does 'alice' get permissions from 'developers'?

By granting 'developers' to 'alice', 'alice' inherits all permissions of 'developers'. See execution_table step 3.

Can 'alice' login after being assigned to 'developers'?

Yes, because 'alice' has LOGIN privilege directly. Group roles do not affect login ability. See execution_table step 4.

Visual Quiz - 3 Questions

Look at the execution_table, after which step does 'alice' inherit 'developers' permissions?

A After Step 1

B After Step 2

C After Step 3

D After Step 4

💡 Hint

Check the 'alice inherits developers' column in variable_tracker after each step

According to variable_tracker, what is the LOGIN status of 'developers' role at the end?

A true

B false

C inherits from alice

D unknown

💡 Hint

Look at the 'developers LOGIN' row in variable_tracker final column

If 'developers' role had LOGIN privilege, what would change in the execution flow?

A 'developers' could login directly

B Nothing changes

C 'alice' loses LOGIN privilege

D 'alice' cannot inherit permissions

💡 Hint

Consider the meaning of LOGIN privilege in execution_table step 2 and 4

Concept Snapshot

PostgreSQL roles can be LOGIN or NOLOGIN.
Login roles connect to the database.
Group roles (NOLOGIN) hold shared permissions.
Grant group roles to login roles to inherit permissions.
Login roles keep their own LOGIN ability.
Group roles simplify permission management.

Full Transcript

In PostgreSQL, a login role is a user that can connect to the database. A group role is a role without login ability used to group permissions. First, we create a login role named 'alice' with LOGIN privilege. Then, we create a group role 'developers' without LOGIN. Next, we grant 'developers' to 'alice', so 'alice' inherits all permissions of 'developers'. 'alice' can still login because she has LOGIN privilege directly. Group roles help manage permissions for multiple users easily. This process shows how login and group roles work together to control access and permissions in PostgreSQL.