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.

Practice

1. Which of the following best describes a login role in PostgreSQL?

easy

A. A role used only to group other roles without login capability.

B. A role that can connect to the database and perform actions.

C. A temporary session role that disappears after logout.

D. A role that automatically grants all permissions to users.

Solution

1. Step 1: Understand the purpose of login roles

   Login roles are created to allow users to connect to the database and perform tasks.

2. Step 2: Differentiate from group roles

   Group roles are for organizing users and sharing permissions but cannot login themselves.

3. Final Answer:

   A role that can connect to the database and perform actions. -> Option B

4. Quick Check:

   Login role = can connect [OK]

Hint: Login roles can connect; group roles cannot [OK]

Common Mistakes:

• Confusing group roles with login roles
• Thinking group roles can login
• Assuming login roles have no permissions

2. Which SQL command correctly creates a group role named managers without login capability?

easy

A. CREATE ROLE managers NOLOGIN;

B. CREATE ROLE managers LOGIN;

C. CREATE USER managers;

D. CREATE ROLE managers WITH LOGIN;

Solution

1. Step 1: Recall syntax for creating roles without login

   To create a group role, use CREATE ROLE with NOLOGIN option.

2. Step 2: Analyze options

   CREATE ROLE managers NOLOGIN; uses NOLOGIN correctly; options B and D enable login; CREATE USER managers; creates a login user.

3. Final Answer:

   CREATE ROLE managers NOLOGIN; -> Option A

4. Quick Check:

   Group role = NOLOGIN [OK]

Hint: Use NOLOGIN to create group roles [OK]

Common Mistakes:

• Using LOGIN when creating group roles
• Using CREATE USER instead of CREATE ROLE
• Omitting NOLOGIN for group roles

3. Given the following commands:

CREATE ROLE analysts NOLOGIN;
CREATE ROLE alice LOGIN PASSWORD 'secret';
GRANT analysts TO alice;

What is true about alice after these commands?

medium

A. Alice can login and has permissions of analysts role.

B. Alice cannot login because analysts role has NOLOGIN.

C. Alice can login but does not inherit analysts permissions.

D. Alice is a group role and cannot login.

Solution

1. Step 1: Understand role creation and grants

   Alice is a login role with password; analysts is a group role without login.

2. Step 2: Check role membership effect

   Granting analysts to alice means alice inherits analysts permissions and can login.

3. Final Answer:

   Alice can login and has permissions of analysts role. -> Option A

4. Quick Check:

   Login role + granted group role = login + permissions [OK]

Hint: Login roles inherit group role permissions when granted [OK]

Common Mistakes:

• Thinking NOLOGIN group role blocks login
• Assuming permissions are not inherited
• Confusing login and group roles

4. Identify the error in this SQL snippet:

CREATE ROLE developers;
CREATE ROLE bob LOGIN;
GRANT developers TO bob NOLOGIN;

medium

A. CREATE ROLE bob LOGIN is invalid syntax.

B. CREATE ROLE developers must include LOGIN.

C. Bob cannot be granted a role.

D. The GRANT statement incorrectly uses NOLOGIN.

Solution

1. Step 1: Review GRANT syntax

   GRANT role TO user does not accept NOLOGIN; NOLOGIN is for CREATE ROLE only.

2. Step 2: Check other statements

   CREATE ROLE developers is valid as group role; CREATE ROLE bob LOGIN is valid.

3. Final Answer:

   The GRANT statement incorrectly uses NOLOGIN. -> Option D

4. Quick Check:

   NOLOGIN only in CREATE ROLE, not GRANT [OK]

Hint: NOLOGIN is for CREATE ROLE, not GRANT [OK]

Common Mistakes:

• Adding NOLOGIN in GRANT statement
• Confusing role creation and granting syntax
• Assuming all roles must have LOGIN

5. You want to create a setup where multiple users share the same permissions easily. Which approach is best in PostgreSQL?

hard

A. Create only group roles and let users login as group roles.

B. Create multiple login roles with identical permissions separately.

C. Create individual login roles and grant them a common group role.

D. Create login roles and assign permissions directly to each.

Solution

1. Step 1: Understand permission management

   Group roles allow sharing permissions easily among many users.

2. Step 2: Evaluate options

   Create individual login roles and grant them a common group role. uses group roles granted to login roles, best for easy permission management.

3. Step 3: Reject incorrect options

   A is invalid as group roles cannot login; B duplicates permissions; D is less efficient.

4. Final Answer:

   Create individual login roles and grant them a common group role. -> Option C

5. Quick Check:

   Group roles + login roles = efficient permission sharing [OK]

Hint: Use group roles to share permissions among login roles [OK]

Common Mistakes:

• Trying to login as group roles
• Assigning permissions individually to each user
• Duplicating permissions instead of grouping