Query for hierarchical RBAC scenario
Here's what I came up with -- thanks to @CL. for the tips. I made it into a view so I can just query against that for a particular user.

CREATE VIEW user_permissions_deep AS
SELECT DISTINCT user_id, permission_id
FROM (
    SELECT urd.user_id user_id, pr.permission_id permission_id, urd.role_id role_id, urd.parent_id parent_role
    FROM (
        WITH RECURSIVE
          deep_roles(user_id, role_id, parent_id, depth) AS (
            SELECT ru.user_id, rr.role_id, rr.parent_id, 0 depth
            FROM role_roles rr
            INNER JOIN role_users ru ON rr.role_id = ru.role_id
            UNION
            SELECT dr.user_id, rr.role_id, rr.parent_id, dr.depth+1
            FROM deep_roles dr, role_roles rr
            WHERE rr.role_id=dr.parent_id
            AND dr.depth &lt; 10
          )
        SELECT user_id, role_id, parent_id FROM deep_roles
        WHERE depth &gt; 0
        UNION
        SELECT user_id, role_id, null parent_id
        FROM role_users
    ) AS urd
    INNER JOIN permission_roles pr
        ON pr.role_id = urd.role_id
        OR pr.role_id = urd.parent_id
    UNION
    SELECT user_id, permission_id, null role_id, null parent_id
    FROM permission_users
) AS upd
INNER JOIN permission p ON upd.permission_id = p.id;


I found I had to keep the depth check otherwise the loop would never exit if there was a cycle in the role inheritance chain.

If anyone has any suggestions on how this could be improved, please let me know!