> On 14-Sep-2022, t...@sss.pgh.pa.us wrote:
>
> …. Therefore, if you don't trust another session that is running as your
> userID, you have already lost. That session can drop your tables, or corrupt
> the data in those tables to an arbitrary extent, and the SQL permissions
> system will not squawk even feebly… So if you're not happy with this hazard,
> you should not be accepting the idea that actors you don't trust are allowed
> to submit queries under the same userID as you. And if you're using a
> client-side software stack that forces that situation on you, it's time to
> look for another one.
>
> Or in other words, I flatly reject the claim that this:
>
>> b...@yugabyte.com wrote:
>>
>> It's common to design a three tier app so that the middle tier always
>> authorizes as just a single role—say, "client"—and where the operations that
>> "client" can perform are limited as the overall design specifies.
>
> is in any way sane or secure. There is not very much that the database server
> can do to clean up after insecure client-side stacks.
*BACKGROUND*
I'm starting a new thread here. What I wrote, and Tom's response, are taken
from a longish thread that I started with the subject "Is it possible to stop
sessions killing each other when they all authorize as the same role?", here:
www.postgresql.org/message-id/10f360bb-3149-45e6-bffe-10b9ae31f...@yugabyte.com
That thread is "case closed" now. (My question arose from my basic
misunderstanding of what's hard-wired and what is simply a default privilege
regime that can be changed. And then I compounded my embarrassment by revoking
"execute from public" on a "pg_catalog" function when "current_database()" had
one value—and then not seeing the effect of this when "current_database()" had
a different value.)
I wandered off topic with a claim about three tier app design. And that
prompted Tom's response here:
https://www.postgresql.org/message-id/3100447.1663213208%40sss.pgh.pa.us
*ANYWAY...*
Tom's "I flatly reject" has been troubling me for the past couple of weeks. I
wonder if what I wrote was unclear. I'll try a different way. First, w.r.t.
Tom's
> the main point of a database is to store your data
I think that more needs to be said, thus:
« The main point of a database is to store your data, to keep it in compliance
with all the specified data rules, and to allow authorized client-side code to
modify the data by using only a set of specified business functions. »
This implies a carefully designed within-database regime that takes advantage
of established notions: for encapsulating the implementation of business
functions; and for access control. This, in turn, implies a minimum of two
distinct roles: one to own the entire implementation. And another to allow
exactly and only the specified business functions to be performed by
client-side code. In a real use case, user-defined functions or procedures
define the business function API. And there'd be typically several roles that
share the implementation and that take advantage of access control notions
among themselves. My code example, below, reduces this paradigm as far as I
could manage to allow a convincing demo of the principles. It relies on this:
— People who implement client-side code to access the database are given *only*
the credentials to connect as one particular user, "client", that exposes the
business function API.
— All other connect credentials, including but not at all limited to superuser
credentials, are kept secret within a manageably small community of server-side
engineers.
— Self-evidently, this relies on carefully designed and strictly implemented
human practices. But so, too, does any human endeavor where security matters.
In our domain, this implies that the overall design has a carefully written
prose specification and that the development shop delivers a set of install
scripts. Then a trusted person whose job is to administer the deployed app
scrutinizes the scripts and runs them. In the limit, just a single person knows
the deployment site passwords and can set "rolcanlogin" to "false" for every
role that owns the implementation artifacts once the installation is done.
My demo seems to show that when a program connects as "client", it can perform
exactly and only the database operations that the database design specified.
Am I missing something? In other words, can anybody show me a vulnerability?
*THE DEMO*
The code example models the simplest form of "hard shell encapsulation" that I
could manage.
(I now realize that, with some very manageable effort, I can revoke all
privileges on every object in the "pg_catalog" schema from public and then
re-grant as needed to whatever roles need them—following the famous principle
of least privilege. So none would be granted to "client" with the result that
it can't see metadata about anything. A prose document would suffice for
communicating what client-side engin
