Re: grant the right to select only certain rows?

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.