Funky User Quota Solution under Linux (possibly other *nixs*)

From: "David A(dot) Gershman" <gershman(at)dagertech(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: Funky User Quota Solution under Linux (possibly other *nixs*)
Date: 2004-03-17 05:10:07
Message-ID: 20040317051007.B873E320001@mail.dagertech.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)dagertech(dot)net

Browse pgsql-admin by date

  From Date Subject
Next Message karthikeyan 2004-03-17 11:32:13 unsubscribe
Previous Message Tom Lane 2004-03-16 22:44:58 Re: nodeRead: did not find '}' at end of plan node