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
3 Answers
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.
3 Comments
Craig Ringer
Until very recent Pg versions explicit triggers or rules were required to make views updatable, so it was kind-of true.
Chris Drappier
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?
Bohemian
@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)See the postgres docs for row level security on Pg 12+.
PostgreSQL 11 and older did not have declarative row-level security 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 DEFINERfunction that lets them make only the permitted changes and limit their access to the table toSELECT, revokingUPDATE,DELETE,TRUNCATEandINSERTrights; or - write a trigger that tries to restrict them from making changes you don't want them to make and
GRANTthem 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.
1 Comment
bugmenot123
For future readers: Since PostgreSQL 9.5 there is Row Level Security stackoverflow.com/a/31977586/4828720
GRANT UPDATE(column) on tabela to user_name;
1 Comment
Community
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.