Обсуждение: Alternate database locations

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

I'm not sure how this message got posted again.  I think it is because I
posted it to the newsgroup first but it was rejected since I was not a
member of the list.  Now that I'm a member, I'm assuming it went through.
However, I'd already sent it directly to the list as an email after joining.

Sorry for the noise.
Tauren

"Tauren Mills" <tauren@servlets.net> writes:
> I'm not sure how this message got posted again.  I think it is because I
> posted it to the newsgroup first but it was rejected since I was not a
> member of the list.  Now that I'm a member, I'm assuming it went through.

If you get a response about "you're not a list member", that actually
means that your message is sitting in Marc's queue to approve for
posting.  Seems like he sifts through the queue every couple or three
days.  So, if you in the meantime subscribe and repost your message,
we'll see it again when Marc approves the original.  (Unless he happens
to notice it's a duplicate, which more often than not he doesn't.)

Fear not, we're used to this.  It beats having the lists inundated by
spam, which is what would happen if Marc weren't filtering nonmember
postings :-(

            regards, tom lane