Creating Users, Roles and Groups in PostgreSQL

In this exercise, I worked with the DVD Rental Database to practice role-based access control in PostgreSQL. The goal was to create users, assign permissions, and control access to tables using SQL.

All tables:

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

Task 2:Fix permission denied when accessing customer table

Task 3:Restrict access to specific columns

Task 4:Create support_user with controlled permissions

Task 5:Create support_user with controlled permissions

Task 6:Create readonly_group with SELECT on all tables

Task 7:Create analyst users and assign them to readonly_group

What I Learned

• How to create roles using CREATE ROLE
• How to control access using GRANT and REVOKE
• Column level permissions in PostgreSQL
• How group roles simplify permission management