Обсуждение: non-superusers are allowed to drop the replication user, but are not allowed to alter or even create them, is that ok?

On Thu, Sep 30, 2021 at 3:37 PM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
>
> Hi All,
>
> While working on one of the internal projects I noticed that currently in Postgres, we do not allow normal users to
alterattributes of the replication user. However we do allow normal users to drop replication users or to even rename
itusing the alter command. Is that behaviour ok? If yes, can someone please help me understand how and why this is
okay.
>
> Here is an example illustrating this behaviour:
>
> supusr@postgres=# create user repusr with password 'repusr' replication;
> CREATE ROLE
>
> supusr@postgres=# create user nonsu with password 'nonsu' createrole createdb;
> CREATE ROLE
>
> supusr@postgres=# \c postgres nonsu;
> You are now connected to database "postgres" as user "nonsu".
>
> nonsu@postgres=> alter user repusr nocreatedb;
> ERROR:  42501: must be superuser to alter replication roles or change replication attribute
>
> nonsu@postgres=> alter user repusr rename to refusr;
> ALTER ROLE
>
> nonsu@postgres=> drop user refusr;
> DROP ROLE
>
> nonsu@postgres=> create user repusr2 with password 'repusr2' replication;
> ERROR:  42501: must be superuser to create replication users

I think having createrole for a non-super allows them to rename/drop a
user with a replication role. Because renaming/creating/dropping roles
is what createrole/nocreaterole is meant for.

postgres=# create user nonsu_nocreterole with createdb;
CREATE ROLE
postgres=# set role nonsu_nocreterole;
SET
postgres=> alter user repusr rename to refusr;
ERROR:  permission denied to rename role
postgres=> drop user refusr;
ERROR:  permission denied to drop role
postgres=>

Regards,
Bharath Rupireddy.

> On Sep 30, 2021, at 3:07 AM, Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
>
> While working on one of the internal projects I noticed that currently in Postgres, we do not allow normal users to
alterattributes of the replication user. However we do allow normal users to drop replication users or to even rename
itusing the alter command. Is that behaviour ok? If yes, can someone please help me understand how and why this is
okay.

The definition of CREATEROLE is a bit of a mess.  Part of the problem is that roles do not have owners, which makes the
permissionsto drop roles work differently than for other object types.  I have a patch pending [1] for the version 15
developmentcycle that fixes this and other problems.  I'd appreciate feedback on the design and whether it addresses
yourconcerns. 

[1] https://commitfest.postgresql.org/34/3223/

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

> On Sep 30, 2021, at 9:26 PM, Ashutosh Sharma <ashu.coek88@gmail.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
itseems 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