Connection limit doesn't work for superuser

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

Connection limit doesn't work for superuser

Evgeniy Efimkin
Connection limit doesn't work for superuser
Hi hackers!
It would be nice if ALTER USER ... WITH CONNECTION LIMIT will work for superuser. It would protect against connection leaks. e.g. we have two superusers, one of them reached connection limit but not max_connections, the other is still possible to connect to database and solve the problem. The current behaviour would be the same for the case with rolconnlimit = -1. Superuser can execute NOLOGIN to another superuser and it works. I found previos discussion about CONNECTION LIMIT for superuser but it ended about Slony. https://www.postgresql.org/message-id/1154351265.22367.210.camel%40coppola.muc.ecircle.de

Reply | Threaded
Open this post in threaded view
|

Re: Connection limit doesn't work for superuser

Andrey Borodin-2
Hi!

> 7 нояб. 2018 г., в 11:48, Evgeniy Efimkin <[hidden email]> написал(а):
> It would be nice if ALTER USER ... WITH CONNECTION LIMIT will work for superuser. It would protect against connection leaks. e.g. we have two superusers, one of them reached connection limit but not max_connections, the other is still possible to connect to database and solve the problem. The current behaviour would be the same for the case with rolconnlimit = -1. Superuser can execute NOLOGIN to another superuser and it works. I found previos discussion about CONNECTION LIMIT for superuser but it ended about Slony. https://www.postgresql.org/message-id/1154351265.22367.210.camel%40coppola.muc.ecircle.de
>

Current docs contain following description for SUPERUSER:
>These clauses determine whether the new role is a “superuser”, who can override all access restrictions within the database.
Do we consider connection limit "access restriction"? Superuser can avoid setting his connection limit if he do not need it.

Best regards, Andrey Borodin.
Reply | Threaded
Open this post in threaded view
|

Re: Connection limit doesn't work for superuser

Robert Haas
On Wed, Nov 7, 2018 at 7:20 AM Andrey Borodin <[hidden email]> wrote:
> >These clauses determine whether the new role is a “superuser”, who can override all access restrictions within the database.
> Do we consider connection limit "access restriction"? Superuser can avoid setting his connection limit if he do not need it.

I don't think we should consider something that prevents you from
connecting to the database to be in the same category as something
that limits what you can do once you are connected.  IOW, +1 to the
original proposal from me.

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

Reply | Threaded
Open this post in threaded view
|

Re: Connection limit doesn't work for superuser

Tom Lane-2
Robert Haas <[hidden email]> writes:
> I don't think we should consider something that prevents you from
> connecting to the database to be in the same category as something
> that limits what you can do once you are connected.  IOW, +1 to the
> original proposal from me.

I'd vote against.  I think there are way more cases where this would
create a problem than where it would fix one.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: Connection limit doesn't work for superuser

Dmitriy Sarafannikov-3
What about LOGIN option? It is a similar access restriction, but it works for superuser.

=# create role nologin_role superuser nologin unencrypted password '1234';
CREATE ROLE
Time: 1.230 ms
~ $ psql postgres -U nologin_role -h localhost
Password for user nologin_role:
psql: FATAL:  role "nologin_role" is not permitted to log in

IMHO it does not fit this logic.

> 7 нояб. 2018 г., в 17:45, Tom Lane <[hidden email]> написал(а):
>
> Robert Haas <[hidden email]> writes:
>> I don't think we should consider something that prevents you from
>> connecting to the database to be in the same category as something
>> that limits what you can do once you are connected.  IOW, +1 to the
>> original proposal from me.
>
> I'd vote against.  I think there are way more cases where this would
> create a problem than where it would fix one.
>
> regards, tom lane
>


Reply | Threaded
Open this post in threaded view
|

Re: Connection limit doesn't work for superuser

Robert Haas
In reply to this post by Tom Lane-2
On Wed, Nov 7, 2018 at 9:45 AM Tom Lane <[hidden email]> wrote:
> Robert Haas <[hidden email]> writes:
> > I don't think we should consider something that prevents you from
> > connecting to the database to be in the same category as something
> > that limits what you can do once you are connected.  IOW, +1 to the
> > original proposal from me.
>
> I'd vote against.  I think there are way more cases where this would
> create a problem than where it would fix one.

Like what?

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

Reply | Threaded
Open this post in threaded view
|

Re: Connection limit doesn't work for superuser

Tom Lane-2
Robert Haas <[hidden email]> writes:
> On Wed, Nov 7, 2018 at 9:45 AM Tom Lane <[hidden email]> wrote:
>> I'd vote against.  I think there are way more cases where this would
>> create a problem than where it would fix one.

> Like what?

alter user postgres connection limit 0;

... oops ...

I'm not buying the argument that there are realistic use-cases where
you need a connection limit on a superuser role, either.  Whatever
you're doing that might merit a connection limit should not be done
as superuser.  I think this proposal boils down to asking for support
for an incredibly bad application design, and equipping every database
with an additional foot-gun in order to have that.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: Connection limit doesn't work for superuser

Robert Haas
On Wed, Nov 7, 2018 at 11:19 AM Tom Lane <[hidden email]> wrote:
> > Like what?
>
> alter user postgres connection limit 0;
>
> ... oops ...

Sure.  If you have no other superusers that's going to be sad.
Hopefully single-user mode lets you recover, though.  And, anyway,
there are plenty of ways for a superuser to break a cluster far worse
than that.

> I'm not buying the argument that there are realistic use-cases where
> you need a connection limit on a superuser role, either.  Whatever
> you're doing that might merit a connection limit should not be done
> as superuser.  I think this proposal boils down to asking for support
> for an incredibly bad application design, and equipping every database
> with an additional foot-gun in order to have that.

I don't agree; that sounds like masterminding to me.  "You shouldn't
want that feature, so we won't give it to you" is not always an
invalid argument, but we ought to tread lightly with it.

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

Reply | Threaded
Open this post in threaded view
|

Re: Connection limit doesn't work for superuser

Tomas Vondra-4
In reply to this post by Tom Lane-2

On 11/7/18 5:19 PM, Tom Lane wrote:
>
> ...
>
> I think this proposal boils down to asking for support for an
> incredibly bad application design, and equipping every database with
> an additional foot-gun in order to have that.
>

I'm not sure about that. IMHO being able to restrict the number of
superuser connections can be used to force users to use regular
(non-superuser) roles for stuff that does not require that. Which should
encourage better application design.

Of course, the question is whether such limit can actually be enforced
reliably (I mean, can't the superuser simply change it?) and whether
handing over superuser accounts to application users is a good idea in
general ...

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: Connection limit doesn't work for superuser

David G Johnston
In reply to this post by Robert Haas
On Wed, Nov 7, 2018 at 9:22 AM Robert Haas <[hidden email]> wrote:

>
> On Wed, Nov 7, 2018 at 11:19 AM Tom Lane <[hidden email]> wrote:
> > I'm not buying the argument that there are realistic use-cases where
> > you need a connection limit on a superuser role, either.  Whatever
> > you're doing that might merit a connection limit should not be done
> > as superuser.  I think this proposal boils down to asking for support
> > for an incredibly bad application design, and equipping every database
> > with an additional foot-gun in order to have that.
>
> I don't agree; that sounds like masterminding to me.  "You shouldn't
> want that feature, so we won't give it to you" is not always an
> invalid argument, but we ought to tread lightly with it.

On the reject side changing reasonable behavior based upon a single
complaint is somewhat over-reactive.

On the accept side, which I'm leaning toward, is that superuser is
already constrained by max_connections and, in addition, the
implications of setting this value are straight-forward and it obvious
requires intent on the part of the user.  Its not a "foot-gun" in the
sense that there are side-effects that the user isn't going to be
aware of by having this feature in place - it does exactly what the
label says it does.  POLA says it should either apply to the superuser
or the attempt to set the value to anything but -1 should fail.  I say
changing it so the feature actually works is the correct course of
action.

Maybe it should be impossible to set the value to zero while we are in
there.  Document setting "NOLOGIN" as the way to prevent a user from
making connections.

Having a validation on the role table that at least one superuser must
have login privileges might be good too if you are interested in
putting some protections in place to avoid self-inflicted lockouts.
But it doesn't seem to be a big issue.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Connection limit doesn't work for superuser

Tom Lane-2
"David G. Johnston" <[hidden email]> writes:
> On the accept side, which I'm leaning toward, is that superuser is
> already constrained by max_connections and, in addition, the
> implications of setting this value are straight-forward and it obvious
> requires intent on the part of the user.  Its not a "foot-gun" in the
> sense that there are side-effects that the user isn't going to be
> aware of by having this feature in place - it does exactly what the
> label says it does.

That's a fair point, and certainly if we allow and enforce "alter
user postgres nologin" (which we do), it's odd to not enforce
connection limit.  However, looking at the code, it's a little
clearer why it was done that way: it's for consistency with the
behavior of datconnlimit (per-database connection limits).

I think that having superusers be immune to datconnlimit is actually
the right thing; for one reason, because datconnlimit can be set by
database owners, who should not be able to lock superusers out of
their database.  If people are okay with having rolconnlimit act
differently from datconnlimit in this respect, then I'll withdraw
my objection.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: Connection limit doesn't work for superuser

Robert Haas
On Wed, Nov 7, 2018 at 1:14 PM Tom Lane <[hidden email]> wrote:
> I think that having superusers be immune to datconnlimit is actually
> the right thing; for one reason, because datconnlimit can be set by
> database owners, who should not be able to lock superusers out of
> their database.

Yeah, that's a reasonable argument, although they'd also be locking
themselves out of the database, and the superuser could undo it by
connecting to some other database.

> If people are okay with having rolconnlimit act
> differently from datconnlimit in this respect, then I'll withdraw
> my objection.

Is there any particular reason why they should be consistent?  It's
not obvious to me, but sometimes I'm dumb.

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

Reply | Threaded
Open this post in threaded view
|

Re: Connection limit doesn't work for superuser

Joshua D. Drake
On 11/7/18 10:49 AM, Robert Haas wrote:

> On Wed, Nov 7, 2018 at 1:14 PM Tom Lane <[hidden email]> wrote:
>> I think that having superusers be immune to datconnlimit is actually
>> the right thing; for one reason, because datconnlimit can be set by
>> database owners, who should not be able to lock superusers out of
>> their database.
> Yeah, that's a reasonable argument, although they'd also be locking
> themselves out of the database, and the superuser could undo it by
> connecting to some other database.
>
>> If people are okay with having rolconnlimit act
>> differently from datconnlimit in this respect, then I'll withdraw
>> my objection.
> Is there any particular reason why they should be consistent?  It's
> not obvious to me, but sometimes I'm dumb.

IMO, super users should only be affected by
superuser_reserved_connections. Otherwise we are getting into fine grain
of potential foot guns.


JD




>

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
*****     Unless otherwise stated, opinions are my own.   *****


Reply | Threaded
Open this post in threaded view
|

Re: Connection limit doesn't work for superuser

Laurenz Albe
In reply to this post by Tomas Vondra-4
Tomas Vondra wrote:

> On 11/7/18 5:19 PM, Tom Lane wrote:
> > I think this proposal boils down to asking for support for an
> > incredibly bad application design, and equipping every database with
> > an additional foot-gun in order to have that.
>
> I'm not sure about that. IMHO being able to restrict the number of
> superuser connections can be used to force users to use regular
> (non-superuser) roles for stuff that does not require that. Which should
> encourage better application design.
>
> Of course, the question is whether such limit can actually be enforced
> reliably (I mean, can't the superuser simply change it?) and whether
> handing over superuser accounts to application users is a good idea in
> general ...

None of these arguments for enforcing a connection limit for superusers
sound terribly compelling to me.

He who is tempted to run his application with a superuser for
simplicitly's sake will not be the person to set a connection limit
for superusers.

I concur with Tom that this will do more harm than good.

Yours,
Laurenz Albe


Reply | Threaded
Open this post in threaded view
|

Re: Connection limit doesn't work for superuser

David G Johnston
In reply to this post by Tom Lane-2
On Wed, Nov 7, 2018 at 11:14 AM Tom Lane <[hidden email]> wrote:
> If people are okay with having rolconnlimit act
> differently from datconnlimit in this respect, then I'll withdraw
> my objection.

Since the rolconnlimit specifically and precisely targets the
superuser in a narrow manner it makes sense on its face to recognize
it.  That the indirect targeting of all superusers via datconnlimit is
ignored is likewise a reasonable decision.  Ignoring datconnlimit
doesn't reduce the validity of having the rolconnlimit setting be
enforced and I do not see a substantial argument for why doing so
could be harmful to a reasonably skilled operator; while it provides a
reasonable, if likely seldom used, capability that is already long
established for non-superusers.

For me the burden falls onto why rolconnlimit should not be
enforced...regardless of the fact that unenforced is status quo.  We
generally tend toward giving superusers abilities and letting them
decide how to use them and this should be no different.

David J.