Re: Limiting the operations that client-side code can perform upon its database backend's artifacts

> rjuju123@gmail.com wrote:
>
>> bryn@yugabyte.com wrote:
>>
>>>> hjp-pgsql@hjp.at wrote:
>>>>
>>>>> rjuju123@gmail.com wrote:
>>>>>
>>>>>> bryn@yugabyte.com wrote:
>>>>>>
>>>>>> [Bryn] My demo seems to show that when a program connects as "client", it can perform exactly and only the
databaseoperations that the database design specified. Am I missing something? In other words, can anybody show me a
vulnerability?
>>>>>
>>>>> [Julien] What exactly prevents the client role from inserting e.g.
>>>>>
>>>>> - 'robert''); drop table students; --'
>>>>
>>>> [Peter] It can do this but it won't do any harm since the client role doesn't have permission to drop the table.
>
> [Julien] For the record. it's a reference to [XKCD's "Little Bobby Tables" cartoon]. Both of you are saying it's
harmlessbecause you're assuming that only the client role may read the data and act on it, but the whole point of SQL
injectionis to try to do actions that the role you have access to can't already do. And that's just a few out of dozens
ofexamples of how having a role connected to the database can do harm. 
>
>>>>> [Julien]
>>>>> - millions of 'cat' rows
>>>>> - millions of 1GB-large rows
>>>>
>>>> [Peter] That depends on "the database operations that the database design specified", but if the client role is
supposedto be able to insert data, you can't really prevent it from inserting non-sensical or enormous data. You can
encapsulatethe insert functionality in a function or procedure and do some sanity checks there. But automatically
distinguishingbetween legitimate use and abuse is generally not simple. 
>
> [Julien] Which is exactly what was the idea behind Tom's "if you don't trust another session that is running as your
userID,you have already lost". 
>
>>>>> [Julien] or just keep sending massive invalid query texts to fill the logs, or just trying to connect until
there'sno available connection slots anymore, and then keep spamming the server thousands of time per second to try to
opennew connections, or ...? 
>>>>
>>>> [Peter] There are often several layers of defense. The database frequently won't be accessible from the open
internet(or even the company network) directly. Only a middle tier of application servers running vetted client code
willconnect directly. Even those servers may not be accessible directly to end users. There may be a layer of proxy
serversabove them. Each of these layers may implement additional checks, rate limits and monitoring. 
>
> [Julien] If no one has direct SQL access to the database, then there's no problem with a role being able to
pg_terminate_backend()session for the same role, and this thread shouldn't exist to begin with. 
>
>> [Bryn] I'm afraid that I didn't see this from you until I'd already replied to Julien's turn in this thread. Sorry
thatI caused thread divergence. Thanks, Peter, for addressing the contribution(s) that other tiers in the stack make
(anduniquely are able to make) in order to deliver the intended application functionality to the end user. 
>
> [Julien] Your whole argument in your other email was:
>
>> [Bryn] Anyway... this kind of denial of service discussion is way outside the scope of what I addressed.
>
> [Julien] which looks like in total contradiction with your original email:
>
>> Am I missing something? In other words, can anybody show me a vulnerability?
>
> [Julien] Again, don't give SQL access to untrusted users and you will avoid a lot of problems, including someone
abusingpg_terminate_backend(). 

I fear that you and I, Julien, are talking past each other. That's probably my fault. Any maybe there's no hope of
rescuenow. 

My brand new thread, started here:

https://www.postgresql.org/message-id/3D119733-6784-4E84-98E4-5124E69D43F9@yugabyte.com

has nothing whatsoever to do with pg_terminate_backend(). The thread that I started here:

https://www.postgresql.org/message-id/10F360BB-3149-45E6-BFFE-10B9AE31F1A6@yugabyte.com

to ask about pg_terminate_backend() reached a nice "case closed" for me because I filled an embarrassing hole in my
understanding.I see now that, in a database of interest, I can revoke execute on the "killer" proc and grant it to any
rolethat needs it. Doing this is nothing other than following the principle of least privilege. It's liberating to know
that"Thing X" that you don't need can be made impossible. And so much nicer than applying the "what-about-ism"
approach:"Thing X" brings some risks. But so what? "Thing Y", and its legion cousins, bring risks too—so don't worry
aboutpreventing "Thing X". 

Now back to my new thread. I interpreted what Tom wrote to mean that he flatly rejected the idea that a database design
waspossible that prevented a client session that authorized as a role, that's designed for that purpose, from dropping
tablesand otherwise arbitrarily corrupting stuff. I expect that I completely misunderstood his point. But, anyway,
that'swhat 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
preventedfrom inserting mixed or upper case data, updating existing data, or deleting existing data. Rather (as your
BobbyTables 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.