Re: "set role" semantics

adrian(dot)klaver(at)aklaver(dot)com wrote:
>
>> bryn(at)yugabyte(dot)com wrote:
>
> Connecting to database and the role that is in play inside a session are two different things. Making them the same would make 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 those that 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" subprogram owned 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 for mentioning the topic. There's a certain family resemblance between a "security definer" subprogram and "set role" in that each 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 the subprogram follows a stacked behavior so that when the subprogram that's first called exits, the "current_role" value is 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 any number 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 notions here 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 regime where 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 clients use to connect can't query the catalog—or, if you prefer, can access exactly and only those catalog items that they need 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 the system to get the information necessary to do things. Throwing restrictions on their access would be akin to pouring sand in 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 disciplined bigger 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 ownership of 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 owner from the user-defined subprograms that implement the business functions that access the tables. Significantly, client-side access 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 jointly define the database's API. The point (conforming to the principle of least privilege) is that sessions that connect as "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 that live 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" granted to it.

A less lazy approach would be carefully to grant exactly and only what was needed to each artifact owning role to allow it to 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.