Search code examples
postgresqlsql-grant

how to grant update or select on some specified row in postgresql table?

I want to create some roles in my Postgresql DB and grant some access.

I have student role and i want to grant this user type : can edit only record a bout him/her in student table and can not edit other rows

how can i do it?

thanks

Solution

  • Create a view on the table with an appropriate where clause, then grant access to that:

    create view students_view as
select col1, col2, col3 -- limit column access here
from mytable
where <whatever>; -- limit row access here

-- limit what he can do here
grant update, select to student_role;

    BTW It is a commonly held misconception that you can't update a view, but that is only true if the view is a join or similarly complicated query.