Re: grant the right to select only certain rows? - Mailing list pgsql-general

Fran Fabrizio writes:
>
>
> If I have a table students:
>
> name    grade_level
> Joe        1
> Kim        1
> Lisa        2
> Mike        2
>
> And I have two database users, mary_smith and tom_white.  If Mary Smith
> is the 1st grade teacher, is there any way to grant her access to only
> select rows where grade_level=1?  I think GRANT only works as a
> table-wide permission, but a co-worker thinks he has seen similar
> behavior in Oracle, like
> "GRANT SELECT AS SELECT * FROM STUDENTS WHERE grade_level = 1
> ON students FOR USER mary_smith"   (Rough approximation of the type of
> query I am looking for).

Not directly, Pg's ACL (Access Control Lists) only apply to full relations.
What you can do though is create a view and only grant her permission on
that view.

  CREATE VIEW first_grade AS SELECT * FROM students WHERE grade_level = 1;
  GRANT SELECT ON first_grade TO mary_smith;

So Mary doesn't have permissions to the "student" table, but she does have
permission to view the "first_grade" view. Not perfect by any stretch of the
imagination, but workable in many situations.