How to show specific information to specific users in Postgress

In the first place, we need to define a view, A view is a database object that is of a stored query. A view can be accessed as a virtual table in PostgreSQL. In other words, a PostgreSQL view is a logical table that represents data of one or more underlying tables through a SELECT statement.
For this example, we are going to allow students to see their grades for their subjects but we do not want that they can see the grades of their classmates, so we will construct a view where the condition validates the user that is logged in.

CREATE VIEW student_view AS
select e.code, e.name, i.code_subject , s.name_subject , i.n1 , i.n2, i.n3 
, ((coalesce(n1, 0))*.35+(coalesce(n2, 0))*.35+(coalesce(n3, 0))*.30) finalNote
from suscribe i
natural join student e 
natural join subject s
where cod_e::text = current_user

Next, we need to define a group role which will have permission only to see the information in the view previously created.

CREATE ROLE students;

GRANT SELECT ON student_view TO students;

The last step is to create with login the users of our students and grant them group students permissions.

CREATE ROLE "200001"
LOGIN 
PASSWORD '200001';

GRANT students TO "200001";

When the user 200001 log in into the database only the information from the view will be shown as you can see in the example.

Finally, I want to recommend to you guys the user of DBeaver is a free multi-platform database tool for database administrators and allows to have multiple connections to the same database with different users in a really simple way for PostgreSQL (easier than pgAdmin 4 )

Thank you so much for reading and I hope this information will be helpful to you!!

Comments

[satoru]

Wow, you write really fast.

[sabrinasuarezarrieta]

Thanks!! If im in the mood and with the inspiration I try to take the wave, because sometimes doesn't strike in days hahaha

[satoru]

Cool. I feel more like writing code recently. But I often don't know what to write when it comes to posting articles.

[sabrinasuarezarrieta]

Well I don't know if this will be helpful to you but lately, if a coworker asks me something I just know that I need to write about it in a really easy peasy lemon squeezy way, I try to give good information but I don't pressure myself that have to be perfect, I just want to share something useful for someone like my coworker ... maybe you have some really interesting things to write about but you don't think that is great enough, but could be amazing for someone.

[satoru]

Thanks for sharing. I used to write these kind of stuff into my note with Obsidian. Next time I can try to share them on Dev.to.