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 7:45 PM Bharath Rupireddy <
bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:

> On Thu, Sep 30, 2021 at 3:37 PM Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)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 alter attributes of the
> replication user. However we do allow normal users to drop replication
> users or to even rename it using 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(at)postgres=# create user repusr with password 'repusr' replication;
> > CREATE ROLE
> >
> > supusr(at)postgres=# create user nonsu with password 'nonsu' createrole
> createdb;
> > CREATE ROLE
> >
> > supusr(at)postgres=# \c postgres nonsu;
> > You are now connected to database "postgres" as user "nonsu".
> >
> > nonsu(at)postgres=> alter user repusr nocreatedb;
> > ERROR: 42501: must be superuser to alter replication roles or change
> replication attribute
> >
> > nonsu(at)postgres=> alter user repusr rename to refusr;
> > ALTER ROLE
> >
> > nonsu(at)postgres=> drop user refusr;
> > DROP ROLE
> >
> > nonsu(at)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.
>

Well, if we go by this theory then the CREATE ROLE command shouldn't have
failed, right?

--
With Regards,
Ashutosh Sharma.