On 11/9/22 15:23, Bryn Llewellyn wrote:
adrian.kla...@aklaver.com wrote:
b...@yugabyte.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.
So, I want it but not really.
<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.
All right that I get.
--
Adrian Klaver
adrian.kla...@aklaver.com
