Schema-level access control in PostgreSQL - Step-by-Step Execution

Concept Flow - Schema-level access control

Start

↓

Create Schema

↓

Grant Privileges to Role

↓

Role Tries to Access Schema

↓

Access Allowed?

No→Access Denied

↓

Access Granted

↓

End

This flow shows creating a schema, granting access rights to a role, and then checking if the role can access the schema.

Execution Sample

PostgreSQL

CREATE SCHEMA sales;
GRANT USAGE, CREATE ON SCHEMA sales TO analyst;
-- analyst tries to create table
SET ROLE analyst;
CREATE TABLE sales.reports(id INT);

This code creates a schema, grants usage and create rights to a role, then the role tries to create a table inside the schema.

Execution Table

Step	Action	Command/Check	Result	Notes
1	Create schema	CREATE SCHEMA sales;	Schema 'sales' created	Schema is now available
2	Grant usage privilege	GRANT USAGE, CREATE ON SCHEMA sales TO analyst;	Privileges granted	Role 'analyst' can now access schema and create objects
3	Switch role	SET ROLE analyst;	Role changed to 'analyst'	Subsequent commands run as 'analyst'
4	Create table in schema	CREATE TABLE sales.reports(id INT);	Table created	Access allowed because 'analyst' has USAGE and CREATE on schema
5	Try to create table without privilege	SET ROLE other_user; CREATE TABLE sales.test(id INT);	Error: permission denied	No privileges for 'other_user' on schema
6	Exit	N/A	Execution ends	No further commands

💡 Execution stops after permission denied error or successful table creation

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	Final
Current Schema	public	sales created	sales created	sales created	sales created	sales created	sales created
Current Role	default	default	default	analyst	analyst	other_user	other_user
Analyst Privilege on sales	none	none	USAGE, CREATE granted	USAGE, CREATE granted	USAGE, CREATE granted	USAGE, CREATE granted	USAGE, CREATE granted
Other_user Privilege on sales	none	none	none	none	none	none	none
Table sales.reports	none	none	none	none	created	created	created

Key Moments - 3 Insights

Why can the 'analyst' role create a table in the 'sales' schema after being granted privileges?

Because USAGE allows access to objects inside the schema and CREATE allows creating new objects, as shown in execution_table row 4.

Why does 'other_user' get a permission denied error when trying to create a table in the 'sales' schema?

Because 'other_user' was never granted USAGE privilege on the 'sales' schema, so access is denied as shown in execution_table row 5.

What does the SET ROLE command do in this context?

It changes the current user context to the specified role, so subsequent commands run with that role's privileges, as seen in execution_table rows 3 and 5.

Visual Quiz - 3 Questions

Look at the execution_table, what is the current role after step 3?

A other_user

B default

C analyst

D sales

💡 Hint

Check the 'Current Role' variable in variable_tracker after step 3

At which step does the 'analyst' role gain USAGE privilege on the 'sales' schema?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look at the 'Analyst Privilege on sales' variable in variable_tracker after each step

If 'other_user' was granted the same privileges on 'sales' schema as analyst, what would happen at step 5?

A Permission denied error

B Table creation succeeds

C Role changes to analyst

D Schema is dropped

💡 Hint

Refer to execution_table row 5 and consider the effect of granting the same privileges

Concept Snapshot

Schema-level access control in PostgreSQL:
- CREATE SCHEMA creates a new schema.
- GRANT USAGE, CREATE ON SCHEMA allows a role to access objects and create new objects inside the schema.
- SET ROLE switches the current user context.
- Without these privileges, roles cannot access schema objects.
- Access control protects schema contents from unauthorized use.

Full Transcript

This visual execution trace shows how schema-level access control works in PostgreSQL. First, a schema named 'sales' is created. Then, the USAGE and CREATE privileges on this schema are granted to the role 'analyst'. When the session switches to the 'analyst' role using SET ROLE, the role can create a table inside the 'sales' schema because it has the necessary privileges. Another role, 'other_user', without privileges, gets a permission denied error when trying to create a table in the same schema. The trace tracks the current schema, current role, privileges, and table creation status step-by-step. Key moments clarify why privileges matter and how role switching affects access. The quiz tests understanding of role changes, privilege grants, and access outcomes. The snapshot summarizes the key commands and rules for schema-level access control.