Users, Roles, Groups

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

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

here a role named report_user is created with login password.Then SELECT permission is granted only on the film table. This means the user can read data from film but cannot modify it or access other tables.

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 occurs because report_user does not have permission on the customer table. Granting SELECT allows the user to read data from that table and resolve the permission denied issue.

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;

First, full SELECT access is removed to avoid unrestricted viewing. Then column-level permissions are granted so the user can only see specific columns such as customer_id, first_name, last_name .

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

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

The support_user role is created with login access. It is allowed to read all customer data using SELECT. It can only update the email column due to column-level UPDATE permission. DELETE permission is explicitly revoked to ensure the user cannot remove records.

Task 5: Remove SELECT access on film from report_user.

REVOKE SELECT ON film FROM report_user;

here the select removes the previously granted read access on the film table, so report_user can no longer query it.

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;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_group;

A group role readonly_group is created. It is granted SELECT access on all existing tables in the public schema .The ALTER DEFAULT PRIVILEGES ensures that any future tables created in the schema will also automatically grant SELECT access to this group.

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

CREATE ROLE analyst1 LOGIN PASSWORD 'password';
CREATE ROLE analyst2 LOGIN PASSWORD 'password';
GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;

Two new login roles (analyst1 and analyst2) are created. They are added to readonly_group, which means they inherit all its permissions, including read-only access to all tables. This avoids granting permissions individually and simplifies management.