Qn 1: Create a login role report_user that can only read from the film table
CREATE ROLE report_user WITH LOGIN PASSWORD 'password123';
GRANT SELECT ON film TO report_user;
we need a user who can log in but shouldn’t be able to modify anything. so first create the role with login, then just give select permission on film so it can only view data.
Qn 2: Fix permission denied for customer table
GRANT SELECT ON customer TO report_user;
this error just means the user doesn’t have access. so instead of changing anything else, just give select permission on customer.
Qn 3: Allow only specific columns from customer table
REVOKE SELECT ON customer FROM report_user;
GRANT SELECT (customer_id, first_name, last_name) ON customer TO report_user;
i removed the full access first then allowed access only to required columns instead of the whole table.
Qn 4: Create support_user with limited permissions
CREATE ROLE support_user WITH LOGIN PASSWORD 'password123';
GRANT SELECT ON customer TO support_user;
GRANT UPDATE (email) ON customer TO support_user;
REVOKE DELETE ON customer FROM support_user;
Support role should view data and update emails. So i explicitly removed delete to avoid mistakes.
Qn 5: Remove SELECT access on film from report_user
REVOKE SELECT ON film FROM report_user;
As per change in requirement, i just revoked the access from the report_user.
Qn 6: Create readonly_group with SELECT on all tables
CREATE ROLE readonly_group;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;
Instead of assigning permissions one by one we can create a group so that it is easier to manage multiple users later.
Qn 7: Create analyst users and add to group
CREATE ROLE analyst1 WITH LOGIN PASSWORD 'password123';
CREATE ROLE analyst2 WITH LOGIN PASSWORD 'password123';
GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;
We create users first then attach them to the group. This way they inherit all read-only permissions automatically.
Top comments (1)
Postgres RBAC is genuinely underused. So many apps just use a single superuser connection because it's easier to set up — then wonder why a bug in one service can wipe another's data.
Row-level security is the next step after roles — once you've modeled who can do what at the role level, RLS lets you push the 'which rows' logic into the database itself. Dramatically simplifies application-level access control.