On Tue, Sep 27, 2022 at 08:47:52PM -0700, Bryn Llewellyn wrote:
>
> 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?
An unprivileged role by definition can't ignore or drop table constraints,
that's not the problem here. The discussion should be "is having a direct SQL
access to the same role as my carefully written application is using
problematic", and the answer is yes.
> > I'm not convinced... that the authorization system can prevent an untrusted
> > user with a direct SQL access from actually hurting you.
>
> What do you mean by "untrusted"? Any person who is given the credentials to
> start a database session is trusted—even a person who 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 carefully designed
> and implemented API that's expressed exclusively with user-defined functions
> and procedures. And choose something to model that meets your criteria for
> realism. Then show me, using a self-contained code example, how a session
> that authorized as "client" can cause the hurt that concerns you. Notice that
> "hurt" must be taken to mean having the persistently stored data no longer
> satisfying as specified business rule. And not anything to do with denial of
> service based on unconstrained resource consumption.
You mean like if the application takes care of checking that the logged-in user
is allowed to insert data based on whatever application defined rules / user
profile, while the SQL role can simply insert data and/or call the carefully
written functions? Yes the data will be consistent, but if your role just
transferred money from an account to another that's not really the problem.