User Quota Implementation
All,
Out of necessity, I've implemented user quotas in 7.4.3. What would the
process be for having this reviewed and combined? I have a patch for
7.4.3 ready, but wanted to know if you suggest that I patch the latest
cvs instead. Below if some information on the implementation.
=======================================================================
-- FACTS --------------------------------------------------------------
=======================================================================
1. Only a superuser can modify user quotas.
2. The minimum quota size is 1K.
3. The maximum quota size is (currently) the maximum of int4*1024 bytes.
4. Altering a user's quota does not make a change to their current data.
5. If you modify a user's quota to smaller than their current amount,
they will be unable to add any more data... but their current schema is
not truncated.
=======================================================================
-- SQL CHANGES --------------------------------------------------------
=======================================================================
COMMAND: ALTER USER
*** NEW SYNTAX ***
ALTER USER name [ [ WITH ] option [ ... ] ]
where option can be:
[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| VALID UNTIL 'abstime'
| QUOTA file_size | NOQUOTA
where file_size is:
integer[K | M | G]
K = Kilobytes
M = Megabytes
G = Gigabytes
=======================================================================
-- USAGE EXAMPLES -----------------------------------------------------
=======================================================================
*** VARIATIONS OF QUOTA SIZES ***
ALTER USER someuser QUOTA 50M;
ALTER USER someuser QUOTA 1G;
ALTER USER someuser QUOTA 500K;
*** GIVE USER AN UNLIMITED QUOTA ***
ALTER USER someuser NOQUOTA;
=======================================================================
-- INTERNAL CHANGES ---------------------------------------------------
=======================================================================
pg_shadow has added attribute userquota of type int4
pg_user view has added attribute (pg_shadow.userquota) to selection.
ADDED KEYWORDS
QUOTA
NOQUOTA
--
Jonah H. Harris, UNIX Administrator | phone: 505.224.4814
Albuquerque TVI | fax: 505.224.3014
525 Buena Vista SE | jharris@tvi.edu
Albuquerque, New Mexico 87106 | http://w3.tvi.edu/~jharris/
"All great truths begin as blasphemies."
-- George Bernard Shaw
* Jonah H. Harris (jharris@tvi.edu) wrote:
Out of necessity, I've implemented user quotas in 7.4.3. What would the
process be for having this reviewed and combined? I have a patch for
7.4.3 ready, but wanted to know if you suggest that I patch the latest
cvs instead. Below if some information on the implementation.
Personally, I would love to see this in PostgreSQL. It'd be great if it
could get into 7.5. An issue I see with that is that (similar to
Oracle...) I think people would want to be able to specify
per-tablespace quotas. Perhaps that wouldn't be too hard to add?
Stephen
Personally, I would love to see this in PostgreSQL. It'd be great if it
could get into 7.5. An issue I see with that is that (similar to
Oracle...) I think people would want to be able to specify
per-tablespace quotas. Perhaps that wouldn't be too hard to add?
7.5 is already closed for new features I believe...
Chris
* Christopher Kings-Lynne (chriskl@familyhealth.com.au) wrote:
Personally, I would love to see this in PostgreSQL. It'd be great if it
could get into 7.5. An issue I see with that is that (similar to
Oracle...) I think people would want to be able to specify
per-tablespace quotas. Perhaps that wouldn't be too hard to add?7.5 is already closed for new features I believe...
Quotas is part of a sane tablespace implementation, right? O;)
Stephen
On Fri, 9 Jul 2004, Christopher Kings-Lynne wrote:
Personally, I would love to see this in PostgreSQL. It'd be great if it
could get into 7.5. An issue I see with that is that (similar to
Oracle...) I think people would want to be able to specify
per-tablespace quotas. Perhaps that wouldn't be too hard to add?7.5 is already closed for new features I believe...
Very much so ...
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
On Fri, 9 Jul 2004, Stephen Frost wrote:
* Christopher Kings-Lynne (chriskl@familyhealth.com.au) wrote:
Personally, I would love to see this in PostgreSQL. It'd be great if it
could get into 7.5. An issue I see with that is that (similar to
Oracle...) I think people would want to be able to specify
per-tablespace quotas. Perhaps that wouldn't be too hard to add?7.5 is already closed for new features I believe...
Quotas is part of a sane tablespace implementation, right? O;)
So its still considered a 'missing feature', not a bug ...
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
On Fri, 2004-07-09 at 09:29, Stephen Frost wrote:
* Christopher Kings-Lynne (chriskl@familyhealth.com.au) wrote:
Personally, I would love to see this in PostgreSQL. It'd be great if it
could get into 7.5. An issue I see with that is that (similar to
Oracle...) I think people would want to be able to specify
per-tablespace quotas. Perhaps that wouldn't be too hard to add?7.5 is already closed for new features I believe...
Quotas is part of a sane tablespace implementation, right? O;)
I would think having would allow us to take advantage of all of the
various kernel level filesystem features without needing to implement
them directly within PostgreSQL (crypto, quotas, data mirror, etc.).
Simply setup a tablespace for a given user with permissions to allow
only that user to create new objects within it and make it the default
location) -- tie their schema to their tablespace? -- then set a kernel
level quota on their tablespace.
Or do we expect a PostgreSQL implementation to do more than that, to
only count active data by ignoring data pending a vacuum?
On Thu, 8 Jul 2004, Jonah H. Harris wrote:
Hi,
3. The maximum quota size is (currently) the maximum of int4*1024 bytes.
why is this? This is very limiting ...
Using a 64bit value would be a lot more straight foreward.
Greetings, Klaus
--
Full Name : Klaus Naumann | (http://www.mgnet.de/) (Germany)
Phone / FAX : ++49/177/7862964 | E-Mail: (kn@mgnet.de)
On Fri, 2004-07-09 at 10:14, Rod Taylor wrote:
On Fri, 2004-07-09 at 09:29, Stephen Frost wrote:
* Christopher Kings-Lynne (chriskl@familyhealth.com.au) wrote:
Personally, I would love to see this in PostgreSQL. It'd be great if it
could get into 7.5. An issue I see with that is that (similar to
Oracle...) I think people would want to be able to specify
per-tablespace quotas. Perhaps that wouldn't be too hard to add?7.5 is already closed for new features I believe...
Quotas is part of a sane tablespace implementation, right? O;)
I would think having would allow us to take advantage of all of the
^^^
having tablespaces would
* Rod Taylor (pg@rbt.ca) wrote:
I would think having would allow us to take advantage of all of the
various kernel level filesystem features without needing to implement
them directly within PostgreSQL (crypto, quotas, data mirror, etc.).Simply setup a tablespace for a given user with permissions to allow
only that user to create new objects within it and make it the default
location) -- tie their schema to their tablespace? -- then set a kernel
level quota on their tablespace.
Since the user accessing/writing to the tablespaces would be the
postgres user I don't really think this 'solution' works in reality.
Or do we expect a PostgreSQL implementation to do more than that, to
only count active data by ignoring data pending a vacuum?
Certainly, it should.
Stephen
Simply setup a tablespace for a given user with permissions to allow
only that user to create new objects within it and make it the default
location) -- tie their schema to their tablespace? -- then set a kernel
level quota on their tablespace.Since the user accessing/writing to the tablespaces would be the
postgres user I don't really think this 'solution' works in reality.
I had assumed it would be a directory based quota rather than a user
based one.
Or do we expect a PostgreSQL implementation to do more than that, to
only count active data by ignoring data pending a vacuum?Certainly, it should.
Okay. But just so we all know that this means the user with a 5MB quota
could still (potentially) fill 1TB of physical diskspace.
* Rod Taylor (pg@rbt.ca) wrote:
Simply setup a tablespace for a given user with permissions to allow
only that user to create new objects within it and make it the default
location) -- tie their schema to their tablespace? -- then set a kernel
level quota on their tablespace.Since the user accessing/writing to the tablespaces would be the
postgres user I don't really think this 'solution' works in reality.I had assumed it would be a directory based quota rather than a user
based one.
It's been a while since I played with quotas but I don't recall this
option being available.
Or do we expect a PostgreSQL implementation to do more than that, to
only count active data by ignoring data pending a vacuum?Certainly, it should.
Okay. But just so we all know that this means the user with a 5MB quota
could still (potentially) fill 1TB of physical diskspace.
Hmm, interesting point. What are the options? Make sure the user
understands they have to vacuum their tables in order to regain the
space? Have two seperate values (similar to soft vs. hard limits) that
the admin sets? Either (or both) of those seem reasonable to me.
Stephen
Since the user accessing/writing to the tablespaces would be the
postgres user I don't really think this 'solution' works in reality.I had assumed it would be a directory based quota rather than a user
based one.It's been a while since I played with quotas but I don't recall this
option being available.
Group quotas should be sufficient. Create directory readable/writable to
only the pgsql user, but have the group ownership be representative of
the user in question.
* Rod Taylor (pg@rbt.ca) wrote:
Since the user accessing/writing to the tablespaces would be the
postgres user I don't really think this 'solution' works in reality.I had assumed it would be a directory based quota rather than a user
based one.It's been a while since I played with quotas but I don't recall this
option being available.Group quotas should be sufficient. Create directory readable/writable to
only the pgsql user, but have the group ownership be representative of
the user in question.
Rather ugly, and you'll run out of groups if you have alot of users (the
postgres user can only be in so many groups). It's a cute idea but I
really don't see it as being viable.
Stephen
On Thu, Jul 08, 2004 at 03:27:34PM -0600, Jonah H. Harris wrote:
Out of necessity, I've implemented user quotas in 7.4.3. What would the
process be for having this reviewed and combined? I have a patch for
7.4.3 ready, but wanted to know if you suggest that I patch the latest
cvs instead. Below if some information on the implementation.
At exactly what time is the quota enforced? Does the enforcement
somehow serialize that operation?
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Thou shalt study thy libraries and strive not to reinvent them without
cause, that thy code may be short and readable and thy days pleasant
and productive. (7th Commandment for C Programmers)
* Klaus Naumann (kn@mgnet.de) wrote:
On Thu, 8 Jul 2004, Jonah H. Harris wrote:
3. The maximum quota size is (currently) the maximum of int4*1024 bytes.
why is this? This is very limiting ...
It's 2TB...
Using a 64bit value would be a lot more straight foreward.
It sounded to me like it might be a limitation forced by some other part
of postgres, but I don't really know... Good question though.
Stephen
There are a couple of modifications that I'd still like to make to user
quotas. Because 7.5 is locked, this may be a good time to discuss the
implementation (possibly for 7.6?)
I have seen some discussion about using OS-level quotas on a user or
group level, however, like our Oracle system, not all database users
have a system account. This is why I needed to implement user-specific
quota functionality within the database itself.
Also note, my quota implementation currently expects a well-vacuumed
database. I always use pg_autovacuum but don't know about most other
people.
I'd like to make the following changes... Let me know your thoughts.
- Change userquota from int4 to int8.
- userquota is stored in units of kilobytes... is this adequate? Would
anyone ever use a quota < 1K other than for allowing a user no space?
If the user has no space, why not just disable the account and/or make
them read-only?
- Would anyone want to use a group quota in PGSQL (rather than user-only)?
- I assume that, based on discussion, not everyone sets up
auto-vacuuming and therefore I would need to change the way I perform
calculations.
- Quota acts on any object owned by the user. Is this adequate for
everyone?
- Hard limits vs. soft limits... does anyone think it's a good idea to
truncate someone's data? I personally don't think it's a good idea.
- What do you think about userquota being the attribute in pg_shadow...
would you rather see something else.
Is there any additional functionality you would like to see in a quota
implementation?
Would you rather see ALTER USER SET for quota rather than ALTER USER?
Likewise, I had originally used ALTER USER username QUOTA UNLIMITED (in
Oracle style) but found that it didn't meet PostgreSQL's common
syntax... which is why I changed it to ALTER USER username NOQUOTA...
does everyone agree with NOQUOTA over QUOTA UNLIMITED?
-Jonah
Stephen Frost wrote:
* Rod Taylor (pg@rbt.ca) wrote:
Since the user accessing/writing to the tablespaces would be the
postgres user I don't really think this 'solution' works in reality.I had assumed it would be a directory based quota rather than a user
based one.It's been a while since I played with quotas but I don't recall this
option being available.Group quotas should be sufficient. Create directory readable/writable to
only the pgsql user, but have the group ownership be representative of
the user in question.Rather ugly, and you'll run out of groups if you have alot of users (the
postgres user can only be in so many groups). It's a cute idea but I
really don't see it as being viable.Stephen
--
Jonah H. Harris, UNIX Administrator | phone: 505.224.4814
Albuquerque TVI | fax: 505.224.3014
525 Buena Vista SE | jharris@tvi.edu
Albuquerque, New Mexico 87106 | http://w3.tvi.edu/~jharris/
"All great truths begin as blasphemies."
-- George Bernard Shaw
Group quotas should be sufficient. Create directory readable/writable to
only the pgsql user, but have the group ownership be representative of
the user in question.Rather ugly, and you'll run out of groups if you have alot of users (the
postgres user can only be in so many groups). It's a cute idea but I
really don't see it as being viable.
The postgres user doesn't need to be in any of the groups, the group
simply exists simply to give the directory representation for a quota.
Quota is currently enforced on commit. I've considered checking during
insert/update/copy and throwing an abort but within a transaction they
may be deleting data as well.
However, even as a delete may take place before a massive insert/update,
a vacuum cannot be run within a transaction block and therefore the
commit would fail at the same point as an insert/update within the
transaction itself.
So, essentially, there is no difference at which point to check other
than to reduce the calculation overhead by placing the check at the
commit point.
What's the consensus on when to check?
Alvaro Herrera wrote:
On Thu, Jul 08, 2004 at 03:27:34PM -0600, Jonah H. Harris wrote:
Out of necessity, I've implemented user quotas in 7.4.3. What would the
process be for having this reviewed and combined? I have a patch for
7.4.3 ready, but wanted to know if you suggest that I patch the latest
cvs instead. Below if some information on the implementation.At exactly what time is the quota enforced? Does the enforcement
somehow serialize that operation?
--
Jonah H. Harris, UNIX Administrator | phone: 505.224.4814
Albuquerque TVI | fax: 505.224.3014
525 Buena Vista SE | jharris@tvi.edu
Albuquerque, New Mexico 87106 | http://w3.tvi.edu/~jharris/
"All great truths begin as blasphemies."
-- George Bernard Shaw
On Fri, 2004-07-09 at 11:47, Stephen Frost wrote:
* Klaus Naumann (kn@mgnet.de) wrote:
On Thu, 8 Jul 2004, Jonah H. Harris wrote:
3. The maximum quota size is (currently) the maximum of int4*1024 bytes.
why is this? This is very limiting ...
It's 2TB...
Okay.. that is good for a few years. What do we do after 2007?
