This one was different from all the previous practice problems. No filtering, no sorting, no aliases. This set was all about controlling who can access what in the database. It is called access control or privilege management and it is something that matters a lot in real world databases where you do not want everyone seeing everything.
All of this was done using the DVD Rental database in PostgreSQL.
Task 1 — Create a Login Role That Can Only Read from the Film Table
The first task was to create a user called report_user who can log in but can only read from the film table and nothing else.
CREATE ROLE report_user WITH LOGIN PASSWORD 'password123';
GRANT SELECT ON film TO report_user;
CREATE ROLE sets up the new user. WITH LOGIN means they can actually log into the database. WITH PASSWORD gives them a password to log in with. GRANT SELECT gives them read only access to the film table.
Task 2 — Fix the Permission Denied Error on the Customer Table
When report_user tries to query the customer table they get a permission denied error. That is expected because we only gave them access to film. To fix it you grant them SELECT on customer as well.
GRANT SELECT ON customer TO report_user;
This is actually the point of the exercise. By default a new role cannot access anything. You have to explicitly grant every permission. That is what makes PostgreSQL access control safe by default.
Task 3 — Allow report_user to See Only Certain Columns of the Customer Table
Instead of giving full access to the customer table, this task wanted report_user to only see three specific columns. PostgreSQL lets you grant SELECT on individual columns instead of the whole table.
REVOKE SELECT ON customer FROM report_user;
GRANT SELECT (customer_id, first_name, last_name) ON customer TO report_user;
First I revoked the full table access I gave in the previous task. Then I granted SELECT only on the three columns that are allowed. Now report_user can query customer but they will only ever see customer_id, first_name, and last_name. Any attempt to select email or other columns will be denied.
Task 4 — Create support_user with Specific Permissions
This task was about creating a role with a mix of permissions. support_user should be able to read from customer, update only the email column, but not delete anything.
CREATE ROLE support_user WITH LOGIN PASSWORD 'support123';
GRANT SELECT ON customer TO support_user;
GRANT UPDATE (email) ON customer TO support_user;
No DELETE grant is given at all which means support_user cannot delete rows. Just like with SELECT, you can grant UPDATE on a specific column only, which is exactly what we did here for email.
Task 5 — Remove SELECT Access on Film from report_user
Revoking a permission works the same way as granting, just with REVOKE instead of GRANT.
REVOKE SELECT ON film FROM report_user;
After this report_user can no longer query the film table. Clean and straightforward.
Task 6 — Create a Group Role with SELECT on All Tables
Instead of granting permissions to individual users one by one, you can create a group role and assign permissions to the group. Then you add users to the group and they all inherit the same permissions.
CREATE ROLE readonly_group;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;
CREATE ROLE without WITH LOGIN means this is a group role, not a user that can log in. GRANT SELECT ON ALL TABLES IN SCHEMA public gives read access to every table in the public schema at once. Much cleaner than granting table by table.
Task 7 — Create analyst1 and analyst2 and Add Them to the Group
CREATE ROLE analyst1 WITH LOGIN PASSWORD 'analyst1pass';
CREATE ROLE analyst2 WITH LOGIN PASSWORD 'analyst2pass';
GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;
Both users are created with login access. Then GRANT readonly_group TO gives each of them all the permissions that readonly_group has. Now if you ever want to add or remove a permission for all analysts, you just change it on readonly_group and it applies to everyone in the group automatically.
What This Set Taught Me
This was honestly the most interesting set so far. The idea that permissions have to be explicitly granted, that you can control access down to individual columns, and that group roles make managing multiple users so much cleaner, these are things that matter the moment you work on a real database with real users. Security in SQL is not just about writing the right queries, it is also about making sure the right people can only see what they are supposed to see.
Top comments (0)