Users, Roles, Groups

Qn 1: Create a login role report_user that can only read from the film table

CREATE ROLE report_user WITH LOGIN PASSWORD 'password123';
GRANT SELECT ON film TO report_user;

A separate role was needed just for viewing data, so login access was created with minimal privileges. Only read permission on the film table was provided to keep access tight.

---

Qn 2: Fix permission denied for customer table

GRANT SELECT ON customer TO report_user;

The error indicates missing access, so read permission was added. No other changes required since only viewing was needed.

---

Qn 3: Allow only specific columns from customer table

REVOKE SELECT ON customer FROM report_user;
GRANT SELECT (customer_id, first_name, last_name) ON customer TO report_user;

General access was too broad, so it was removed first. Then access was restricted to only the required columns.

---

Qn 4: Create support_user with limited permissions

CREATE ROLE support_user WITH LOGIN PASSWORD 'password123';
GRANT SELECT ON customer TO support_user;
GRANT UPDATE (email) ON customer TO support_user;
REVOKE DELETE ON customer FROM support_user;

This role is meant for handling customer info, so it can view data and update emails. Delete permission was removed to avoid accidental data loss.

---

Qn 5: Remove SELECT access on film from report_user

REVOKE SELECT ON film FROM report_user;

Since access is no longer required, the permission was withdrawn. Revoking ensures the role cannot read from that table anymore.

---

Qn 6: Create readonly_group with SELECT on all tables

CREATE ROLE readonly_group;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

Instead of assigning permissions repeatedly, a group role was created. This makes it easier to manage read-only access across multiple users.

---

Qn 7: Create analyst users and add to group

CREATE ROLE analyst1 WITH LOGIN PASSWORD 'password123';
CREATE ROLE analyst2 WITH LOGIN PASSWORD 'password123';
GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;

Users were created individually and then linked to the group. This way, permissions are inherited without assigning them one by one.