Users, Roles, Groups

Lets understand roles and permissions with a few practical questions.

QN 1: Create a login role that can only read from film table

CREATE ROLE report_user LOGIN;
GRANT SELECT ON film TO report_user;

we create a user called report_user and give it only SELECT permission on the film table. So it can read data but cant modify anything.

2: Accessing customer table gives permission denied fix it

GRANT SELECT ON customer TO report_user;

The error happens because report_user doesnt have access to the customer table. Granting SELECT fixes the issue.

3: Allow access to only specific columns

REVOKE SELECT ON customer FROM report_user;
GRANT SELECT (customer_id, first_name, last_name) ON customer TO report_user;

we remove full access.
Then we grant access only to selected columns. Now the user can’t see sensitive fields like email or other data.

4: Create a support user with limited permissions

CREATE ROLE support_user LOGIN;
GRANT SELECT ON customer TO support_user;
GRANT UPDATE (email) ON customer TO support_user;

This user can: Read all customer data update only the email column no delete or full update access is given tight control.

5: Remove access from film table

REVOKE SELECT ON film FROM report_user;

This simply removes the permission we granted earlier.

6: Create a read-only group

CREATE ROLE readonly_group;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

This creates a group role that can read all tables in the schema.

7: Add users to the group

CREATE ROLE analyst1 LOGIN;
CREATE ROLE analyst2 LOGIN;
GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;

Both users inherit permissions from the group so instead of managing permissions individually we manage them centrally.