Users, Roles, Groups

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

solution:

• To create a login role
  CREATE ROLE report_user LOGIN PASSWORD 'password123';

• Grant SELECT on film table
  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.

solution:

• Grant SELECT access to 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.

solution:

• Revoke full access first
  REVOKE SELECT ON customer FROM report_user;

• Grant column-level access
  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

solution:

• Create user
  CREATE ROLE support_user LOGIN PASSWORD 'password123';

• Allow SELECT on customer
  GRANT SELECT ON customer TO support_user;

• Allow UPDATE only on email column
  GRANT UPDATE (email) ON customer TO support_user;

• Ensure DELETE is not allowed (default is no access, but explicit revoke for safety)
  REVOKE DELETE ON customer FROM support_user;

Task 5: Remove SELECT access on film from report_user.

Solution:

• To remove access from report_user REVOKE SELECT ON film FROM report_user;

Task 6: Create readonly_group that has SELECT on all tables.

Solution:

• Create role group
  CREATE ROLE readonly_group;

• Grant SELECT on all tables in public schema
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

• Future tables also get SELECT automatically
  ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly_group;

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

Solution:

• Create users
  CREATE ROLE analyst1 LOGIN PASSWORD 'password123';
  CREATE ROLE analyst2 LOGIN PASSWORD 'password123';

• Add users to group
  GRANT readonly_group TO analyst1;
  GRANT readonly_group TO analyst2;