GRANT and REVOKE permissions in PostgreSQL - Mini Project: Build & Apply

Managing User Permissions with GRANT and REVOKE in PostgreSQL

📖 Scenario: You are a database administrator for a small company. You need to control who can access and modify the employees table in your PostgreSQL database. You will practice giving and taking away permissions to users.

🎯 Goal: Learn how to use GRANT to give permissions and REVOKE to remove permissions on a table in PostgreSQL.

📋 What You'll Learn

Create a table named employees with columns id (integer) and name (text).

Grant SELECT permission on employees to a user named alice.

Revoke SELECT permission on employees from the user alice.

Grant INSERT permission on employees to a user named bob.

💡 Why This Matters

🌍 Real World

Database administrators often need to control who can read or change data. Using GRANT and REVOKE helps keep data safe and organized.

💼 Career

Knowing how to manage permissions is essential for roles like database administrator, backend developer, and data engineer.

1

Create the employees table

Write a SQL statement to create a table called employees with two columns: id as an integer and name as text.

PostgreSQL

-- Your code here to create the employees table

Need a hint?

Use CREATE TABLE employees (id INTEGER, name TEXT); to create the table.

2

Grant SELECT permission to alice

Write a SQL statement to grant SELECT permission on the employees table to the user alice.

PostgreSQL

CREATE TABLE employees (
    id INTEGER,
    name TEXT
);
-- Your code here to grant SELECT to alice

Need a hint?

Use GRANT SELECT ON employees TO alice; to give read access.

3

Revoke SELECT permission from alice

Write a SQL statement to revoke SELECT permission on the employees table from the user alice.

PostgreSQL

CREATE TABLE employees (
    id INTEGER,
    name TEXT
);

GRANT SELECT ON employees TO alice;
-- Your code here to revoke SELECT from alice

Need a hint?

Use REVOKE SELECT ON employees FROM alice; to remove read access.

4

Grant INSERT permission to bob

Write a SQL statement to grant INSERT permission on the employees table to the user bob.

PostgreSQL

CREATE TABLE employees (
    id INTEGER,
    name TEXT
);

GRANT SELECT ON employees TO alice;

REVOKE SELECT ON employees FROM alice;
-- Your code here to grant INSERT to bob

Need a hint?

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