Role creation and management in PostgreSQL - Step-by-Step Execution

Concept Flow - Role creation and management

Start

↓

Create Role

↓

Assign Privileges

↓

Grant Role to User

↓

Verify Role and Privileges

↓

Modify or Drop Role

↓

End

This flow shows how to create a role, assign privileges, grant it to users, verify, and modify or drop the role.

Execution Sample

PostgreSQL

CREATE ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;
GRANT analyst TO alice;
\du

This code creates a role 'analyst', grants it select access on all tables in public schema, assigns it to user 'alice', and lists roles.

Execution Table

Step	Command	Effect	Result/Output
1	CREATE ROLE analyst;	Creates a new role named 'analyst'	Role 'analyst' created
2	GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;	Gives 'analyst' select rights on all public tables	Privileges granted to 'analyst'
3	GRANT analyst TO alice;	Assigns role 'analyst' to user 'alice'	Role 'analyst' granted to 'alice'
4	\du	Lists all roles and their attributes	List shows 'analyst' role and 'alice' with 'analyst' role assigned
5	REVOKE analyst FROM alice; DROP ROLE analyst;	Revokes role from alice then deletes the role 'analyst'	Role 'analyst' dropped
6	DROP ROLE analyst;	Fails if role does not exist	ERROR: role "analyst" does not exist

💡 Execution stops after role is dropped or error if role missing

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 5
Roles	none	analyst created	analyst with SELECT privilege	analyst assigned to alice	analyst dropped

Key Moments - 2 Insights

Why does the GRANT analyst TO alice; command not give alice any privileges directly?

Because 'analyst' is a role that holds privileges; alice gets those privileges by having the role assigned, not directly.

What happens if you try to DROP a role that is still assigned to a user?

PostgreSQL will prevent dropping the role until it is revoked from all users.

Visual Quiz - 3 Questions

Look at the execution table, what is the effect of step 2?

A Grants SELECT privileges on all public tables to role 'analyst'

B Creates a new user named 'analyst'

C Assigns role 'analyst' to user 'alice'

D Deletes the role 'analyst'

💡 Hint

Check the 'Effect' column in step 2 of the execution table

At which step does user 'alice' get the 'analyst' role assigned?

A Step 1

B Step 2

C Step 3

D Step 5

💡 Hint

Look at the 'Command' and 'Effect' columns for step 3

If you try to drop a role that does not exist, what is the result?

A Role is created automatically

B An error is shown

C Role is dropped silently

D Privileges are revoked

💡 Hint

See the 'Result/Output' column in step 6

Concept Snapshot

CREATE ROLE role_name; -- makes a new role
GRANT privileges TO role_name; -- gives rights to role
GRANT role_name TO user; -- assigns role to user
DROP ROLE role_name; -- deletes role if unused
Roles group privileges for easy management.

Full Transcript

This visual execution shows how to create and manage roles in PostgreSQL. First, a role is created with CREATE ROLE. Then privileges like SELECT on tables are granted to the role. Next, the role is assigned to a user with GRANT role TO user. The \du command lists roles and their assignments. Finally, roles can be dropped if no longer needed. Trying to drop a role still assigned to users causes an error. This step-by-step trace helps beginners see how roles group privileges and are assigned to users for easier permission management.

Practice

1. What is the primary purpose of a role in PostgreSQL?

easy

A. To control access and permissions for users and groups

B. To store data in tables

C. To create backups of the database

D. To optimize query performance

Solution

1. Step 1: Understand the concept of roles

   Roles in PostgreSQL are used to manage who can access the database and what they can do.

2. Step 2: Identify the main function of roles

   Roles control permissions and access rights, not data storage or backups.

3. Final Answer:

   To control access and permissions for users and groups -> Option A

4. Quick Check:

   Roles = Access control [OK]

Hint: Roles manage user permissions and access rights [OK]

Common Mistakes:

• Confusing roles with tables or data storage
• Thinking roles handle backups
• Assuming roles optimize queries

2. Which of the following is the correct syntax to create a role with login permission in PostgreSQL?

easy

A. CREATE ROLE user1 WITH LOGIN;

B. CREATE ROLE user1 CAN LOGIN;

C. CREATE ROLE user1 LOGIN;

D. CREATE ROLE user1 ALLOW LOGIN;

Solution

1. Step 1: Recall the syntax for creating a role with login

   The correct syntax uses WITH LOGIN to allow the role to log in.

2. Step 2: Check each option

   CREATE ROLE user1 WITH LOGIN; uses 'WITH LOGIN' which is correct. Others use incorrect keywords.

3. Final Answer:

   CREATE ROLE user1 WITH LOGIN; -> Option A

4. Quick Check:

   WITH LOGIN = enable login [OK]

Hint: Use WITH LOGIN to grant login rights when creating roles [OK]

Common Mistakes:

• Omitting WITH before LOGIN
• Using CAN or ALLOW instead of WITH
• Forgetting semicolon at end

3. Given the commands:

CREATE ROLE analyst NOLOGIN;
ALTER ROLE analyst CREATEDB;

What is true about the role analyst?

medium

A. The role can log in but cannot create databases

B. The role can log in and create databases

C. The role cannot log in but can create databases

D. The role cannot log in and cannot create databases

Solution

1. Step 1: Analyze the CREATE ROLE command

   The role 'analyst' is created with NOLOGIN, so it cannot log in.

2. Step 2: Analyze the ALTER ROLE command

   The role is altered to have CREATEDB permission, so it can create databases.

3. Final Answer:

   The role cannot log in but can create databases -> Option C

4. Quick Check:

   NOLOGIN + CREATEDB = no login, can create DB [OK]

Hint: NOLOGIN disables login; CREATEDB allows database creation [OK]

Common Mistakes:

• Assuming NOLOGIN means role cannot do anything
• Confusing CREATEDB with login permission
• Ignoring ALTER ROLE effects

4. Identify the error in the following command:

CREATE ROLE manager LOGIN PASSWORD 'secret';

medium

A. PASSWORD must be set using ENCRYPTED keyword

B. LOGIN cannot be used with PASSWORD

C. PASSWORD should be set with USING keyword

D. PASSWORD must be set using WITH keyword

Solution

1. Step 1: Check correct syntax for setting password

   In PostgreSQL, options like PASSWORD must be specified after WITH keyword.

2. Step 2: Identify the error in the command

   The command misses WITH before PASSWORD, causing syntax error.

3. Final Answer:

   PASSWORD must be set using WITH keyword -> Option D

4. Quick Check:

   Use WITH before PASSWORD [OK]

Hint: Use WITH before PASSWORD when creating roles [OK]

Common Mistakes:

• Omitting WITH before PASSWORD
• Thinking LOGIN disallows PASSWORD
• Using ENCRYPTED incorrectly

5. You want to create a role named developer that can log in, create databases, and also inherit permissions from another role team_member. Which command correctly achieves this?

hard

A. CREATE ROLE developer WITH LOGIN CREATEDB INHERIT team_member;

B. CREATE ROLE developer WITH LOGIN CREATEDB INHERIT; GRANT team_member TO developer;

C. CREATE ROLE developer WITH LOGIN CREATEDB INHERIT; ALTER ROLE developer IN ROLE team_member;

D. CREATE ROLE developer WITH LOGIN CREATEDB INHERIT; GRANT developer TO team_member;

Solution

1. Step 1: Create role with login, createdb, and inherit

   The role must be created with WITH LOGIN, CREATEDB, and INHERIT options.

2. Step 2: Grant membership to inherit permissions

   To inherit permissions from team_member, grant team_member role to developer using GRANT.

3. Step 3: Check each option

   CREATE ROLE developer WITH LOGIN CREATEDB INHERIT; GRANT team_member TO developer; correctly creates the role and grants team_member to developer. Others misuse syntax or reverse grant direction.

4. Final Answer:

   CREATE ROLE developer WITH LOGIN CREATEDB INHERIT; GRANT team_member TO developer; -> Option B

5. Quick Check:

   GRANT role TO user for inheritance [OK]

Hint: Use GRANT role TO user to inherit permissions [OK]

Common Mistakes:

• Putting role name after INHERIT
• Reversing GRANT direction
• Missing WITH keyword or semicolon