Horton

Thread

  1. superusers are members of all roles?

    Andrew Dunstan <andrew@dunslane.net> — 2011-04-06T23:04:42Z

    I just hit this, which at least violated my sense of least astonishment, 
if it's not an outright bug:

After creating a role foo, I added to following lines to my (9.0) 
pg_hba.conf:

    local    all +foo           reject
    host     all +foo 0.0.0.0/0 reject

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.

If this is intended, it should at least be documented. But if it is 
intended then it's ugly anyway, IMNSHO, and we should change it.

cheers

andrew

  2. Re: superusers are members of all roles?

    Stephen Frost <sfrost@snowman.net> — 2011-04-06T23:54:06Z

    * Andrew Dunstan (andrew@dunslane.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.

> If this is intended, it should at least be documented. But if it is
> intended then it's ugly anyway, IMNSHO, and we should change it.

Perhaps the superuser-override should be moved to be at the 'set role'
level instead of setting things up such that the superuser is considered
a member of every role.  That would fix this but would require adding a
couple of additional special superuser checks, which isn't something to
do lightly, imv.

	Thanks,

		Stephen

  3. Re: superusers are members of all roles?

    Robert Haas <robertmhaas@gmail.com> — 2011-04-07T03:54:45Z

    On Wed, Apr 6, 2011 at 7:54 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * Andrew Dunstan (andrew@dunslane.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.

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

  4. Re: superusers are members of all roles?

    Josh Berkus <josh@agliodbs.com> — 2011-04-07T04:21:58Z

    > See bug #5763, and subsequent emails.  Short version: Tom argued it
> wasn't a bug; Peter and I felt that it was.

Add my vote: it's a bug.

Users who fall afoul of this will spend *hours* trying to debug this
before they stumble on the correct answer.  pg_hba.conf is confusing
enough as it is.

The only reason we don't get more bug reports on this is that not very
many users know about using group roles in pg_hba.conf (and few enough
users are using group roles in the first place).

If we're not going to fix this, then we need a big warning in the docs
and the pg_hba.conf file:

"NOTE: Please make sure that at least one rule in pg_hba.conf matches
superuser access before any reject rules"

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

  5. Re: superusers are members of all roles?

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-04-07T04:29:17Z

    Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Apr 6, 2011 at 7:54 PM, Stephen Frost <sfrost@snowman.net> wrote:
>> * Andrew Dunstan (andrew@dunslane.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.

Therefore, "fixing" this without introducing even-more-surprising
behaviors is going to be a very ticklish business.  I remain on the side
of the fence that says it's not a bug.

			regards, tom lane

  6. Re: superusers are members of all roles?

    Josh Berkus <josh@agliodbs.com> — 2011-04-07T04:45:48Z

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

Actually, I find that behavior surprising -- and undesirable -- too.
Enough so that I'm going to have to modify the pg_hba.conf on a couple
of production databases tommorrow.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

  7. Re: superusers are members of all roles?

    Andrew Dunstan <andrew@dunslane.net> — 2011-04-07T04:49:02Z

    
On 04/07/2011 12:29 AM, Tom Lane wrote:
> Robert Haas<robertmhaas@gmail.com>  writes:
>> On Wed, Apr 6, 2011 at 7:54 PM, Stephen Frost<sfrost@snowman.net>  wrote:
>>> * Andrew Dunstan (andrew@dunslane.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

  8. Re: superusers are members of all roles?

    Alastair Turner <bell@ctrlf5.co.za> — 2011-04-07T07:48:36Z

    On Thu, Apr 7, 2011 at 6:49 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
>
> On 04/07/2011 12:29 AM, Tom Lane wrote:
>>
>> Robert Haas<robertmhaas@gmail.com>  writes:
>>>
>>> On Wed, Apr 6, 2011 at 7:54 PM, Stephen Frost<sfrost@snowman.net>  wrote:
>>>>
>>>> * Andrew Dunstan (andrew@dunslane.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.

  9. Re: superusers are members of all roles?

    Andrew Dunstan <andrew@dunslane.net> — 2011-04-07T10:33:08Z

    
On 04/07/2011 03:48 AM, Alastair Turner wrote:
>>>
>>> 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.
>

I think that's just about guaranteed to produce massive confusion. +foo 
should mean one thing, regardless of the rule type. I seriously doubt 
that very many people who work with this daily would agree with Tom's 
argument about what that should be.

cheers

andrew

  10. Re: superusers are members of all roles?

    Christian Ullrich <chris@chrullrich.net> — 2011-04-07T11:33:48Z

    * Andrew Dunstan wrote:

> On 04/07/2011 03:48 AM, Alastair Turner wrote:

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

> I think that's just about guaranteed to produce massive confusion. +foo
> should mean one thing, regardless of the rule type. I seriously doubt
> that very many people who work with this daily would agree with Tom's
> argument about what that should be.

What about adding a second group syntax that only evaluates explicit 
memberships? That way, everyone could pick which behavior they liked 
better, and Alastair's suggestion could be done that way, too:

	host	all	*personae_non_gratae	0.0.0.0/0	reject
	host	all	+foo			0.0.0.0/0	md5

If, as Josh said, few users even know about the old syntax, there should 
not be much potential for confusion in adding a new one.

Additionally, most things that can be done with groups in pg_hba.conf 
can also be done using CONNECT privilege on databases.

-- 
Christian

  11. Re: superusers are members of all roles?

    Andrew Dunstan <andrew@dunslane.net> — 2011-04-07T12:01:16Z

    
On 04/07/2011 07:33 AM, Christian Ullrich wrote:
> * Andrew Dunstan wrote:
>
>> On 04/07/2011 03:48 AM, Alastair Turner wrote:
>
>>> 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?
>
>> I think that's just about guaranteed to produce massive confusion. +foo
>> should mean one thing, regardless of the rule type. I seriously doubt
>> that very many people who work with this daily would agree with Tom's
>> argument about what that should be.
>
> What about adding a second group syntax that only evaluates explicit 
> memberships? That way, everyone could pick which behavior they liked 
> better, and Alastair's suggestion could be done that way, too:
>
>     host    all    *personae_non_gratae    0.0.0.0/0    reject
>     host    all    +foo            0.0.0.0/0    md5
>
> If, as Josh said, few users even know about the old syntax, there 
> should not be much potential for confusion in adding a new one.

I thought about that. What I'd like to know is how many people actually 
want and use and expect the current behaviour. If it's more than a 
handful (which I seriously doubt) then that's probably the way to go. 
Otherwise it seems more trouble than it's worth.

>
> Additionally, most things that can be done with groups in pg_hba.conf 
> can also be done using CONNECT privilege on databases.

In my case this won't work at all, since what I need is to allow the 
group access on a hot standby but prevent it on the master, and the 
CONNECT privs will be the same on both. We also don't have negative 
privileges analogous to "reject" lines.

cheers

aqndrew

  12. Re: superusers are members of all roles?

    Stephen Frost <sfrost@snowman.net> — 2011-04-07T14:14:39Z

    * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> 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.

I disagree about this.  I don't feel that the 'superuser is a member of
every role' behavior is what's really crucial here, it's that a
superuser can 'set role' to any other role and can grant/revoke
role memberships, and read every table, etc.

The fact that we're doing that by making the superuser be a member of
every role feels more like an implementation detail- one which has now
bitten us because it's affecting things that it really shouldn't.  The
'+group' list should be derivable from pg_auth_members and not include
'implicit' roles.

	Thanks,

		Stephen

  13. Re: superusers are members of all roles?

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-04-07T15:01:19Z

    Andrew Dunstan <andrew@dunslane.net> writes:
> I thought about that. What I'd like to know is how many people actually 
> want and use and expect the current behaviour. If it's more than a 
> handful (which I seriously doubt) then that's probably the way to go. 
> Otherwise it seems more trouble than it's worth.

Well, the point here is that "is_member_of" is currently considered
to be a kind of privilege test, and of course superusers should
automatically pass every privilege test.  If you want it to not act
that way in some circumstances, we need a fairly clear theory as to
which circumstances it should act which way in.

			regards, tom lane

  14. Re: superusers are members of all roles?

    Andrew Dunstan <andrew@dunslane.net> — 2011-04-07T15:26:29Z

    
On 04/07/2011 11:01 AM, Tom Lane wrote:
> Andrew Dunstan<andrew@dunslane.net>  writes:
>> I thought about that. What I'd like to know is how many people actually
>> want and use and expect the current behaviour. If it's more than a
>> handful (which I seriously doubt) then that's probably the way to go.
>> Otherwise it seems more trouble than it's worth.
> Well, the point here is that "is_member_of" is currently considered
> to be a kind of privilege test, and of course superusers should
> automatically pass every privilege test.  If you want it to not act
> that way in some circumstances, we need a fairly clear theory as to
> which circumstances it should act which way in.
>
> 			

Personally, other things being equal I would expect things to operate 
similarly to Unix groups, where root can do   just about anything but is 
only actually a member of a small number of groups:

    [root@emma ~]# groups
    root bin daemon sys adm disk wheel

I bet most DBAs and SAs would expect the same.

The HBA file is the most obvious context in which this actually matters, 
and off hand I can't think of another.

cheers

andrew

  15. Re: superusers are members of all roles?

    Bruce Momjian <bruce@momjian.us> — 2011-05-08T03:42:57Z

    Andrew Dunstan wrote:
> 
> 
> On 04/07/2011 11:01 AM, Tom Lane wrote:
> > Andrew Dunstan<andrew@dunslane.net>  writes:
> >> I thought about that. What I'd like to know is how many people actually
> >> want and use and expect the current behaviour. If it's more than a
> >> handful (which I seriously doubt) then that's probably the way to go.
> >> Otherwise it seems more trouble than it's worth.
> > Well, the point here is that "is_member_of" is currently considered
> > to be a kind of privilege test, and of course superusers should
> > automatically pass every privilege test.  If you want it to not act
> > that way in some circumstances, we need a fairly clear theory as to
> > which circumstances it should act which way in.
> >
> > 			
> 
> Personally, other things being equal I would expect things to operate 
> similarly to Unix groups, where root can do   just about anything but is 
> only actually a member of a small number of groups:
> 
>     [root@emma ~]# groups
>     root bin daemon sys adm disk wheel
> 
> I bet most DBAs and SAs would expect the same.
> 
> The HBA file is the most obvious context in which this actually matters, 
> and off hand I can't think of another.

Is this a TODO?

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

  16. Re: superusers are members of all roles?

    Robert Haas <robertmhaas@gmail.com> — 2011-05-09T11:53:50Z

    On Sat, May 7, 2011 at 11:42 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Is this a TODO?

I think so.

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

  17. Re: superusers are members of all roles?

    Bruce Momjian <bruce@momjian.us> — 2011-09-10T03:34:45Z

    Robert Haas wrote:
> On Sat, May 7, 2011 at 11:42 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > Is this a TODO?
> 
> I think so.

Added to TODO:

	Address problem where superusers are assumed to be members of all groups
	
	    http://archives.postgresql.org/pgsql-hackers/2011-04/msg00337.php 

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

  18. Re: superusers are members of all roles?

    Andrew Dunstan <andrew@dunslane.net> — 2011-09-12T01:40:13Z

    
On 09/09/2011 11:34 PM, Bruce Momjian wrote:
> Robert Haas wrote:
>> On Sat, May 7, 2011 at 11:42 PM, Bruce Momjian<bruce@momjian.us>  wrote:
>>> Is this a TODO?
>> I think so.
> Added to TODO:
>
> 	Address problem where superusers are assumed to be members of all groups
> 	
> 	    http://archives.postgresql.org/pgsql-hackers/2011-04/msg00337.php

This turns out to be a one-liner.

Patch attached.

cheers

andrew

  19. Re: superusers are members of all roles?

    Stephen Frost <sfrost@snowman.net> — 2011-09-12T02:32:07Z

    * Andrew Dunstan (andrew@dunslane.net) wrote:
> >	Address problem where superusers are assumed to be members of all groups
> >	
> >	    http://archives.postgresql.org/pgsql-hackers/2011-04/msg00337.php
> 
> This turns out to be a one-liner.

I really don't know that I agree with removing this, to be honest..  I
haven't got time at the moment to really discuss it, but at the very
least, not being able to 'set role' to any user when postgres would be
REALLY annoying..

	Thanks,

		Stephen

  20. Re: superusers are members of all roles?

    Robert Haas <robertmhaas@gmail.com> — 2011-09-12T03:27:40Z

    On Sun, Sep 11, 2011 at 10:32 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * Andrew Dunstan (andrew@dunslane.net) wrote:
>> >     Address problem where superusers are assumed to be members of all groups
>> >
>> >         http://archives.postgresql.org/pgsql-hackers/2011-04/msg00337.php
>>
>> This turns out to be a one-liner.
>
> I really don't know that I agree with removing this, to be honest..  I
> haven't got time at the moment to really discuss it, but at the very
> least, not being able to 'set role' to any user when postgres would be
> REALLY annoying..

Sure.  But I don't believe anyone has proposed changing that.  What
we're talking about here is that, for example, setting a reject rule
for a certain group in pg_hba.conf will always match superusers, even
though they're not in that group.

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

  21. Re: superusers are members of all roles?

    Andrew Dunstan <andrew@dunslane.net> — 2011-09-12T03:29:59Z

    
On 09/11/2011 10:32 PM, Stephen Frost wrote:
> * Andrew Dunstan (andrew@dunslane.net) wrote:
>>> 	Address problem where superusers are assumed to be members of all groups
>>> 	
>>> 	    http://archives.postgresql.org/pgsql-hackers/2011-04/msg00337.php
>> This turns out to be a one-liner.
> I really don't know that I agree with removing this, to be honest..  I
> haven't got time at the moment to really discuss it, but at the very
> least, not being able to 'set role' to any user when postgres would be
> REALLY annoying..
>
> 	

It's NOT changing that. All this affects is how +groupname is treated in 
pg_hba.conf, i.e. do we treat every superuser there as being a member of 
every group.

cheers

andrew

  22. Re: superusers are members of all roles?

    Stephen Frost <sfrost@snowman.net> — 2011-09-12T09:55:42Z

    * Andrew Dunstan (andrew@dunslane.net) wrote:
> It's NOT changing that. All this affects is how +groupname is
> treated in pg_hba.conf, i.e. do we treat every superuser there as
> being a member of every group.

Ah, sorry for the noise, that's fine (and I'm bit suprised it was a
one-liner, guess I should go look at the patch... ;).

	Thanks,

		Stephen

  23. Re: superusers are members of all roles?

    Andrew Dunstan <andrew@dunslane.net> — 2011-11-02T19:27:28Z

    
On 09/11/2011 09:40 PM, Andrew Dunstan wrote:
>
>
> On 09/09/2011 11:34 PM, Bruce Momjian wrote:
>> Robert Haas wrote:
>>> On Sat, May 7, 2011 at 11:42 PM, Bruce Momjian<bruce@momjian.us>  
>>> wrote:
>>>> Is this a TODO?
>>> I think so.
>> Added to TODO:
>>
>>     Address problem where superusers are assumed to be members of all 
>> groups
>>
>>         
>> http://archives.postgresql.org/pgsql-hackers/2011-04/msg00337.php
>
> This turns out to be a one-liner.
>
>

Patch with a small docs addition also. Adding to Nov commitfest.

cheers

andrew

  24. Re: superusers are members of all roles?

    Robert Haas <robertmhaas@gmail.com> — 2011-11-03T01:41:44Z

    On Wed, Nov 2, 2011 at 3:27 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
> Patch with a small docs addition also. Adding to Nov commitfest.

I have reviewed this and it looks good to me.  Marking Ready for Committer.

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