Role creation and management in PostgreSQL - Mini Project: Build & Apply

Role Creation and Management in PostgreSQL

📖 Scenario: You are a database administrator for a small company. You need to create roles to manage user permissions securely. This project will guide you through creating roles, assigning privileges, and managing role memberships in PostgreSQL.

🎯 Goal: Build a set of PostgreSQL commands to create roles, assign privileges, and manage role memberships to control database access effectively.

📋 What You'll Learn

Create a role named reporting_user without login permission

Create a role named data_analyst with login permission and password analyst123

Grant SELECT privilege on the sales table to reporting_user

Make data_analyst a member of reporting_user

Set data_analyst to inherit privileges from reporting_user

💡 Why This Matters

🌍 Real World

Database administrators use roles to control who can access and modify data securely in real companies.

💼 Career

Understanding role creation and management is essential for database security and user management in many IT and data-related jobs.

1

Create the reporting_user role

Create a role called reporting_user without login permission using the command CREATE ROLE reporting_user NOLOGIN;

PostgreSQL

# Create the reporting_user role without login permission
# Your code here

Need a hint?

Use CREATE ROLE followed by the role name and NOLOGIN to prevent login.

2

Create the data_analyst role with login and password

Create a role called data_analyst with login permission and password analyst123 using CREATE ROLE data_analyst LOGIN PASSWORD 'analyst123';

PostgreSQL

CREATE ROLE reporting_user NOLOGIN;
# Create the data_analyst role with login and password
# Your code here

Need a hint?

Use LOGIN to allow the role to connect and PASSWORD 'analyst123' to set the password.

3

Grant SELECT privilege on sales table to reporting_user

Grant SELECT privilege on the sales table to the role reporting_user using GRANT SELECT ON sales TO reporting_user;

PostgreSQL

CREATE ROLE reporting_user NOLOGIN;
CREATE ROLE data_analyst LOGIN PASSWORD 'analyst123';
# Grant SELECT privilege on sales table to reporting_user
# Your code here

Need a hint?

Use GRANT SELECT ON sales TO reporting_user; to allow read access.

4

Make data_analyst a member of reporting_user and set inheritance

Make data_analyst a member of reporting_user using GRANT reporting_user TO data_analyst; and ensure data_analyst inherits privileges with ALTER ROLE data_analyst INHERIT;

PostgreSQL

CREATE ROLE reporting_user NOLOGIN;
CREATE ROLE data_analyst LOGIN PASSWORD 'analyst123';
GRANT SELECT ON sales TO reporting_user;
# Grant reporting_user role to data_analyst and set inheritance
# Your code here

Need a hint?

Use GRANT reporting_user TO data_analyst; to add membership and ALTER ROLE data_analyst INHERIT; to allow privilege inheritance.