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