4 ms·

> 3. I do think it is a big surface area. It can be reduced and, in this thread, I have never stated you cannot reduce it at all. No, but you did state, "I thi

> 3. I do think it is a big surface area. It can be reduced and, in this thread, I have never stated you cannot reduce it at all. No, but you did state, "I think exposing the entire database to users is a pretty big surface area to hand out." without qualification. Owing to the ambiguities of human language I never know for sure what people are saying online, but it seemed plausible to me that you thought the surface area couldn't be reduced at all. I welcome your clarification, so thank you for that. > 6. I did not state [people don't need all the power of SQL]. Well, you did state, "We don't need all of the power of SQL." Sorry, but I took "we" to be "all of us." Perhaps "we" meant just you and your colleagues for your particular use-case. Again, I would be grateful for clarification though you're not obliged to provide it. > I still don't buy that for a real-world dataset, the security engineering would not be onerous relative to a DSL. Let's get down to brass tacks. The "security engineering" which I don't think would be onerous comprises: - basic data types - custom data types - custom domains - primary key constraints - foreign key constraints - check constraints - triggers - procedures - views - Row Based Access Controls (RBAC) - Row Level Security (RLS) - recource limits (e.g. statement timeouts) Do you still consider these to be onerous? > and you haven't provided anything concrete to counter that Let me ask you this. Take your use-case of "a multi-tenant database with sensitive information in it that would be bad if somehow another user was able to access it." I don't know the details so for all I know it could be something simple. In that case, it could be handled with something like what's depicted in this demo. https://asciinema.org/a/AVEDmoFRlciDxpXx3PYk1ERXy https://asciinema.org/a/AVEDmoFRlciDxpXx3PYk1ERXy It's just 4 lines: grant all on tenant to tenant; -- use RBAC for the table create policy tenant on tenant as restrictive for all using (id = current_setting('session.id')::uuid); -- RLS specific policy create policy global on tenant as permissive for all using (true); -- RLS general policy alter table tenant enable row level security; -- Turn on RLS for the table Granted, your use-case probably isn't this simple. Then again, we still have 196 lines to work with before we hit the dreaded 200. It's been a long day and this thread is getting awful deep, so I don't expect you to respond. If you'd like to continue it in different channel (e.g., GitHub discussions) I'd be happy to oblige, however. Peace.

valenterry 3y ago
I actually like and use database security features. However, there are generally two problems with them: 1. There is a limit to the customization 2. They are generally harder to test and change Application code is usually optimized for both points. So for me, I actually prefer a combination: use rough database security features to reduce the blast-radius of a bug in the application code. And use application code for everything else, since it's easy to customize, easy to understand, independent of the database technology and very easy to test.
- dventimi 3y ago
  > I actually like and use database security features. However, there are generally two problems with them: > 1. There is a limit to the customization 2. They are generally harder to test and change Those aren't problems that I have.
sausagefeet 3y ago
Thank you for the response. > Well, you did state, "We don't need all of the power of SQL." Sorry, but I took "we" to be "all of us." Perhaps "we" meant just you and your colleagues for your particular use-case. The context of that sentence is talking about the specific proposal of the blog post, which states that their usecase did not require all of SQL. I can understand why it would be confusing as I switched to "we" in the next sentence. For my usecase, as well, we do not need all of the power of SQL. > Let's get down to brass tacks. The "security engineering" which I don't think would be onerous comprises > ... > Do you still consider these to be onerous? What you have listed is not an actual solution, just a list of things you'd need to solve it. The security engineering is how you actually use it. I could say the only thing you need for the DSL to SQL solution is a programming language, my list would be one item, but would that be reflective of the actual complexity in solving the problem? No. So I cannot say whether or not it is onerous. Additionally, the solution I mentioned is around 200 SLOC, but it has another 200 SLOC of tests, so how to test this is a valid question as well. My tests also don't require a database to with data in it to test, we just validate that the query looks like what we expect it to. > Granted, your use-case probably isn't this simple. Then again, we still have 196 lines to work with before we hit the dreaded 200. Thank you for the example, it clarifies in my head more how this would be implemented. Do you have an example of how this works if you are performing joins between tables? Does every table need to have some sort of user id in it directly for that to work?

valenterry 3y ago

I actually like and use database security features. However, there are generally two problems with them: 1. There is a limit to the customization 2. They are generally harder to test and change Application code is usually optimized for both points. So for me, I actually prefer a combination: use rough database security features to reduce the blast-radius of a bug in the application code. And use application code for everything else, since it's easy to customize, easy to understand, independent of the database technology and very easy to test.

dventimi 3y ago
> I actually like and use database security features. However, there are generally two problems with them: > 1. There is a limit to the customization 2. They are generally harder to test and change Those aren't problems that I have.

sausagefeet 3y ago

Thank you for the response. > Well, you did state, "We don't need all of the power of SQL." Sorry, but I took "we" to be "all of us." Perhaps "we" meant just you and your colleagues for your particular use-case. The context of that sentence is talking about the specific proposal of the blog post, which states that their usecase did not require all of SQL. I can understand why it would be confusing as I switched to "we" in the next sentence. For my usecase, as well, we do not need all of the power of SQL. > Let's get down to brass tacks. The "security engineering" which I don't think would be onerous comprises > ... > Do you still consider these to be onerous? What you have listed is not an actual solution, just a list of things you'd need to solve it. The security engineering is how you actually use it. I could say the only thing you need for the DSL to SQL solution is a programming language, my list would be one item, but would that be reflective of the actual complexity in solving the problem? No. So I cannot say whether or not it is onerous. Additionally, the solution I mentioned is around 200 SLOC, but it has another 200 SLOC of tests, so how to test this is a valid question as well. My tests also don't require a database to with data in it to test, we just validate that the query looks like what we expect it to. > Granted, your use-case probably isn't this simple. Then again, we still have 196 lines to work with before we hit the dreaded 200. Thank you for the example, it clarifies in my head more how this would be implemented. Do you have an example of how this works if you are performing joins between tables? Does every table need to have some sort of user id in it directly for that to work?