pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David A. Gershman <gershman@dagertech.net>
To: pgsql-admin@postgresql.org
Subject: Funky User Quota Solution under Linux (possibly other *nixs*)
Date: Tue, 16 Mar 2004 21:10:07 -0800 (PST)
Message-ID: <20040317051007.B873E320001@mail.dagertech.net> (raw)


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




Message-ID: <20040317051007.B873E320001@mail.dagertech.net>
Permalink:  ../20040317051007.B873E320001@mail.dagertech.net/
Also on:    postgresql.org/message-id/20040317051007.B873E320001@mail.dagertech.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: gershman@dagertech.net
  Subject: Re: Funky User Quota Solution under Linux (possibly other *nixs*)
  In-Reply-To: <20040317051007.B873E320001@mail.dagertech.net>
  References: <20040317051007.B873E320001@mail.dagertech.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox