Schema-level access control in PostgreSQL - Mini Project: Build & Apply

Schema-level Access Control in PostgreSQL

📖 Scenario: You are managing a PostgreSQL database for a small company. The company wants to organize its data into different schemas to separate departments. You need to set up schema-level access control so that only authorized users can access specific schemas.

🎯 Goal: Learn how to create schemas, create users, and grant schema-level permissions in PostgreSQL to control access.

📋 What You'll Learn

Create a schema named sales

Create a user named sales_user

Grant usage and create privileges on the sales schema to sales_user

Revoke public access on the sales schema

💡 Why This Matters

🌍 Real World

Companies often separate data by department using schemas and restrict access to protect sensitive information.

💼 Career

Database administrators and backend developers use schema-level access control to enforce security policies and manage user permissions efficiently.

1

Create the sales schema

Write a SQL statement to create a schema named sales in the database.

PostgreSQL

-- Your code here to create the sales schema

Need a hint?

Use the CREATE SCHEMA command followed by the schema name.

2

Create the user sales_user

Write a SQL statement to create a user named sales_user with password 'sales_pass'.

PostgreSQL

-- Your code here to create the sales_user

Need a hint?

Use CREATE USER with WITH PASSWORD to set the password.

3

Grant schema privileges to sales_user

Write SQL statements to grant USAGE and CREATE privileges on the sales schema to the user sales_user.

PostgreSQL

CREATE SCHEMA sales;
CREATE USER sales_user WITH PASSWORD 'sales_pass';
-- Your code here to grant privileges

Need a hint?

Use two GRANT statements, one for USAGE and one for CREATE.

4

Revoke public access on the sales schema

Write a SQL statement to revoke all privileges on the sales schema from the public role.

PostgreSQL

CREATE SCHEMA sales;
CREATE USER sales_user WITH PASSWORD 'sales_pass';
GRANT USAGE ON SCHEMA sales TO sales_user;
GRANT CREATE ON SCHEMA sales TO sales_user;
-- Your code here to revoke public access

Need a hint?

Use REVOKE ALL ON SCHEMA sales FROM public; to remove default access.