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>
To: 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 12:53:51 -0800
Message-ID: <343d00c6-afee-3416-ee43-b3f9efa5bc0e@aklaver.com> (raw)
In-Reply-To: <7F67C2B7-B054-408A-A2B4-2A9BF31B1855@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>

On 11/9/22 12:31, Bryn Llewellyn wrote:

> Thanks. If nobody thinks that ending up as I showed is possible brings any kind of risk, then I’m happy to accept that. More generally, I’m a huge fan of the principle of least privilege, and (as far as it concerns what I asked about in this thread), its following aspect:
> 
> If you consider these two alternatives:
> 
> Alt. #1: Some ability that you do not need (and therefor don’t want) is simply not available.
> 
> Alt. #2: That unwanted ability is available, but reasoning shows that it’s harmless.
> 
> then Alt. #1 is preferable.
> 
> But I do see that I can’t get Alt #1 here.
> 
> In my actual use case, every role apart from "postgres", and its non-superuser deputy with "create role" and "createdb", will be confined at "connect time" to exactly one database. And those of these that will be able to "set role" will be able to do this only to other roles that are also confined (at "connect" time) to the same database. Moreover, I cannot worry about what sessions that authorize as "postgres" or its deputy can do because the former is unstoppable" and the latter is dangerously powerful in the wrong human hands. There's always a need for trusted humans who, if they betray that trust, can do untold harm. In this case, they must understand the design of the "multitenancy by convention" scheme and must be committed to honoring it. So, sure enough, reasoning tells me that my plan is sound.
> 
> Nevertheless, it does seem to be unfortunate to take the mechanics of "connect" as salient rather than the resulting state of the session that either "connect" or "set role" can bring about. There could be (in a future PG version) a privilege that governed "set role" in the present scenario. But I'm sure that there never will be.

Connecting to database and the role that is in play inside a session are 
two different things. Making them the same would make things like from here:

https://www.postgresql.org/docs/current/sql-createfunction.html

[EXTERNAL] SECURITY INVOKER
[EXTERNAL] SECURITY DEFINER

     SECURITY INVOKER indicates that the function is to be executed with 
the privileges of the user that calls it. That is the default. SECURITY 
DEFINER specifies that the function is to be executed with the 
privileges of the user that owns it.

     The key word EXTERNAL is allowed for SQL conformance, but it is 
optional since, unlike in SQL, this feature applies to all functions not 
only external ones.

go sideways.



> 
> <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.


-- 
Adrian Klaver
adrian.klaver@aklaver.com






view thread (14+ messages)  latest in thread

Message-ID: <343d00c6-afee-3416-ee43-b3f9efa5bc0e@aklaver.com>
Permalink:  ../343d00c6-afee-3416-ee43-b3f9efa5bc0e@aklaver.com/
Also on:    postgresql.org/message-id/343d00c6-afee-3416-ee43-b3f9efa5bc0e@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: <343d00c6-afee-3416-ee43-b3f9efa5bc0e@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>

* 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