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;

Needed a user who can only view data, so gave login access and only SELECT on film. No extra permissions to keep it restricted.

---

Qn 2: Fix permission denied for customer table

GRANT SELECT ON customer TO report_user;

Error usually means missing permission, so just granted read access. No need to change anything else.

---

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;

Full access was too broad, so removed it first. Then gave access only to needed 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;

Support role should view data and update emails, nothing more. Explicitly removed delete to avoid mistakes.

---

Qn 5: Remove SELECT access on film from report_user

REVOKE SELECT ON film FROM report_user;

Requirement changed, so took back access. Simple revoke does the job.

---

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 one by one, created a group. Easier to manage multiple users later.

---

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;

Created users first, then attached them to the group. This way they inherit all read-only permissions automatically.