3

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

happy Sun
  • 579
  • 8
  • 15

3 Answers3

5

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.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Until very recent Pg versions explicit triggers or rules were required to make views updatable, so it was kind-of true. – Craig Ringer May 01 '13 at 07:51
  • what about inserts? say your filter criteria is foo="bar". what happens if the users does an insert into students_view(foo) values ("bar"); or even, update students_view set foo="bar"; will they be able to do that? anyway to limit this type of behavior? – Chris Drappier Jun 26 '14 at 18:22
  • @chris to my mind, new/updated rows with foo=bar *should* be visible. If that is no good, either make the filter narrower, or use the id to filter on a specific whitelist of ids `...where id in (1,3,5,8,etc)` – Bohemian Jun 26 '14 at 22:19
4

PostgreSQL doesn't have row-level declarative security (yet, there's ongoing work into it) so if you can't just create a view - say, if you have many different people who need this access - you will probably need a SECURITY DEFINER helper function or trigger.

You've got a couple of options:

  • Write a SECURITY DEFINER function that lets them make only the permitted changes and limit their access to the table to SELECT, revoking UPDATE, DELETE, TRUNCATE and INSERT rights; or
  • write a trigger that tries to restrict them from making changes you don't want them to make and GRANT them write access to the table.

Of the two, the function and restricted rights approach is by far the safest option so long as you follow the SECURITY DEFINER secure coding guidelines set above - setting search_path for the function, avoiding dynamic SQL (EXECUTE) with string substitutions, etc.

The view approach given above can work quite nicely if it's a view that filters by current_user. You may also want to look at the new SECURITY BARRIER views; see this post for a useful discussion of them.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
0

GRANT UPDATE(column) on tabela to user_name;

Maelson Marques
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 26 '22 at 01:34