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

> rjuju123@gmail.com wrote:
>
> bryn@yugabyte.com wrote:
>>
>> Now back to my new thread. I interpreted what Tom wrote to mean that he flatly rejected the idea that a database
designwas possible that prevented a client session that authorized as a role, that's designed for that purpose, from
droppingtables 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
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.
>
> I'm convinced that that authorization system works as expected, what I'm not convinced of is that the authorization
systemcan 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
leadto outage or other problems without any difficulty, and there's nothing in the authorization system that can
preventthat. 
>
> I'm also not convinced that your demo is proving anything, as "inserting any only value made of non-uppercase
charactersin a single table" isn't really representative of any basic application, especially without knowing what that
datawill 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
nonsensicalor malicious data, depending on what the application will do with those data (which could lead to crash in
theclient application, or make it do thing it shouldn't do). 

My example wasn't meant in any way to be realistic. I'm sorry if I didn't make that clear from the outset. It was meant
onlyto illustrate the principles. For example, the "lower case only" rule was meant to be an example of *any* data
rule.Just like the write-once-read-many auto-generated surrogate primary key rule. Can you show me how those data
rules,unrealistic as you might think them to be, can be violated? 

> I'm not convinced... that the authorization system can prevent an untrusted user with a direct SQL access from
actuallyhurting you. 

What do you mean by "untrusted"? Any person who is given the credentials to start a database session is trusted—even a
personwho can connect as a superuser and do untold harm. So focus on a person who has the credentials to connect as
"client"in my example. But imagine a design that exposes functionality to "client" sessions exclusively through a
carefullydesigned and implemented API that's expressed exclusively with user-defined functions and procedures. And
choosesomething to model that meets your criteria for realism. Then show me, using a self-contained code example, how a
sessionthat authorized as "client" can cause the hurt that concerns you. Notice that "hurt" must be taken to mean
havingthe persistently stored data no longer satisfying  as specified business rule. And not anything to do with denial
ofservice based on unconstrained resource consumption. 

If, when I review it, I can see how to change the code to remove the vulnerability, then you'll have learned something.
Onthe other hand, if you can show me a vulnerability that cannot be fixed, then I'll have learned something! I'm
selfishlymore interested in that second outcome because my overall mental model will have been improved.