Re: superusers are members of all roles?

On Thu, Apr 7, 2011 at 6:49 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
> 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.
>
Is the solution possibly to assign positive entries on the basis of
the superuser being a member of all groups but require negative
entries to explicitly specify that they apply to superuser?

That would provide least surprise for the simplistic concept of
superuser - a user who can do anything any other user can - and allow
for superuser remote access to be restricted if desired.