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
Thu, 18 Dec 2003 09:59:40 +1100
David Felstead <David.Felstead@colesmyer.com.au>
Re: TODO list
-----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-----

Wed, 17 Dec 2003 16:48:33 -0800
Jonathan Gardner <jgardner@jonathangardner.net>
Limiting per user and per db accesse (was TODO list)
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

Wed, 17 Dec 2003 20:30:11 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Limiting per user and per db accesse (was TODO list) 
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)

Wed, 17 Dec 2003 22:49:39 -0300
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Limiting per user and per db accesse (was TODO list)
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

Wed, 17 Dec 2003 21:30:17 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Limiting per user and per db accesse (was TODO list) 
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

Wed, 17 Dec 2003 23:03:33 -0400 (AST)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: Limiting per user and per db accesse (was TODO list)
"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

Wed, 17 Dec 2003 22:45:10 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Limiting per user and per db accesse (was TODO list) 