pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Bryn Llewellyn <bryn@yugabyte.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
Subject: Re: "set role" semantics
Date: Wed, 9 Nov 2022 21:18:30 -0800
Message-ID: <4ef0194d-a851-de38-e4b1-c6b8cd1e4fe6@aklaver.com> (raw)
In-Reply-To: <F6ED5E28-382D-417E-835C-C701FD2B6861@yugabyte.com>
References: <FE0F7CE2-F1E8-40C8-A097-0CFDB0E14933@yugabyte.com>
	<CAKFQuwYxmv=GX_zAMNDO0U409m_kcD83T-TkyOMex_cRF+bXYQ@mail.gmail.com>
	<7F67C2B7-B054-408A-A2B4-2A9BF31B1855@yugabyte.com>
	<343d00c6-afee-3416-ee43-b3f9efa5bc0e@aklaver.com>
	<F6ED5E28-382D-417E-835C-C701FD2B6861@yugabyte.com>

On 11/9/22 15:23, Bryn Llewellyn wrote:
> 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 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.klaver@aklaver.com



view thread (14+ messages)  latest in thread

Message-ID: <4ef0194d-a851-de38-e4b1-c6b8cd1e4fe6@aklaver.com>
Permalink:  ../4ef0194d-a851-de38-e4b1-c6b8cd1e4fe6@aklaver.com/
Also on:    postgresql.org/message-id/4ef0194d-a851-de38-e4b1-c6b8cd1e4fe6@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, bryn@yugabyte.com, david.g.johnston@gmail.com
  Subject: Re: "set role" semantics
  In-Reply-To: <4ef0194d-a851-de38-e4b1-c6b8cd1e4fe6@aklaver.com>
  References: <FE0F7CE2-F1E8-40C8-A097-0CFDB0E14933@yugabyte.com> <CAKFQuwYxmv=GX_zAMNDO0U409m_kcD83T-TkyOMex_cRF+bXYQ@mail.gmail.com> <7F67C2B7-B054-408A-A2B4-2A9BF31B1855@yugabyte.com> <343d00c6-afee-3416-ee43-b3f9efa5bc0e@aklaver.com> <F6ED5E28-382D-417E-835C-C701FD2B6861@yugabyte.com> <4ef0194d-a851-de38-e4b1-c6b8cd1e4fe6@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox