On Thu, Sep 30, 2021 at 7:45 PM Bharath Rupireddy < bharath.rupireddyforpostg...@gmail.com> wrote:
> 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. > Well, if we go by this theory then the CREATE ROLE command shouldn't have failed, right? -- With Regards, Ashutosh Sharma.