In a database system, controlling who can access and modify data is very important. In real-world applications like digital platforms, different users should have different levels of access based on their role. To understand this, I performed a series of tasks using the DVD Rental database in PostgreSQL.
First, I created a role called report_user with login access and gave it permission to read only from the film table. This ensured that the user could view data but not modify it.
Next, I tested access to the customer table using this role. As expected, PostgreSQL denied permission because no access had been granted. I then fixed this by giving SELECT permission on the customer table.
After that, I restricted access further by allowing report_user to view only specific columns: customer_id, first_name, and last_name. This demonstrated column-level security, where users can see only the required data instead of the entire table.
Then, I created another role called support_user. This role was given permission to read from the customer table and update only the email column. At the same time, delete access was not provided, ensuring that important data could not be removed.
In the next step, I removed the previously given SELECT permission on the film table from report_user, showing how permissions can be revoked when no longer needed.
I also created a group role called readonly_group and granted it SELECT access on all tables. Then, I created two users, analyst1 and analyst2, and added them to this group. This allowed both users to inherit read-only access without assigning permissions individually.
Through this exercise, I understood how PostgreSQL manages roles and permissions effectively. It allows fine-grained control over data access, ensuring security and proper usage of the database. This is especially important in real-world systems where different users require different levels of access.
Top comments (1)
Nice hands-on example—clearly shows how roles, permissions, and revocation work in a practical PostgreSQL setup.