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).