On Tue, Sep 27, 2022 at 07:29:39PM -0700, Bryn Llewellyn wrote: > > Now back to my new thread. I interpreted what Tom wrote to mean that he > flatly rejected the idea that a database design was possible that prevented a > client session that authorized as a role, that's designed for that purpose, > from dropping tables and otherwise arbitrarily corrupting stuff. I expect > that I completely misunderstood his point. But, anyway, that's what I > responded to. > > Now it seems that you, Julien, are not convinced that the code that I showed > prevents a session that authorizes as "client" from dropping the table, owned > by "u1", where the data is. Nor are you convinced that a "client" session is > prevented from inserting mixed or upper case data, updating existing data, or > deleting existing data. Rather (as your Bobby Tables reference indicates) you > think that a cunning SQL injection attack can manage to do these bad things. > > Well... the challenge is yours now: prove your point with some working code. I'm convinced that that authorization system works as expected, what I'm not convinced of is that the authorization system can prevent an untrusted user with a direct SQL access from actually hurting you. So yes in your case maybe the "client" role cannot drop the showed table, but it can still insert nonsensical data, from a client point of view, or lead to outage or other problems without any difficulty, and there's nothing in the authorization system that can prevent that. I'm also not convinced that your demo is proving anything, as "inserting any only value made of non-uppercase characters in a single table " isn't really representative of any basic application, especially without knowing what that data will be used for. The only case this example could make sense would be a log application, and then a direct SQL access you can insert nonsensical or malicious data, depending on what the application will do with those data (which could lead to crash in the client application, or make it do thing it shouldn't do).
