Re: row-level security model

On Thu, Apr 01, 2004 at 13:21:15 -0800,
  Marc Durham <pgsql@d-tech.com> wrote:
> From: "John DeSoi" <jd@icx.net>
>
> Do you think this would work?
> There are a lot of joins. And I assumed it would need to look up the
> parent's and teacher's  usernames, and that your many-to-many relationships
> were in tables like students_parent_link.
>
> CREATE VIEW your_students AS
> SELECT s.*
>  FROM student AS s
>   INNER JOIN class_student_link AS cs ON s.student_id = cs.student_id
>   INNER JOIN class AS c ON cs.class_id = c.class_id
>   INNER JOIN students_parent_link AS sp ON s.student_id = sp.student_id
>   INNER JOIN parent AS p ON sp.parent_id = p.parent_id
>   INNER JOIN teacher AS t ON c.teacher_id = t.teacher_id
> WHERE t.username = CURRENT_USER()
>  OR p.username = CURRENT_USER()

This makes the implicit assumption that students always have at least
one parent and at least one teacher. If that isn't necessarily true
you will need to use a couple of left (or right) joins or records of
students missing one or the other will not be accessible.