Re: BUG #6264: Superuser does not have inherent Replication permission

On Sat, Oct 22, 2011 at 11:49 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Keith Fiske" <keith(at)omniti(dot)com> writes:
>> The documentation says replication is inherent to a superuser.
>
> What it actually says is that superusers get the replication privilege
> by default --- but you can create a superuser without that.  If you
> see a place that says something different, please point it out
> specifically so we can improve it.

From the documentation
"...If not specified, NOREPLICATION is the default for all roles
except superusers..."

From the 9.1 release notes:
"...Previously only superusers could initiate replication connections;
superusers still have this permission by default..."

Reading through all the other options, none of them state anything
special about superuser defaults. As stated above, if one were first
reading this, one would assume that a superuser would have replication
permission as default, no matter if it was given superuser via ALTER
ROLE or CREATE ROLE. If you're going to start saying which permissions
a superuser has and doesn't have by default, you should be consistent
with all of them and clearly state how and when they are given. Ex:
superusers inherently have CREATEROLE and CREATEDB, but not LOGIN.
And especially reading the release notes the use of the word "still"
would indicate that it works the same as it did in 9.0. It's what
caused my much of my confusion in this whole thing.

When I upgraded my 9.0.4 system to 9.1 and couldn't get streaming
replication to work, I saw the above and began to assume this was a
regression of the "replication" role name issue since it gave the same
error that that bug gave and things were working fine in 9.0.4. It was
only after I created a brand new superuser and then did a \du that I
noticed the replication permission was tacked on separately and only
during role creation. An upgrade from 9.0 to 9.1 does not account for
SR users and the release notes should probably reflect this, NOT state
that superusers still have the permission by default. They don't.

>
>> After testing
>> several times, I can assure you it is not.
>
> Please show your test case.  It looks to me like it works as expected:
>
> regression=# create user foo superuser;
> CREATE ROLE
> regression=# create user foo2 superuser noreplication;
> CREATE ROLE
> regression=# select rolname,rolsuper,rolreplication from pg_authid ;
>  rolname  | rolsuper | rolreplication
> ----------+----------+----------------
>  postgres | t        | t
>  foo      | t        | t
>  foo2     | t        | f
> (3 rows)
>
>

If you create a non-super user then ALTER them to be one, they don't
have replication as default. This either needs to be clarified in the
documentation or the ALTER should give the same defaults as the
CREATE. Whether it's inherent like CREATEDB or a separate permission,
I don't think matters and is a whole other argument. But things should
be consistent in some manner.

>> If you create a user as a NONsuperuser,
>> then later ALTER them to be one, they will NOT have the replication
>> permission and cannot be used as a replication user until you explicitly
>> grant that permission.
>
> That doesn't sound to me like a bug.  These flags are independent, we
> just provide a certain default at role creation time.
>

That is not what the documentation as read would lead people to
believe. I'd be more than happy to help with clarifying the
documentation myself if needed. Just let me know how.

>                        regards, tom lane
>

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.