Re: User Quota Implementation - Mailing list pgsql-hackers

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