Alternate database locations

From: "Tauren Mills" <tauren(at)servlets(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: Alternate database locations
Date: 2001-05-31 20:30:42
Message-ID: 9f69h2$29e7$1@news.tht.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

I'm new to PostgreSQL and am trying to implement it in a shared web
application hosting environment. Ideally, I'd like each customer to have
their databases stored within their own user area. For instance, the
/home/user1/.pgsql would contain all of user1's databases.

It looks like this could be done by using an environment variable for each
customer, running initlocation for each. For instance:

PGDATA2=/home/user1/.pgsql; export PGDATA2
initlocation PGDATA2
createdb -D PGDATA2 user1_test

However, since I'll have around 50 different database locations, I'll have
to start the postmaster with PGDATA2, PGDATA3, PGDATA4, ..., PGDATA50 all
defined. This doesn't seem like a good solution.

There also appears to be a way to create databases with an absolute path,
but this is turned off by default for security reasons. I haven't tried
this yet, but it looks like I'd have to recompile with different settings
for ALLOW_ABSOLUTE_DBPATHS. This URL has more info:
http://www.postgresql.org/idocs/index.php?managing-databases.html

However, this page indicates that there are security risks in doing this.
What are those risks? If I prevent users other than the superuser
"postgres" from being able to create and drop databases, are there any
security risks?

Lastly, I've even tried creating a database normally, then moving it to the
user's area and creating a symlink to it. But this didn't seem to work.
Any ideas why it wouldn't work?

The main reason for putting the databases in each user's directory is for
billing purposes. It makes it easier to determine how much disk space a
customer is using. I would also consider keeping all of the databases in a
common location if there are easy ways to determine the following:

* Is there an easy way to map database directories in the file system to
database names? The directories names in /var/lib/pgsql/data/base/ are not
very helpful.

* Is there any command or tool that reports how much space a database is
taking up? Obviously du will work, but it would be nice if there was a
reporting tool that would output the database name instead of the directory
name.

Are there any other options that I'm missing? Any suggestions for a
different approach? Anyone know what other hosting providers are doing?

Thanks!
Tauren

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Andy Samuel 2001-06-01 04:55:12 Re: Database over multiple drives
Previous Message Tauren Mills 2001-05-31 20:27:56 Limiting simultaneous connections

Browse pgsql-general by date

  From Date Subject
Next Message Peter Eisentraut 2001-05-31 20:47:32 Re: Compiling to RPM setup/filesystem layout
Previous Message Lamar Owen 2001-05-31 20:26:36 Re: Compiling to RPM setup/filesystem layout