Horton

Re: fixing CREATEROLE

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Mark Dilger <mark.dilger@enterprisedb.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2022-11-23T18:11:01Z
Lists: pgsql-hackers 
Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Nov 23, 2022 at 12:36 PM Mark Dilger
> <mark.dilger@enterprisedb.com> wrote:
>> Yes, this all makes sense, but does it entail that the CREATE ROLE command must behave differently on the basis of a setting?

> Well, we certainly don't HAVE to add those new role-level properties;
> that's why they are in a separate patch. But I think they add a lot of
> useful functionality for a pretty minimal amount of extra code
> complexity.

I haven't thought about these issues hard enough to form an overall
opinion (though I agree that making CREATEROLE less tantamount
to superuser would be an improvement).  However, I share Mark's
discomfort about making these commands act differently depending on
context.  We learned long ago that allowing GUCs to affect query
semantics was a bad idea.  Basing query semantics on properties
of the issuing role (beyond success-or-permissions-failure) doesn't
seem a whole lot different from that.  It still means that
applications can't just issue command X and expect Y to happen;
they have to inquire about context in order to find out that Z might
happen instead.  That's bad in any case, but it seems especially bad
for security-critical behaviors.

			regards, tom lane

Commits

  1. Add new GUC createrole_self_grant.

  2. Restrict the privileges of CREATEROLE users.

  3. Pass down current user ID to AddRoleMems and DelRoleMems.

  4. Refactor permissions-checking for role grants.

  5. Improve documentation of the CREATEROLE attibute.

  6. Make role grant system more consistent with other privileges.