How We Turn Authorization Logic into SQL

Re:How We Turn Authorization Logic into SQL by SPBS on Wednesday November 24, 2021 @12:38AM (#29327217)

Oh okay, so you can store authorization data in a table mapping user_ids to location_ids right? Then instead of using `IN (big_list)` (which can't use indexes) you join on the mapping table (which can use indexes). Something like

    CREATE TABLE locations (
        location_id INT PRIMARY KEY,
        rating INT
    );

    CREATE TABLE authorized_locations (
        user_id INT REFERENCES users (user_id),
        location_id INT REFERENCES locations (location_id)
    );
    CREATE INDEX authorized_locations_user_id_idx ON authorized_locations (user_id);
    CREATE INDEX authorized_locations_location_id_idx ON authorized_locations (location_id);

    SELECT AVG(rating) FROM locations JOIN authorized_locations USING (location_id) WHERE user_id = $1;

[ Parent | on HN ]

Re:How We Turn Authorization Logic into SQL by winrid on Wednesday November 24, 2021 @12:50AM (#29327272)

In (list) can use an index just fine in most databases. It's "not equal" that has trouble.

A better example would be:

Product {LocationID, Rating} User{id} UserLocations{UserID, locationID)

However, let's say you have an architecture where you have 100s of these "product" tables, since every piece of the app restricts access by location. So not only do you have Product but maybe you have FormResult{LocationID}, Feedback{LocationID} etc.

So for a service to find out what a user has access to, first they call the User service to get a list of locations, and then they pass the big list of ids to their own DB, whatever that DB is.

I guess this is one case where a monolith and good ol SQL would win. :)

[ Parent | on HN ]