4 ms·

I once asked the same question on Security Stack Exchange. It has some interesting responses, if you’re curious what security people say. Edit: totally forgot

by 9dev 3y ago

I once asked the same question on Security Stack Exchange. It has some interesting responses, if you’re curious what security people say. Edit: totally forgot to add the actual link. Here goes: https://security.stackexchange.com/questions/229954/why-cant-i-just-let-customers-connect-directly-to-my-database https://security.stackexchange.com/questions/229954/why-cant...

valenterry 3y ago
Can you find the link for me?
- dventimi 3y ago
  Unsurprisingly, the answers to that security question including the accepted answer, are full of mistakes. This is such a sad state of affairs.
  - 9dev 3y ago
    I accepted that particular answer because it was balanced and comprehensive; I still think there’s a way to do this right that provides lots of benefits, namely something similar to what TFA is describing, that is: a wrapper layer that speaks SQL and applies ACLs and other limiters before passing the query to the database, potentially even translating between a virtual, exposed schema, and the actual database. The key advantage here being the ability to package this into a library, instead of everyone and their mother rolling their own.
    - dventimi 3y ago
      I think that's funny because personally I DIDN'T find that answer to be comprehensive or balanced. By my lights, it seems to go out of its way to ignore the affordances modern databases offer to protect against abuse. I found the following paragraph from the answer to be especially immeasured. "One of the most common vulnerabilities on web applications is SQL Injection, and you are giving a SQL console to your users. You are giving them a wide variety of guns, lots of bullets, and your foot, your hand, your head... And some of them don't like you." No, you are not giving your users a wide variety guns and bullets and hands and heads. You DO have some measure of control in the amount of power available to users. I don't consider it comprehensive or balanced not to address that fact in the answer.
    - dventimi 3y ago
      As for the idea itself, I believe there IS a recipe for doing this right: - Role Based Access Permissions (RBAC) for course-grained control over database objects (tables, procedures, etc.) - Row Level Security (RLS) for fine-grained control over data items (e.g. multinenant) For correctness: - custom data types - custom domains - check constraints - triggers - procedures For loose coupling: - views - procedures For QoS: - quotas (timeouts, row limits, data limits) That gets you most of the way there. You would still want a connection pooler and a rate limiter, of course, outside the DB.

by 9dev 3y ago

valenterry 3y ago

Can you find the link for me?

dventimi 3y ago
Unsurprisingly, the answers to that security question including the accepted answer, are full of mistakes. This is such a sad state of affairs.
- 9dev 3y ago
  I accepted that particular answer because it was balanced and comprehensive; I still think there’s a way to do this right that provides lots of benefits, namely something similar to what TFA is describing, that is: a wrapper layer that speaks SQL and applies ACLs and other limiters before passing the query to the database, potentially even translating between a virtual, exposed schema, and the actual database. The key advantage here being the ability to package this into a library, instead of everyone and their mother rolling their own.
  - dventimi 3y ago
    I think that's funny because personally I DIDN'T find that answer to be comprehensive or balanced. By my lights, it seems to go out of its way to ignore the affordances modern databases offer to protect against abuse. I found the following paragraph from the answer to be especially immeasured. "One of the most common vulnerabilities on web applications is SQL Injection, and you are giving a SQL console to your users. You are giving them a wide variety of guns, lots of bullets, and your foot, your hand, your head... And some of them don't like you." No, you are not giving your users a wide variety guns and bullets and hands and heads. You DO have some measure of control in the amount of power available to users. I don't consider it comprehensive or balanced not to address that fact in the answer.
  - dventimi 3y ago
    As for the idea itself, I believe there IS a recipe for doing this right: - Role Based Access Permissions (RBAC) for course-grained control over database objects (tables, procedures, etc.) - Row Level Security (RLS) for fine-grained control over data items (e.g. multinenant) For correctness: - custom data types - custom domains - check constraints - triggers - procedures For loose coupling: - views - procedures For QoS: - quotas (timeouts, row limits, data limits) That gets you most of the way there. You would still want a connection pooler and a rate limiter, of course, outside the DB.