DEV Community

loading...
Cover image for How to show specific information to specific users in Postgress

How to show specific information to specific users in Postgress

sabrinasuarezarrieta
Colombian full stack Developer that loves learning new things and sharing ...
・2 min read

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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";
Enter fullscreen mode Exit fullscreen mode

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.
Alt Text

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!!

Discussion (5)

Collapse
satoru profile image
satoru

Wow, you write really fast.

Collapse
sabrinasuarezarrieta profile image
sabrinasuarezarrieta Author

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

Collapse
satoru profile image
satoru

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

Thread Thread
sabrinasuarezarrieta profile image
sabrinasuarezarrieta Author

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.

Thread Thread
satoru profile image
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.