GRANT and REVOKE permissions in PostgreSQL - Step-by-Step Execution

Concept Flow - GRANT and REVOKE permissions

Start

↓

GRANT permission to user

↓

User can perform action

↓

REVOKE permission from user

↓

User can no longer perform action

↓

End

This flow shows how permissions are given to a user with GRANT, allowing actions, and then taken away with REVOKE, stopping those actions.

Execution Sample

PostgreSQL

GRANT SELECT ON employees TO alice;
-- Alice can now read the employees table
REVOKE SELECT ON employees FROM alice;
-- Alice can no longer read the employees table

This code first gives Alice permission to read the employees table, then removes that permission.

Execution Table

Step	Command	Permission State for alice	Effect on alice's actions
1	Initial state	No permissions	Cannot read employees table
2	GRANT SELECT ON employees TO alice;	SELECT permission granted	Can read employees table
3	REVOKE SELECT ON employees FROM alice;	SELECT permission revoked	Cannot read employees table anymore

💡 Permissions revoked, alice cannot read employees table

Variable Tracker

Permission	Start	After GRANT	After REVOKE
SELECT on employees	No	Yes	No

Key Moments - 3 Insights

Why can't Alice read the employees table before the GRANT command?

Because initially Alice has no SELECT permission on the employees table, as shown in execution_table step 1.

Does REVOKE remove all permissions or just the specified one?

REVOKE only removes the specified permission, here SELECT on employees, as shown in execution_table step 3.

If we GRANT SELECT again after REVOKE, what happens?

Alice will regain the ability to read the employees table, similar to the state after step 2.

Visual Quiz - 3 Questions

Look at the execution_table, what is Alice's permission state after step 2?

A No permissions

B SELECT permission granted

C SELECT permission revoked

D All permissions granted

💡 Hint

Check the 'Permission State for alice' column in execution_table row 2

At which step does Alice lose the ability to read the employees table?

A Step 3

B Step 1

C Step 2

D Never

💡 Hint

Look at the 'Effect on alice's actions' column in execution_table

If we skip the REVOKE command, what will be Alice's permission after step 3?

A No permissions

B INSERT permission granted

C SELECT permission granted

D All permissions revoked

💡 Hint

Refer to variable_tracker and execution_table before REVOKE

Concept Snapshot

GRANT permission ON object TO user;
- Gives user rights to perform actions (e.g., SELECT)
REVOKE permission ON object FROM user;
- Removes those rights
Permissions control what users can do with database objects.

Full Transcript

This visual execution shows how PostgreSQL manages permissions using GRANT and REVOKE commands. Initially, Alice has no permission to read the employees table. When we run GRANT SELECT ON employees TO alice;, Alice gains the right to read the table. Later, REVOKE SELECT ON employees FROM alice; removes that right. The execution table tracks these changes step-by-step, and the variable tracker shows the permission state changes. Key moments clarify common confusions about when permissions apply and how REVOKE works. The quiz tests understanding by asking about permission states at different steps. The snapshot summarizes the syntax and purpose of GRANT and REVOKE.