Table-level permissions in PostgreSQL - Mini Project: Build & Apply

Managing Table-Level Permissions in PostgreSQL

📖 Scenario: You are a database administrator for a small company. You need to control who can see and change data in the employees table to keep information safe.

🎯 Goal: Learn how to create a table and set up permissions so only certain users can read or modify the data.

📋 What You'll Learn

Create a table called employees with specific columns

Create a role called hr_staff

Grant SELECT permission on employees to hr_staff

Grant INSERT permission on employees to hr_staff

💡 Why This Matters

🌍 Real World

Companies often need to control who can see or change data in their databases to protect sensitive information.

💼 Career

Database administrators and developers use table-level permissions to secure data and comply with company policies.

1

Create the employees table

Write a SQL statement to create a table called employees with these columns: id as integer primary key, name as text, and position as text.

PostgreSQL

-- Your code here

Need a hint?

Use CREATE TABLE followed by the table name and define each column with its type.

2

Create the role hr_staff

Write a SQL statement to create a role called hr_staff without login permission.

PostgreSQL

-- Your code here

Need a hint?

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

3

Grant SELECT permission on employees to hr_staff

Write a SQL statement to grant SELECT permission on the employees table to the role hr_staff.

PostgreSQL

-- Your code here

Need a hint?

Use GRANT SELECT ON employees TO hr_staff; to allow reading the table.

4

Grant INSERT permission on employees to hr_staff

Write a SQL statement to grant INSERT permission on the employees table to the role hr_staff.

PostgreSQL

-- Your code here

Need a hint?

Use GRANT INSERT ON employees TO hr_staff; to allow adding new rows.