CA 24 - Users, Roles, Groups

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;

2: Fix permission denied for customer table
GRANT SELECT ON customer TO report_user;

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;

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;

5: Remove SELECT access on film from report_user
REVOKE SELECT ON film FROM report_user;

6: Create readonly_group with SELECT on all tables
CREATE ROLE readonly_group;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

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;