Thread: TODO list
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 %|&|^|~|`|'|@|}|#|%
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
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
--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
> 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
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
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
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
-*- 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
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
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
-----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-----
"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
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
Þó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
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)
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
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
"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
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)
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