Users, Roles, Groups

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

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

Task 2: Now try to access customer table. When report_user tries to query customer and gets permission denied. Fix it.
To allow report_user to read the full customer table:

GRANT SELECT ON customer TO report_user;

Task 3: Allow report_user to see only customer_id, first_name, last_name of the customer table
If full SELECT was already given in Task 2, first remove it, then give column-level permission:

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

Task 4: Create support_user who can SELECT from customer, UPDATE only email column, Cannot DELETE

CREATE ROLE support_user WITH LOGIN PASSWORD 'support123';
GRANT SELECT ON customer TO support_user;
GRANT UPDATE (email) ON customer TO support_user;

DELETE permission is not given, so support_user cannot delete.

Task 5: Remove SELECT access on film from report_user

REVOKE SELECT ON film FROM report_user;

Task 6: Create readonly_group that has SELECT on all tables

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

Task 7: Create analyst1 and analyst2 and add them to readonly_group

CREATE ROLE analyst1 WITH LOGIN PASSWORD 'analyst123';
CREATE ROLE analyst2 WITH LOGIN PASSWORD 'analyst123';

GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;