> t...@sss.pgh.pa.us wrote:
>
>> b...@yugabyte.com writes:
>>
>>> rjuju...@gmail.com wrote:
>>>
>>> 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.
>
> Sure. That is called an application server. What we are is a SQL server, and
> that means that the API is SQL commands, and the authorization model is what
> the SQL spec says it is. [So]... any session authenticated as user X has the
> same privileges as any other session authenticated as user X, so there is not
> a lot of point in user X mounting defenses against user X. So I think the
> concerns you're expressing here would be better addressed at the next level
> up the stack.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Julien replied to the same message from me that Tom did thus:
> rjuju...@gmail.com wrote
>
> 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.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Peter Holzer replied to an earlier message fro be in this branching thread thus:
> hjp-pg...@hjp.at wrote:
>
> I think you did [misunderstand Tom]. What he was saying was that a database
> design which uses only one single role which both owns all the objects and
> executes all code on behalf of the user can not be secure. At the very least
> you need two roles: One which owns the objects and one which can only use the
> objects in a way allowed by the business logic and is not allowed to change
> that logic (This is the case you demonstrated.)
Yes, indeed it is. That was my aim.
> In many cases this should be even more fine-grained, and at the extreme end
> every user could actually have several roles, each with only the minimal
> privileges required.
>
> (I have often observed that we tend to throw away and build permission
> systems at every layer of the stack: The OS has a system of users and
> permissions. But the database server runs as a single user (postgres) which
> has access to all the data files. So it has to implement its own system of
> roles and permissions. Then an application developer comes along and writes
> an app which uses a single database role which has access to all the data.
It often is done like this. But it doesn't have to be done this way. This is
where a user-defined within-RDBMS functions and procedures, and so-called "run
authority" in the wider discourse, come to the rescue.
> So it again has to implement its own system of users and permissions...)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
*SUMMARY*
I'd better simply attempt to stop further discussion in this thread by saying
that no mutual understanding has been reached. I'm sad about this outcome. But
it would seem to be unfair to invite anybody to spend any more time on these
exchanges.
*MORE... ONLY IF YOU CAN BEAR TO READ IT*
First, lest a really awful misunderstanding lingers on, I must stress that,
about this from Tom:
> any session authenticated as user X has the same privileges as any other
> session authenticated as user X
well, yes. That statement of outcome is a tautology. I don't think that I wrote
anything to say that I thought otherwise. About the notion that Ms. Jones,
authorized as X can kill Ms. Smith's session authorized as X, and vice versa...
this seems now to be just a red herring. Either the designer wants this or they
don't. And if they don't, they can prevent it by a simple revocation of one
object privilege. It's been argued that some other evil doing can't be
prevented. Well, fair enough. But this doesn't seem to justify not preventing
any bad thing that *can* be prevented.
There was an era, now long gone, when an outfit had its own production RDBMS
(or a small few of these) in a locked and guarded room in the very same
building within which all human end users of such a system worked. In those
days, each human user was mapped one-to-one to its own within-RDBMS role. And
the application under the end-users fingertips connected directly to the RDBMS
by authentic
