Funky User Quota Solution under Linux (possibly other *nixs*) - Mailing list pgsql-admin

Hello all,

  I'm not a member of the mailing list, but I'm posting 'cause I saw
others with my problem: Somehow setting a quota on the size of a users
database.  Well, I came up with the following, though funky,
solution...and it works!  However, let me know if you think there may be
some security risk involved...I don't think so.

  First, the kernel must have loopback device support and I recommend
making in a loadable modules.  Then in /etc/modules.conf, set the following:

    options loop max_loop=<number>

where <number> is some number above the number of users you'll need this
for.  For example, say I'll have 10 users I need to limit, I'd want
<number> to be at least 10.  Then load the module:

      modprobe loop

  Next, I make the device nodes (in bash):

   for n in 0 1 2 3 4 5 ... <number> ; do
      mknod /dev/loop$n b 7 $n
   done

Some of these may already exist.  Now, I associate the users with loop
devices via symlinks (actually my test didn't use loop#, but actual an
actual username, i.e. loop_userbob).

  Now the fun begins.  Basically, it abuses Linux's loopback device
support.  I create a file in the user's home directory, say
$USER/pg_database, to the size I want as the limit, i.e. 5MB:

   dd if=/dev/zero of=$USER/pg_database bs=1M count=5

Next, as root, I setup the loopback device for that user:

   losetup /dev/loop_userbob $USER/pg_database

Create the filesystem:

   mke2fs /dev/loop_userbob

Now mount the device and create a DB Dir:

   mkdir -p /mnt/pg_dbs/userbob
   mount /dev/loop_userbob /mnt/pg_dbs/userbob
   mkdir /mnt/pg_dbs/userbob/data
   chown postgre_user.postgre_grp /mnt/pg_dbs/userbob/data

'su' to the postgre user and setup the DB:

   su - postgre_user
   {shutdown current DB}
   export PGDATA_USERBOB=/mnt/pg_dbs/userbob/data
   initlocation PGDATA_USERBOB
   {restart DB}

Now you can create the users DB:

   psql -d template1 ...{as usual}
   ==> create database USERBOB_DB with location 'PGDATA_USERBOB';


'userbob' can now access and work with the DB all he wants and do
whatever he wants...but because the actual database is on a loopback
device (i.e. a file), he is limited to the 5MB we setup.  Furthermore,
the DB files are owned by 'postgre_user' but the "filesystem" is owned
by 'userbob', thus in his file quota!

More Notes:
  - Since a 'username' works, we can now script the (un)mounting of the
    loopback device filesystem and not worry about 'loop2' getting freed
    up from user1, and now user2 needs to mount...but which loop device?

  - DANGER: The user can delete the file at any time possibly harming
    the server.  I don't know, I haven't tested this yet.  Either way,
    'userbob' does not have to own the file if you wish to keep a
    "database quota" separate from "file quota".

  - Disadvantage:  Since each user has their own database, if the
    database is the same for each user...the full schema must be
    duplicated (else setup some inter-database work...not sure if
    Postgre supports that).

I'm sorry the instructions are brief, but it's a lot to type.  Let me
know if you want a full example or more info.  Hope this helps some of you.

David
gershman@dagertech.net