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