Re: non-superusers are allowed to drop the replication user, but are not allowed to alter or even create them, is that ok?

> On Sep 30, 2021, at 9:26 PM, Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com> wrote:
>
> I'll take a look at this thread in detail to see if it addresses the issue raised here. Although from the first email it seems like the proposal is about allowing normal users to set some of the GUC params that can only be set by the superusers.

Right. The bit that you will be interested in patches 1, 19, 20, and 21:

Subject: [PATCH v8 01/21] Add tests of the CREATEROLE attribute.

While developing alternate rules for what privileges CREATEROLE has,
I noticed that none of the changes to how CREATEROLE works triggered
any regression test failures. This is problematic for two reasons.
It means the existing code has insufficient test coverage, and it
means that unintended changes introduced by subsequent patches may
go unnoticed. Fix that.

Subject: [PATCH v8 19/21] Add owners to roles

All roles now have owners. By default, roles belong to the role
that created them, and initdb-time roles are owned by POSTGRES.

This is a preparatory patch for changing how CREATEROLE works.

Subject: [PATCH v8 20/21] Give role owners control over owned roles

Create a role ownership hierarchy. The previous commit added owners
to roles. This goes further, making role ownership transitive. If
role A owns role B, and role B owns role C, then role A can act as
the owner of role C. Also, roles A and B can perform any action on
objects belonging to role C that role C could itself perform.

This is a preparatory patch for changing how CREATEROLE works.

Subject: [PATCH v8 21/21] Restrict power granted via CREATEROLE.

The CREATEROLE attribute no longer has anything to do with the power
to alter roles or to grant or revoke role membership, but merely the
ability to create new roles, as its name suggests. The ability to
alter a role is based on role ownership; the ability to grant and
revoke role membership is based on having admin privilege on the
relevant role or alternatively on role ownership, as owners now
implicitly have admin privileges on roles they own.

A role must either be superuser or have the CREATEROLE attribute to
create roles. This is unchanged from the prior behavior. A new
principle is adopted, though, to make CREATEROLE less dangerous: a
role may not create new roles with privileges that the creating role
lacks. This new principle is intended to prevent privilege
escalation attacks stemming from giving CREATEROLE to a user. This
is not backwards compatible. The idea is to fix the CREATEROLE
privilege to not be pathway to gaining superuser, and no
non-breaking change to accomplish that is apparent.

SUPERUSER, REPLICATION, BYPASSRLS, CREATEDB, CREATEROLE and LOGIN
privilege can only be given to new roles by creators who have the
same privilege. In the case of the CREATEROLE privilege, this is
trivially true, as the creator must necessarily have it or they
couldn't be creating the role to begin with.

The INHERIT attribute is not considered a privilege, and since a
user who belongs to a role may SET ROLE to that role and do anything
that role can do, it isn't clear that treating it as a privilege
would stop any privilege escalation attacks.

The CONNECTION LIMIT and VALID UNTIL attributes are also not
considered privileges, but this design choice is debatable. One
could think of the ability to log in during a given window of time,
or up to a certain number of connections as a privilege, and
allowing such a restricted role to create a new role with unlimited
connections or no expiration as a privilege escalation which escapes
the intended restrictions. However, it is just as easy to think of
these limitations as being used to guard against badly written
client programs connecting too many times, or connecting at a time
of day that is not intended. Since it is unclear which design is
better, this commit is conservative and the handling of these
attributes is unchanged relative to prior behavior.

Since the grammar of the CREATE ROLE command allows specifying roles
into which the new role should be enrolled, and also lists of roles
which become members of the newly created role (as admin or not),
the CREATE ROLE command may now fail if the creating role has
insufficient privilege on the roles so listed. Such failures were
not possible before, since the CREATEROLE privilege was always
sufficient.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company