On 2022-09-27 19:29:39 -0700, Bryn Llewellyn wrote:
> 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.
I think you did. 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). 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. So it again has to implement its own system of
users and permissions ...)
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"