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

On Tue, Oct 25, 2011 at 8:39 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> On Mon, Oct 24, 2011 at 16:37, Keith Fiske <keith(at)omniti(dot)com> wrote:
>> 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:
>>
>>>> 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.
>
> This part I agree with - it makes sense for ALTER to set both flags
> when it enables superuser. It seems inconsistent now - even if it may
> be technically correct.
>
> If we don't change it, we should definitely accept a docs patch to
> clarify what happens, because I agree it's confusing.

There's a pre-existing catalog flag called rolcatupdate that works a
little bit like this, and we discussed making rolreplication behave
similarly. But we ended up with different semantics:

rhaas=# create user bob nosuperuser;
CREATE ROLE
rhaas=# create user fred nosuperuser;
CREATE ROLE
rhaas=# alter role fred superuser;
ALTER ROLE
rhaas=# create user harry superuser;
CREATE ROLE
rhaas=# select * from pg_authid where rolname in ('bob', 'fred',
'harry') order by rolname;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb |
rolcatupdate | rolcanlogin | rolreplication | rolconnlimit |
rolpassword | rolvaliduntil
---------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+---------------
bob | f | t | f | f | f
| t | f | -1 | |
fred | t | t | f | f | t
| t | f | -1 | |
harry | t | t | f | f | t
| t | t | -1 | |
(3 rows)

One difference is that, AFAIK, there's no DDL command to change
rolcatupdate separately from rolsuper. If we do decide to change the
behavior, we'd better carefully document that if you want to make
someone a superuser without giving them replication privileges (or
revoke their superuser status without revoking replication
privileges), you need to specify both ALTER TABLE options.

All in all I'm somewhat inclined to think we should just patch the
docs. 9.1 hasn't been out for very long, so maybe expectations aren't
too settled yet, but changing security-critical behavior in back
branches doesn't seem like a wonderful idea; and I think I mildly
prefer the current semantics to the proposed ones.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company