Firebase security rules for hierarchical structure

Question

How can I give users access to data for anything below a certain node in a hierarchical structure AND make it easy to query? Can this be done in Firebase, or do I have to abandon my beloved Firebase and go back to...grumble grumble...RDBMS?

I've tried two different ways. One makes it easy to query but hard to restrict access. The other makes it easier to restrict access but means I have to do nested loops to aggregate all my data.

Specifically, I have a typical business organization:

• Company
  • West Region
    • Division 1
      • Department 1
      • Department 2
    • Division 2
      • Department 3
      • Department 4
  • South Region
    • Division 3
      • Department 5
      • Department 6
• Company 2 ...etc.

At the lowest (department) level, I have orders whose amounts I have to aggregate.

First Attempt

Data (Denormalized)

{
    "Orders": {
        "UniqueID1": {
            "company": "Company",
            "region": "West Region",
            "division": "Division 1",
            "department": "Department 1",
            "amount": 19.8
        },
        "UniqueID2": {
            "company": "Company",
            "region": "West Region",
            "division": "Division 1",
            "department": "Department 1",
            "amount": 20.1
        },
        ...and so on.
    },
    "Users": {
        "Bob's UID": {
            "departments": {
                "Department 1": true, // Note that these two departments combined are Division 1
                "Department 2": true
            }
        }
    }
}

Rules

{
    "Orders": {
        ".indexOn": ["company", "region", "division", "department"],
        ".read":false,
        ".write":false,
        "$order_id": {
            ".read": "root.child('Users').child(auth.uid).child('departments').hasChild(data.child('department').val())"
        }
    }
}

Conclusion

Pros

• Querying is flexible, e.g: ordersRef.orderByChild('division').equalTo('Division 1').
• Querying is fast. This works in under 2 seconds for 200k records.

Cons

• I don't believe I can restrict access with kind of a structure. I get permission_denied based on the rule I have above. I think it's because I've run into the issue of "rules are not filters, tsk tsk tsk".

Second Attempt

Data (More Normalized)

{
    "Orders": {
        "Department 1": {
            "UniqueID1": {
                "company": "Company",
                "region": "West Region",
                "division": "Division 1",
                "amount": 19.8
            },
            "UniqueID2": {
                "company": "Company",
                "region": "West Region",
                "division": "Division 1",
                "amount": 20.1
            },
        },
        "Department 2": {...
        ...and so on.
    },
    "Users": {
        "Bob's UID": {
            "departments": {
                "Department 1": true, // Note that these two departments combined are Division 1
                "Department 2": true
            }
        }
    }
}

Rules

{
    "Orders": {
        ".read":false,
        ".write":false,
        "$order_id": {
            ".read": "root.child('Users').child(auth.uid).child('departments').hasChild(data.child('department').val())"
        }
    }
}

Conclusion

Pros

• I can restrict access using security rules now by reading one department at a time.

Cons

• I don't think I can do a deep AND variate index on "Orders/$order_id/" +company, region, or division, which means I have to make a separate call for each department's orders to aggregate at some higher level.

Answer 1

Your first data structure looks reasonable. You can access specific orders you have access to, and you already can find the list of the divisions for a specific user.

To also allow accessing the order for (for example) a division, you'll need to add a secondary index that maps a division to a list of order IDs:

{
    "Orders": {
        "UniqueID1": {
            "company": "Company",
            "region": "West Region",
            "division": "Division 1",
            "department": "Department 1",
            "amount": 19.8
        },
        "UniqueID2": {
            "company": "Company",
            "region": "West Region",
            "division": "Division 1",
            "department": "Department 1",
            "amount": 20.1
        },
        ...and so on.
    },
    "Users": {
        "Bob's UID": {
            "departments": {
                "Department 1": true, // Note that these two departments combined are Division 1
                "Department 2": true
            }
        }
    },
    "OrdersByDivision": {
        "Division 1": {
            "UniqueID1": true,
            "UniqueID2": true
        }
    }
}

Now you can find the list of orders for a division with a direction lookup under OrdersByDivision and then loop to load the orders:

ref.child('OrdersByDivision/Division 1').on('child_added', function(snapshot) {
    ref.child('Orders').child(snapshot.key).once('value', function(order) {
        console.log(order.val());
    });
});

Most developers that have done web development without Firebase are afraid this pattern will be slow. But given that Firebase retrieves the items over an already established connection, this is actually quite fast. See Speed up fetching posts for my social network app by using query instead of observing a single event repeatedly