Re: 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.