Users, Roles, Groups

Think of a user as an individual person who logs into the database and does work. A role is like a set of permissions — what actions are allowed, like reading or updating data. Instead of giving permissions to each user one by one, we create groups (roles) and assign permissions to them, then simply add users to those groups. This way, managing access becomes much easier, because changing the role automatically updates permissions for everyone in it.lets look into some examples to understand better

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

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

GRANT is used to grant access to use commands to roles

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

SELECT * FROM customer;
*Error prompted*
GRANT SELECT ON customer TO report_user;

as user was initially given access only yto film table, he was unable to access the customer table

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;

REVOKE is used to disable a user from doing certain commands in a table

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

CREATE ROLE support_user WITH LOGIN PASSWORD 'pass123';
GRANT SELECT ON customer TO support_user;
GRANT UPDATE (email) ON customer TO support_user;
REVOKE DELETE ON customer FROM support_user;

Task 5: Remove SELECT access on film from report_user.

REVOKE SELECT ON film TO 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 'pass123';
CREATE ROLE analyst2 WITH LOGIN PASSWORD 'pass123';
GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;