Thread: TODO list

TODO list

From
Marko Zmak
Date:
I've been usin psql for quite a long time and I found it to be very
comfortable. Since some web providers in my country refuse to put psql
while some thing are still in psql TODO list, I'm interested in
following...


I'd like to know when are you planning to deal with this TODO item:

Allow limits on per-db/user connections

I would appreciate if it was as soon as possible. Thanks.

Best regards, Marko Zmak.

Can't stop the Zmak
%|&|^|~|`|'|@|}|#|%


Re: TODO list

From
Bruce Momjian
Date:
Marko Zmak wrote:
> 
> I've been usin psql for quite a long time and I found it to be very
> comfortable. Since some web providers in my country refuse to put psql
> while some thing are still in psql TODO list, I'm interested in
> following...
> 
> 
> I'd like to know when are you planning to deal with this TODO item:
> 
> Allow limits on per-db/user connections
> 
> I would appreciate if it was as soon as possible. Thanks.

I know of no one working on this feature.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: TODO list

From
Gavin Sherry
Date:
On Wed, 17 Dec 2003, Bruce Momjian wrote:

> Marko Zmak wrote:
> >
> > I've been usin psql for quite a long time and I found it to be very
> > comfortable. Since some web providers in my country refuse to put psql
> > while some thing are still in psql TODO list, I'm interested in
> > following...
> >
> >
> > I'd like to know when are you planning to deal with this TODO item:
> >
> > Allow limits on per-db/user connections
> >
> > I would appreciate if it was as soon as possible. Thanks.
>
> I know of no one working on this feature.

But I'm sure no one would have a problem with Marko attempting it :-).

Gavin


Re: TODO list

From
Larry Rosenman
Date:

--On Thursday, December 18, 2003 08:07:03 +1100 Gavin Sherry
<swm@linuxworld.com.au> wrote:

> On Wed, 17 Dec 2003, Bruce Momjian wrote:
>
>> Marko Zmak wrote:
>> >
>> > I've been usin psql for quite a long time and I found it to be very
>> > comfortable. Since some web providers in my country refuse to put psql
>> > while some thing are still in psql TODO list, I'm interested in
>> > following...
>> >
>> >
>> > I'd like to know when are you planning to deal with this TODO item:
>> >
>> > Allow limits on per-db/user connections
>> >
>> > I would appreciate if it was as soon as possible. Thanks.
>>
>> I know of no one working on this feature.
>
> But I'm sure no one would have a problem with Marko attempting it :-).
I also know that Olivier PRENANT was looking for something similar.  And
the same admonition goes.  I.E., you have an itch, we'd love to see you
scratch it.

LER

>
> Gavin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>



--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Re: TODO list

From
"Jonathan Gardner"
Date:
> Marko Zmak wrote:
>>
>> I've been usin psql for quite a long time and I found it to be very
>> comfortable. Since some web providers in my country refuse to put psql
>> while some thing are still in psql TODO list, I'm interested in
>> following...
>>
>>
>> I'd like to know when are you planning to deal with this TODO item:
>>
>> Allow limits on per-db/user connections
>>
>> I would appreciate if it was as soon as possible. Thanks.
>
> I know of no one working on this feature.
>

Is this something a someone who is new to PostgreSQL development can
attempt? Does someone already have a design planned out?


-- 
Jonathan Gardner
jgardner@jonathangardner.net




Re: TODO list

From
Andrew Dunstan
Date:
Jonathan Gardner wrote:

>>Marko Zmak wrote:
>>    
>>
>>>I've been usin psql for quite a long time and I found it to be very
>>>comfortable. Since some web providers in my country refuse to put psql
>>>while some thing are still in psql TODO list, I'm interested in
>>>following...
>>>
>>>
>>>I'd like to know when are you planning to deal with this TODO item:
>>>
>>>Allow limits on per-db/user connections
>>>
>>>I would appreciate if it was as soon as possible. Thanks.
>>>      
>>>
>>I know of no one working on this feature.
>>
>>    
>>
>
>Is this something a someone who is new to PostgreSQL development can
>attempt? Does someone already have a design planned out?
>
>
>  
>

Before we even get there we need agreement on exactly what it means :-)

Seems like the best place for the settings might be pg_hba.conf.

Say we have a new keyword "limit" there. Here are the possibilities (I 
think):

case 1 - limit username's connections to dbname:
limit dbname username n
case2 - limit username's connections regardless of database:
limit all username n
case 3 - limit all users' connections to dbname:
limit dbname all n
case 4 - limit username's connections to any particular database:
limit any username n
case 5 - limit all users' connections to any particular database:
limit any all n
case 6 - limit all users' connections regardless of database:
limit all all n

Would we want to bother about cases 4 and 5? Are the semantics of case 6 
what we want?

Once the behaviour is agreed, (based on my recent experience) I think 
this is something that might well be attempted by someone new to Pg 
development. If you ask questions you will get plenty of help.

cheers

andrew






Re: TODO list

From
Tom Lane
Date:
Andrew Dunstan <andrew@dunslane.net> writes:
> case 6 - limit all users' connections regardless of database:
> limit all all n

That's called max_connections.  Don't think we need a redundant
implementation of same ...

Another little nitpick is that I don't like assuming that "any" and
"all" are never going to be used as database or user names.  (I know
that pg_hba.conf already uses "all" this way, and IMHO that was a bogus
decision.  Something like "*" would have been less likely to collide.)

On an implementation level, where are you thinking of enforcing this?
pg_hba.conf would not be very appropriate for the most likely place to
put it, which is in backend startup shortly after establishing a PGPROC
entry (with the data about numbers of active connections obtained by
scanning the PGPROC array for other backends connected to the same
database or with the same userid).  I think we've thrown away the
PostmasterContext long before that, so we couldn't use cached
pg_hba.conf data without some redesign of the startup sequence.
        regards, tom lane


Re: TODO list

From
David Felstead
Date:
Andrew Dunstan wrote:

>Before we even get there we need agreement on exactly what it means :-)
>
>Seems like the best place for the settings might be pg_hba.conf.
>
>Say we have a new keyword "limit" there. Here are the possibilities (I
>think):

<snip>

First post (unlurking) here, so just a minor contribution.  I think that
there is already a LIMIT keyword, ala:

http://www.postgresql.org/docs/7.4/static/queries-limit.html

Since this is more or less a privileges issue, would it be reasonable to
extend the GRANT/REVOKE style syntax (with a different keyword, of course)
to allow this parameter to be set, just for consistencies sake? This would
also allow the sysadmin to limit connections on groups as well, if
necessary.

I'm not sure how you would structure a GRANT/REVOKE style query to include
a numeric parameter so it doesn't look completely out of place, so it's just
an idea...

Best regards,

-David Felstead

Attachment

Re: TODO list

From
Þórhallur Hálfdánarson
Date:
-*- Tom Lane <tgl@sss.pgh.pa.us> [ 2003-12-17 22:46 ]:
> Andrew Dunstan <andrew@dunslane.net> writes:
> > case 6 - limit all users' connections regardless of database:
> > limit all all n
> 
> That's called max_connections.  Don't think we need a redundant
> implementation of same ...
> 
> Another little nitpick is that I don't like assuming that "any" and
> "all" are never going to be used as database or user names.  (I know
> that pg_hba.conf already uses "all" this way, and IMHO that was a bogus
> decision.  Something like "*" would have been less likely to collide.)
> 
> On an implementation level, where are you thinking of enforcing this?
> pg_hba.conf would not be very appropriate for the most likely place to
> put it, which is in backend startup shortly after establishing a PGPROC
> entry (with the data about numbers of active connections obtained by
> scanning the PGPROC array for other backends connected to the same
> database or with the same userid).  I think we've thrown away the
> PostmasterContext long before that, so we couldn't use cached
> pg_hba.conf data without some redesign of the startup sequence.

I'd like to mention that administrators likely to use the this feature would probably like to be able to tune this
withouthaving to modify a file -- updating via SQL (=> storing this in a system table) would be extremely nice...
 

-- 
Regards,
Tolli
tolli@tol.li


Re: TODO list

From
David Felstead
Date:
David Felstead wrote:
>
>Andrew Dunstan wrote:
>
>>Before we even get there we need agreement on exactly what it means :-)
>>
>>Seems like the best place for the settings might be pg_hba.conf.
>>
>>Say we have a new keyword "limit" there. Here are the possibilities (I
>>think):
>
<snip again>

Bah, what a way to make an entrance - I re-read Andrew's post and realised
he was talking about pg_hba.conf.  My apologies. :(

Regardless, is this something that could be handled at a privilege level?

Best regards,

-David Felstead

Attachment

Re: TODO list

From
"Andrew Dunstan"
Date:
Tom Lane said:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> case 6 - limit all users' connections regardless of database:
>> limit all all n
>
> That's called max_connections.  Don't think we need a redundant
> implementation of same ...
>

no - this was intended to limit *each* user - max-connections limits
total  connections. Maybe I expressed it badly. (reinforces my point about
needing to make sure we get the semantics straight first).


> Another little nitpick is that I don't like assuming that "any"
and "all" are never going to be used as database or user names.  (I know
that pg_hba.conf already uses "all" this way, and IMHO that was a bogus
decision.  Something like "*" would have been less likely to collide.)
>


I entirely agree. Let's change it. For a new major release people will
have probably need to do an initdb anyway.

> On an implementation level, where are you thinking of enforcing this?
pg_hba.conf would not be very appropriate for the most likely place to put
it, which is in backend startup shortly after establishing a PGPROC entry
(with the data about numbers of active connections obtained by scanning
the PGPROC array for other backends connected to the same database or with
the same userid).  I think we've thrown away the PostmasterContext long
before that, so we couldn't use cached
> pg_hba.conf data without some redesign of the startup sequence.
>


Without digging deeply at all I thought probably in the postmaster. But I
would defer to your good advice ;-)

I'm not at all dogmatic about using pg_hba.conf - it just seemed similar
to the info we carry there.

cheers

andrew





Limiting per user and per db accesse (was TODO list)

From
Jonathan Gardner
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wednesday 17 December 2003 2:59 pm, David Felstead wrote:
> David Felstead wrote:
> Bah, what a way to make an entrance - I re-read Andrew's post and
> realised he was talking about pg_hba.conf.  My apologies. :(
>
> Regardless, is this something that could be handled at a privilege level?
>

This actually sounds a lot better than the pg_hba.conf idea. We create
databases and users with SQL commands, we should probably expand the syntax
to limit their abilities wrt the cluster.

So here's another shot. What if we had a variable "max_connections" for each
user, group, and database?

- -- User jgardner can only have 12 concurrent connections with this cluster.
ALTER USER jgardner SET max_connections = 12;

- -- Group www can only have 12 concurrent connections with the cluster.
ALTER GROUP www SET max_connections = 12;

- -- Database db can only have 12 concurrent connections from anyone.
ALTER DATABASE db SET max_connections = 12;


Not good enough for you? Perhaps we can do something like we do for
permissions and use an aclitem[]

ALTER USER jgardner
SET max_connections = '{=12,db1=5,db2=1}'::maxconnitem[];

This would mean that jgardner can only have up to 12 connections with this
cluster; only 5 of those may be with database db1, and only 1 can be with
db2.

We can do something very similar for groups.

And databases? Maybe we can provide a type for that as well, that
distinguishes between users and groups?

- --
Jonathan Gardner
jgardner@jonathangardner.net
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE/4PlpWgwF3QvpWNwRArFHAJ9UQIai+oevgeu8V5JAk+I3gWCUgwCfWUQe
3e8m5CzRZIgzZYUqjnC5ZMg=
=kP5S
-----END PGP SIGNATURE-----


Re: TODO list

From
Tom Lane
Date:
"Andrew Dunstan" <andrew@dunslane.net> writes:
> Tom Lane said:
>> On an implementation level, where are you thinking of enforcing this?

> Without digging deeply at all I thought probably in the postmaster.

Nah, that's a nonstarter, because the postmaster has basically no
information about its children except for their PIDs and cancel keys.
In particular it does not know which database or user each one is for,
and really can't because the connection request packet is not input
from the client connection until after fork().

AFAICS there's really no other way to get this information than by
looking in shared memory.  The PGPROC array already has info about
connected databases.  I don't think it stores info about session user,
but that would be an easy and cheap addition.

> I'm not at all dogmatic about using pg_hba.conf - it just seemed similar
> to the info we carry there.

It's not necessarily a bad idea; we'd just need to adjust our theory
about when the cached pg_hba.conf data can be freed.
        regards, tom lane


Re: Limiting per user and per db accesse (was TODO list)

From
Tom Lane
Date:
Jonathan Gardner <jgardner@jonathangardner.net> writes:
> - -- Group www can only have 12 concurrent connections with the cluster.
> ALTER GROUP www SET max_connections = 12;

I think group-related restrictions would be an impossible rat's nest
to define, because there's no one-to-one correspondence between backend
processes and groups.  Per-user and per-database make sense to me,
because a backend does have a well-defined (session) user and a
well-defined database.
        regards, tom lane


Re: TODO list

From
"Andrew Dunstan"
Date:
Þórhallur Hálfdánarson said:
>
> I'd like to mention that administrators likely to use the this feature
> would probably like to be able to tune this without having to modify a
> file -- updating via SQL (=> storing this in a system table) would be
> extremely nice...
>


We set connection permissions in a config file - why would we not use the
same mechanism for connection limits?

I'm not objecting, but I think we should be consistent.

cheers

andrew




Re: Limiting per user and per db accesse (was TODO list)

From
Alvaro Herrera
Date:
On Wed, Dec 17, 2003 at 08:30:11PM -0500, Tom Lane wrote:
> Jonathan Gardner <jgardner@jonathangardner.net> writes:
> > - -- Group www can only have 12 concurrent connections with the cluster.
> > ALTER GROUP www SET max_connections = 12;
> 
> I think group-related restrictions would be an impossible rat's nest
> to define, because there's no one-to-one correspondence between backend
> processes and groups.  Per-user and per-database make sense to me,
> because a backend does have a well-defined (session) user and a
> well-defined database.

What about roles?  Is anybody going to attack the mixed users+groups
implementation in this development cycles?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
A male gynecologist is like an auto mechanic who never owned a car.
(Carrie Snow)


Re: Limiting per user and per db accesse (was TODO list)

From
Tom Lane
Date:
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> What about roles?  Is anybody going to attack the mixed users+groups
> implementation in this development cycles?

Not me.  I think Peter was making some noises about it though.
        regards, tom lane


Re: Limiting per user and per db accesse (was TODO list)

From
"Marc G. Fournier"
Date:
On Wed, 17 Dec 2003, Tom Lane wrote:

> Jonathan Gardner <jgardner@jonathangardner.net> writes:
> > - -- Group www can only have 12 concurrent connections with the cluster.
> > ALTER GROUP www SET max_connections = 12;
>
> I think group-related restrictions would be an impossible rat's nest to
> define, because there's no one-to-one correspondence between backend
> processes and groups.  Per-user and per-database make sense to me,
> because a backend does have a well-defined (session) user and a
> well-defined database.

'k, I'm a bit confused here ... we already do the 'user->group' checks at
the table level, through GRANT/REVOKE ... why couldn't we do similar at
the database level?  If you were to know that the database *had* per group
restrictions, when you check # of connections, all you'd need to do is
figure out if user is part of GroupA and, if so, increment that count ...
no?


----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664


Re: Limiting per user and per db accesse (was TODO list)

From
Tom Lane
Date:
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> On Wed, 17 Dec 2003, Tom Lane wrote:
>> I think group-related restrictions would be an impossible rat's nest to
>> define, because there's no one-to-one correspondence between backend
>> processes and groups.

> 'k, I'm a bit confused here ... we already do the 'user->group' checks at
> the table level, through GRANT/REVOKE ... why couldn't we do similar at
> the database level?  If you were to know that the database *had* per group
> restrictions, when you check # of connections, all you'd need to do is
> figure out if user is part of GroupA and, if so, increment that count ...
> no?

Well, no, because I don't understand what the interaction of different
group settings ought to be.  If user A is a member of groups G and H,
while user B is a member of groups H and J, how are you going to decide
whether a collection of M logins from A and N logins from B violate some
per-group limits?  In particular do you want to say that the requirement
is on min() or on max() --- that is, must the configuration meet every
group's limit separately, or can you log in if there's at least one
group that says you can?  I can imagine uses for both approaches, so
I don't think that's an idle question.

There's also an implementation-efficiency issue, which is how you check
such constraints without groveling through each entry in the pg_group
table.  I think this could put a pretty serious hurt on our backend
startup time, *even if you are not using the feature at all*.
        regards, tom lane


Re: TODO list

From
ohp@pyrenet.fr
Date:
Yeah...
That would help me soooo much, that would soooo cool...

According to what I read on the subject after I got this mail, could we
envison something like:

ALTER DATABASE xxx SET max_connection to 100;
?

Regards
On Wed, 17 Dec 2003, Larry Rosenman wrote:

> Date: Wed, 17 Dec 2003 15:13:00 -0600
> From: Larry Rosenman <ler@lerctr.org>
> To: Gavin Sherry <swm@linuxworld.com.au>,
>      Bruce Momjian <pgman@candle.pha.pa.us>
> Cc: Marko Zmak <xmak@sharanet.org>, pgsql-hackers@postgresql.org,
>      Olivier PRENANT <ohp@pyrenet.fr>
> Subject: Re: [HACKERS] TODO list
>
>
>
> --On Thursday, December 18, 2003 08:07:03 +1100 Gavin Sherry
> <swm@linuxworld.com.au> wrote:
>
> > On Wed, 17 Dec 2003, Bruce Momjian wrote:
> >
> >> Marko Zmak wrote:
> >> >
> >> > I've been usin psql for quite a long time and I found it to be very
> >> > comfortable. Since some web providers in my country refuse to put psql
> >> > while some thing are still in psql TODO list, I'm interested in
> >> > following...
> >> >
> >> >
> >> > I'd like to know when are you planning to deal with this TODO item:
> >> >
> >> > Allow limits on per-db/user connections
> >> >
> >> > I would appreciate if it was as soon as possible. Thanks.
> >>
> >> I know of no one working on this feature.
> >
> > But I'm sure no one would have a problem with Marko attempting it :-).
> I also know that Olivier PRENANT was looking for something similar.  And
> the same admonition goes.  I.E., you have an itch, we'd love to see you
> scratch it.
>
> LER
>
> >
> > Gavin
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 7: don't forget to increase your free space map settings
> >
>
>
>
>

-- 
Olivier PRENANT                    Tel: +33-5-61-50-97-00 (Work)
6, Chemin d'Harraud Turrou           +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
FRANCE                          Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)


Re: TODO list

From
Andrew Dunstan
Date:
Tom Lane wrote:

>"Andrew Dunstan" <andrew@dunslane.net> writes:
>  
>
>>Tom Lane said:
>>    
>>
>>>On an implementation level, where are you thinking of enforcing this?
>>>      
>>>
>
>  
>
>>Without digging deeply at all I thought probably in the postmaster.
>>    
>>
>
>Nah, that's a nonstarter, because the postmaster has basically no
>information about its children except for their PIDs and cancel keys.
>In particular it does not know which database or user each one is for,
>and really can't because the connection request packet is not input
>from the client connection until after fork().
>
>AFAICS there's really no other way to get this information than by
>looking in shared memory.  The PGPROC array already has info about
>connected databases.  I don't think it stores info about session user,
>but that would be an easy and cheap addition.
>
>  
>
>>I'm not at all dogmatic about using pg_hba.conf - it just seemed similar
>>to the info we carry there.
>>    
>>
>
>It's not necessarily a bad idea; we'd just need to adjust our theory
>about when the cached pg_hba.conf data can be freed.
>
>  
>


Did we reach a concensus about how this should be done? From a config 
file? If so, should it be pg_hba.conf? Or from a table?

cheers

andrew