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.