On Thu, Sep 30, 2021 at 3:37 PM Ashutosh Sharma <ashu.coe...@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 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@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.
