Re: User Quota Implementation

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(at)rbt(dot)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(at)tvi(dot)edu
Albuquerque, New Mexico 87106 | http://w3.tvi.edu/~jharris/

"All great truths begin as blasphemies."
-- George Bernard Shaw