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;
Here, we first create a new login role named report_user.
Then we give only SELECT permission on the film table, so this user can only read that table.

Task 2: Now try to access customer table. When report_user tries to query customer and gets permission denied. Fix it
GRANT SELECT ON customer TO report_user;
The error happens because report_user was not given permission on the customer table.
So we fix it by granting SELECT access on customer.

Task 3: Allow report_user to see only customer_id, first_name, last_name of the customer table
REVOKE SELECT ON customer FROM report_user;

GRANT SELECT (customer_id, first_name, last_name)
ON customer
TO report_user;
If we want the user to see only some columns, full table access should be removed first.
Then column-level SELECT permission is given only for the required columns.

Task 4: Create support_user who can SELECT from customer, UPDATE only email column, and 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;
This user should be able to read customer details and change only the email column.
Since we do not give DELETE permission, the user cannot delete any row.

Task 5: Remove SELECT access on film from report_user
REVOKE SELECT ON film FROM report_user;
Earlier, report_user was allowed to read from film.
Now this command removes that permission.

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;
Here, readonly_group acts like a common read-only role.
Anyone added to this group will get SELECT access on all tables in the public schema.

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;
First, we create two login users.
Then we add both of them to readonly_group,so they automatically get all read-only permissions of that group.