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

On Thu, Oct 27, 2011 at 01:45:34PM -0400, Robert Haas wrote:
> 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:

[ALTER ROLE sets rolcatupdate = rolsuper when changing rolsuper.]

> 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.

Let's look at the behavior of DDL-exposed access constraints for precedent. We
currently have three paradigms for applying access control to superusers:

1. Settings that affect superusers and regular users identically. These include
ALTER ROLE ... LOGIN | VALID UNTIL.

2. Rights that superusers possess implicitly and irrevocably; the actual setting
recorded in pg_authid or elsewhere has no effect. These include GRANT ... ON
TABLE and ALTER ROLE ... CREATEDB | CREATEROLE.

3. ALTER ROLE ... REPLICATION is very similar to #1, except that CREATE ROLE
... SUPERUSER implies CREATE ROLE ... SUPERUSER REPLICATION.

I think we should merge #3 into #2; nothing about the REPLICATION setting
justifies a distinct paradigm. On a clean slate, #1 might be an equally
suitable fit. However, #2 provides better backward compatibility with 9.0.

> 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.

The implication of my proposal is to remove the possibility of creating a
superuser that cannot connect for replication; users so restricted under 9.1.1
would suddenly be able to make replication connections in 9.1.2. Of course,
they could already "ALTER ROLE myself REPLICATION". On that account, no secure
system would quietly become insecure following such an upgrade.

nm