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.

Practice

1. What does the USAGE privilege on a schema in PostgreSQL allow a user to do?

easy

A. Access objects within the schema without creating new ones

B. Create new tables and objects inside the schema

C. Delete the schema entirely

D. Modify data in tables outside the schema

Solution

1. Step 1: Understand USAGE privilege meaning

   The USAGE privilege allows a user to access objects inside the schema, such as selecting data from tables, but does not allow creating new objects.

2. Step 2: Differentiate from CREATE privilege

   The CREATE privilege is needed to add new tables or other objects. USAGE alone does not grant this ability.

3. Final Answer:

   Access objects within the schema without creating new ones -> Option A

4. Quick Check:

   USAGE = access only [OK]

Hint: USAGE lets you use, CREATE lets you add [OK]

Common Mistakes:

• Confusing USAGE with CREATE privilege
• Thinking USAGE allows schema deletion
• Assuming USAGE grants data modification outside schema

2. Which of the following is the correct syntax to grant CREATE privilege on a schema named sales to user alice?

easy

A. GRANT CREATE ON sales TO alice;

B. GRANT CREATE TO alice ON SCHEMA sales;

C. GRANT CREATE ON SCHEMA sales TO alice;

D. GRANT CREATE ON DATABASE sales TO alice;

Solution

1. Step 1: Identify correct GRANT syntax for schema

   In PostgreSQL, to grant privileges on a schema, the syntax is: GRANT privilege ON SCHEMA schema_name TO user;

2. Step 2: Match syntax with options

   GRANT CREATE ON SCHEMA sales TO alice; matches this syntax exactly: GRANT CREATE ON SCHEMA sales TO alice;

3. Final Answer:

   GRANT CREATE ON SCHEMA sales TO alice; -> Option C

4. Quick Check:

   GRANT ... ON SCHEMA ... TO ... [OK]

Hint: Use 'ON SCHEMA' when granting schema privileges [OK]

Common Mistakes:

• Omitting 'SCHEMA' keyword
• Using 'ON DATABASE' instead of 'ON SCHEMA'
• Placing TO clause incorrectly

3. Given the commands below, what will be the result of SELECT * FROM sales.orders; when run by user bob?

GRANT USAGE ON SCHEMA sales TO bob;
REVOKE CREATE ON SCHEMA sales FROM bob;

medium

A. Query runs but returns no rows

B. Error: permission denied for schema sales

C. Error: relation sales.orders does not exist

D. Query runs successfully and returns rows from sales.orders

Solution

1. Step 1: Analyze granted privileges

   User bob has USAGE on schema sales, so can access objects inside it. CREATE privilege is revoked, so bob cannot create new objects but can read existing ones.

2. Step 2: Understand effect on SELECT query

   Since bob has USAGE, SELECT on sales.orders will work if bob has SELECT privilege on the table (assumed). The REVOKE of CREATE does not affect SELECT.

3. Final Answer:

   Query runs successfully and returns rows from sales.orders -> Option D

4. Quick Check:

   USAGE allows access, REVOKE CREATE blocks creation only [OK]

Hint: USAGE lets you read; CREATE controls adding objects [OK]

Common Mistakes:

• Confusing CREATE with SELECT privilege
• Assuming REVOKE CREATE blocks all access
• Ignoring USAGE privilege effect

4. You want to allow user carol to create tables in schema inventory, but she gets an error: permission denied for schema inventory. Which command fixes this?

medium

A. GRANT CREATE ON SCHEMA inventory TO carol;

B. GRANT USAGE ON SCHEMA inventory TO carol;

C. GRANT ALL PRIVILEGES ON SCHEMA inventory TO carol;

D. REVOKE USAGE ON SCHEMA inventory FROM carol;

Solution

1. Step 1: Understand error cause

   To create tables, user needs both USAGE and CREATE privileges on the schema. Without USAGE, permission denied error occurs.

2. Step 2: Grant missing privilege

   Granting USAGE on schema inventory to carol allows her to access the schema and create tables if CREATE is already granted.

3. Final Answer:

   GRANT USAGE ON SCHEMA inventory TO carol; -> Option B

4. Quick Check:

   USAGE needed before CREATE works [OK]

Hint: Grant USAGE before CREATE to avoid permission errors [OK]

Common Mistakes:

• Granting CREATE without USAGE privilege
• Revoking instead of granting privileges
• Assuming ALL PRIVILEGES always needed

5. You want to restrict user dave so he can only create objects in schema projects but cannot access any existing objects. Which combination of privileges achieves this?

hard

A. GRANT CREATE ON SCHEMA projects TO dave; REVOKE USAGE ON SCHEMA projects FROM dave;

B. GRANT USAGE ON SCHEMA projects TO dave; REVOKE CREATE ON SCHEMA projects FROM dave;

C. GRANT ALL ON SCHEMA projects TO dave;

D. REVOKE ALL ON SCHEMA projects FROM dave;

Solution

1. Step 1: Understand privilege effects

   CREATE allows adding new objects. USAGE allows accessing existing objects. To restrict access but allow creation, grant CREATE and revoke USAGE.

2. Step 2: Apply correct commands

   GRANT CREATE ON SCHEMA projects TO dave; REVOKE USAGE ON SCHEMA projects FROM dave; grants CREATE and revokes USAGE, so dave can create but not access existing objects.

3. Final Answer:

   GRANT CREATE ON SCHEMA projects TO dave; REVOKE USAGE ON SCHEMA projects FROM dave; -> Option A

4. Quick Check:

   Create without usage blocks access [OK]

Hint: Grant CREATE, revoke USAGE to allow creation only [OK]

Common Mistakes:

• Granting USAGE allows access to existing objects
• Revoking CREATE disables creation
• Granting ALL gives too many rights