principle-of-least-privilege-db-design-1

I used this design on a recent project and wanted to write up my thoughts.

This approach was taken as the project was a small scale web application with quick time scales. I'd previously worked on several projects which took a generic web-api-db pattern, even when there was no plan or ability to scale or separate the components out, so the implementation increased complexity for little gain. Also principle of least privilege had come up in some security reviews, database permissions not really being considered early on.

I wanted to see if I could cut out the API components, that added an additional layer of mainly boilerplate code, without resorting to a monolith design. This also reduced the complexity of the infrastructure and networking. Experience from looking at database permissions made me aware that users/roles/schemas permissions can be set very fine grained, providing assurances that connections can be locked down to specific tables/operations (e.g. SELECT/UPDATE only, no DELETE)

You could take this  further and go for full microservice split, with internal/external each having a separate worker and communicating via limited exposed API endpoints, but for this project that wasn't really necessary and I was sick of designs dictated by patterns rather than needs.

Scenario

You have two applications:

  • external-web
    • Public site used by unauthenticated users and exposed to the internet
    • Allows users to submit application data to be processed, with a limited view of previously submitted application data
    • High risk, don't want users to potentially view other users application data or change details
    • Higher usage than internal (public facing, unpredictable traffic)
  • internal-site
    • Internal site used by authenticated users and IP restricted
    • Allows users to process applications
    • Lower risk, but still don't want users to be able to perform actions like deleting records or submitting applications
    • Low number of active users (small team)

Proposed solution:

  • Split the data stores, so external-web and internal-web have their own stores, with external-web only holding data as long as necessary
  • Use permissions to prevent each application from doing anything other than the minimum they need on their stores (principle of least privilege)
  • Use a worker application, not exposed or directly connected to either application, to move data between the two
  • Use either a special API or function to allow external-web to query historic data with limited access, so it cannot query the entire store

Thoughts on outcome

Pros

  • Simple and low number of components (moving parts that could go wrong)
  • Low infrastructure requirements
  • Still able to scale internal/external independently
  • Less code and complexity
  • Public facing site only has access to data in transit, not large amounts of long term data

Cons

  • Public facing application has access to database (even if limited to select/updates)
  • Unable to scale external/internal API independently from sites
  • Worker unable to scale independently of external/internal
  • Lose a lot of relational integrity from copying between stores if using relational stores

Links