Re: row-level security model

On Thu, Apr 01, 2004 at 13:21:15 -0800,
Marc Durham <pgsql(at)d-tech(dot)com> wrote:
> From: "John DeSoi" <jd(at)icx(dot)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.