Re: fixing CREATEROLE

On Mon, Nov 28, 2022 at 3:02 PM Mark Dilger
 wrote:
> Robert's patch tries to deal with the (possibly unwanted) role membership by setting up defaults to mitigate the effects, but that is more confusing to me than just de-conflating role membership from role administration, and giving role creators administration over roles they create, without in so doing giving them role membership.  I don't recall enough details about how hard it is to de-conflate role membership from role administration, and maybe that's a non-starter for reasons I don't recall at the moment.  I expect Robert has already contemplated that idea and instead proposed this patch for good reasons.  Robert?

"De-conflating role membership from role administration" isn't really
a specific proposal that someone can go out and implement. You have to
make some decision about *how* you are going to separate those
concepts. And that's what I did: I made INHERIT and SET into
grant-level options. That allows you to give someone access to the
privileges of a role without the ability to administer it (at least
one of INHERIT and SET true, and ADMIN false) or the ability to
administer a role without having any direct access to its privileges
(INHERIT FALSE, SET FALSE, ADMIN TRUE). I don't see that we can, or
need to, separate things any more than that.

You can argue that a grant with INHERIT FALSE, SET FALSE, ADMIN TRUE
still grants membership, and I think formally that's true, but I also
think it's just picking something to bicker about. The need isn't to
separate membership per se from administration. It's to separate
privilege inheritance and the ability to SET ROLE from role
administration. And I've done that.

I strongly disagree with the idea that the ability for users to
control defaults here isn't needed. You can set a default tablespace
for your database, and a default tablespace for your session, and a
default tablespace for new partitions of an existing partition table.
You can set default privileges for every type of object you can
create, and a default search path to find objects in the database. You
can set defaults for all of your connection parameters to the database
using environment variables, and the default data directory for
commands that need one. You can set defaults for all of your psql
settings in ~/.psqlrc. You can set defaults for the character sets,
locales and collations of new databases.  You can set the default
version of an extension in the control file, so that the user doesn't
have to specify a version. And so on and so on. There's absolutely
scads of things for which it is useful to be able to set defaults and
for which we give people the ability to set defaults, and I don't
think anyone is making a real argument for why that isn't also true
here. The argument that has been made is essentially that you could
get by without it, but that's true of *every* default. Yet we keep
adding the ability to set defaults for new things, and to set the
defaults for existing things in new ways, and there's a very good
reason for that: it's extremely convenient. And that's true here, too.

-- 
Robert Haas
EDB: http://www.enterprisedb.com