On Sat, Sep 3, 2022 at 3:46 PM Jeff Davis <pg...@j-davis.com> wrote: > > You are now connected to database "rhaas" as user "rhaas". > > rhaas=# grant pg_read_all_settings to seer with set false; > > GRANT ROLE > > You've defined this in terms of the mechanics -- allow SET ROLE or not > -- but I assume you intend it as a security feature to allow/forbid > some capabilities. > > Is this only about the capability to create objects owned by a role > you're a member of? Or are there other implications?
I think there are some other implications, but I don't think they're anything super-dramatic. For example, you could create a group that's just for purposes of pg_hba.conf matching and make the grants both SET FALSE and INHERIT FALSE, with the idea that the members shouldn't have any access to the role; it's just there for grouping purposes. I mentioned one other possible scenario, with oncallbot, in the original post. I'm not sure whether thinking about this in terms of security capabilities is the most helpful way to view it. My view was, as you say, more mechanical. I think sometimes you grant somebody a role and you want them to be able to use SET ROLE to assume the privileges of the target role, and sometimes you don't. I think that primarily depends on the reason why you made the grant. In the case of a predefined role, you're almost certainly granting membership so that the privileges of the predefined role can be inherited. In other cases, you may be doing it so that the member can SET ROLE to the target role, or you may be doing it so that the member can administer the role (because you give them ADMIN OPTION), or you may even be doing it for pg_hba.conf matching. And because of this, I think it follows that there may be some capabilities conferred by role membership that you don't really want to convey in particular cases, so I think it makes sense to have a way to avoid conveying the ones that aren't necessary for the grant to fulfill its purpose. I'm not exactly sure how far that gets you in terms of building a system that is more secure than what you could build otherwise, but it feels like a useful capability regardless. -- Robert Haas EDB: http://www.enterprisedb.com