Re: "set role" semantics - Mailing list pgsql-general

adrian.klaver@aklaver.com wrote:
>
>> bryn@yugabyte.com wrote:
>
> Connecting to database and the role that is in play inside a session are two different things. Making them the same
wouldmake things [security define vs "security invoker"] go sideways. 

I said nothing to suggest that the role with which you connect to a database should be identical, always, to what
"current_role"returns. I speculated only that an enhanced privilege scheme that limited the target of "set role" to
thosethat have "connect" on the current database might be nice. I can't see that this would interfere with the
"security"notion for a subprogram. After all, it's already possible for role "r1" to invoke a "security definer"
subprogramowned by role "r2" when "r1" cannot "set role" to "r2". (This is probably the overwhelmingly common case.) 

I believe that I do understand the business of these two "security" kinds for user-defined functions and procedures
well.(And, yes, I know that a "set role" attempt in a "security definer" context causes a run-time error.) But thanks
formentioning the topic. There's a certain family resemblance between a "security definer" subprogram and "set role" in
thateach brings the outcome that the value that "current_role" returns might differ from the value that "session_user"
returns.And you can certainly arrange it so that a "security definer" subprogram is owned by a role that does not have
"connect"on the database where the subprogram exists. There is, though, a difference between the two paradigms in that
thesubprogram follows a stacked behavior so that when the subprogram that's first called exits, the "current_role"
valueis back where it was when the call was made. In contrast "set role" makes a durable change that you can see at the
"psql"prompt (mentioning this as an example of any client). And you can use "set role" to roam around, on demand, among
anynumber of roles in the set that allows you do do this in any order. This feels different—at least to me. 

Anyway, all this is moot (except in that thinking about it helps me to enrich my mental model) because the privilege
notionshere will never change. 

>> <aside>
>> You mentioned access to the catalog tables. This, too, belongs to the discussion of the principle of least
privilege.This access is not hard wired. Rather, it's just a manifestation of the default regime. I've prototyped a
regimewhere the privileges that you need to access these tables (and other things too) are revoked from public and (for
convenience)are granted to a single dedicated role. This means that it's easy to make it such that the role(s) that
clientsuse to connect can't query the catalog—or, if you prefer, can access exactly and only those catalog items that
theyneed to. I'm pleased with how it worked out. And I'll pursue this regime further. 
>> </aside>
>
> Have you actually done that and tried to run SQL statements? They are called system catalogs because they are used by
thesystem to get the information necessary to do things. Throwing restrictions on their access would be akin to pouring
sandin a gearbox, lots of strange behavior and then nothing. 

Yes I have actually done this. But rigorous testing remains to be done. I've implemented the scheme only within a
disciplinedbigger picture. I've mentioned the thinking that I'll sketch now, before, in other contexts. It's not
original.Many real-world applications follow it. I like to refer to it as the "hard shell" paradigm. Here, the
ownershipof the various artifacts that implement an application's database backend is spread among as many roles as you
please.For example, tables and their associated artifacts (like indexes, sequences, and so on) would have a different
ownerfrom the user-defined subprograms that implement the business functions that access the tables. Significantly,
client-sideaccess to this whole shooting match would be via one (or a few) dedicated "client" roles. Such a role has
only"connect" on the database that houses the application's backend. And it owns no schema and no objects in other
schemas.Rather, it's just the target for the "execute" privilege of those few of all the user-defined subprograms that
jointlydefine the database's API. The point (conforming to the principle of least privilege) is that sessions that
connectas "client" must not be allowed to do arbitrary SQL. Rather, they should be able to do only what has been
explicitly"white-listed" in by the encapsulation provided by the API-defining subprograms. 

The "lazy" approach for the roles that own the application's implementation and that rely on (some of) the artifacts
thatlive in "pg_catalog" is simply to revoke "all" from "public" for every one of these catalog items and, in the same
breath,to grant "all" (or what is needed) on each to a dedicated role (say, "d0$developer" in database "d0"). Then
"d0"developer"is granted to every role that owns any of the artifacts that jointly implement the application's database
backend.But, significantly, "d0$client_1" for as many such "client" roles as there are would NOT have "d0$developer"
grantedto it. 

A less lazy approach would be carefully to grant exactly and only what was needed to each artifact owning role to allow
itto do what it's designed to do—and nothing else. Of course, this would take significant effort (and not least
testing).And this might not be judged to be cost-effective.