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

> david.g.johnston@gmail.com wrote:
>
>> bryn@yugabyte.com wrote:
>>
>> Is there anything that can be done to limit the scope of the ability to end up in a database like I'd thought would
bepossible? (A little test showed me that "set role" doesn't fire an event trigger.) 
>>
>> I do see that, as far as I've taken this test, "d2$mgr" is entirely impotent when the "current_database()" is "d1".
Isthis, maybe, just as good as it gets. I suppose I can live with what seems to me to be very odd as long as no harm
cancome of it. 
>
> Yes. In short, you can prevent a person from connecting to specific databases by ensuring the credentials they hold
onlyresolve successfully on those specific databases.  Both via connect privilege and pg_hba.conf can this be done. But
thatperson, while connected to any databases, can assume any roles the user name of the credentials they used are a
memberof.  Whether that is actually useful or not depends upon grants.  But in no case can you prevent them from, say,
examiningthe database catalog tables, or executing at least some limited SQL. 

Thanks. If nobody thinks that ending up as I showed is possible brings any kind of risk, then I’m happy to accept that.
Moregenerally, I’m a huge fan of the principle of least privilege, and (as far as it concerns what I asked about in
thisthread), 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
byconvention" 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
stateof the session that either "connect" or "set role" can bring about. There could be (in a future PG version) a
privilegethat governed "set role" in the present scenario. But I'm sure that there never will be. 

<aside>
You mentioned access to the catalog tables. This, too, belongs to the discussion of the principle of least privilege.
Thisaccess is not hard wired. Rather, it's just a manifestation of the default regime. I've prototyped a regime where
theprivileges that you need to access these tables (and other things too) are revoked from public and (for convenience)
aregranted to a single dedicated role. This means that it's easy to make it such that the role(s) that clients use to
connectcan't query the catalog—or, if you prefer, can access exactly and only those catalog items that they need to.
I'mpleased with how it worked out. And I'll pursue this regime further. 
</aside>