Re: superusers are members of all roles?

On 04/07/2011 12:29 AM, Tom Lane wrote:
> Robert Haas<robertmhaas(at)gmail(dot)com> writes:
>> On Wed, Apr 6, 2011 at 7:54 PM, Stephen Frost<sfrost(at)snowman(dot)net> wrote:
>>> * Andrew Dunstan (andrew(at)dunslane(dot)net) wrote:
>>>> The surprising (to me) consequence was that every superuser was
>>>> locked out of the system. I had not granted them (or anyone) the
>>>> role, but nevertheless these lines took effect.
>>> As I recall, the way we allow superusers to set role to other roles is
>>> by considering the superuser to be a member of every role. Now, I agree
>>> that such an approach doesn't make sense for pg_hba consideration.
>> See bug #5763, and subsequent emails. Short version: Tom argued it
>> wasn't a bug; Peter and I felt that it was.
> The problem here is that if Andrew had had the opposite case (a
> positive-logic hba entry requiring membership in some group to get into
> a database), and that had locked out superusers, he'd be on the warpath
> about that too. And with a lot more reason.

In such a case I could add the superusers to the role explicitly, or
make the rule cover superusers as well. But as the situation is now, any
rule covering a group covers superusers, whether I want it to or not.
I'd rather have a choice in the matter (and it's clear I'm not alone in
that).

The introduction of hot standby has made this pattern more likely to
occur. It happened here because we have a bunch of users that are
allowed to connect to the standby but not to the master, and the rules I
was trying to implement were designed to enforce that exclusion.

cheers

andrew