Thread: Alternate database locations

Alternate database locations

From
"Tauren Mills"
Date:
I've sent this to both pgsql-general and pgsql-admin.  Please advise which
is the proper list for this type of question.

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



Re: Alternate database locations

From
Peter Eisentraut
Date:
Tauren Mills writes:

> I've sent this to both pgsql-general and pgsql-admin.  Please advise which
> is the proper list for this type of question.

-admin probably

> 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.

> 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?

No.  The risks are related to the fact that non-superusers can also be
allowed to create databases.

> 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.

It should, since that is what the "official" mechanisms do as well.

> * 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.

In 7.1 and later, there is one database per directory and the directories
are named $PGDATA/base/<oid>, where <oid> is the oid of the pg_database
entry.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Re: Alternate database locations

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
>> 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?

> No.  The risks are related to the fact that non-superusers can also be
> allowed to create databases.

If the user's DB area is in his home directory, then he can presumably
rename it, leading to nasty problems when operations like CHECKPOINT try
to write to files in it.  At the very least you'd have potential for
denial of service to all the other users.

>> 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.

> It should, since that is what the "official" mechanisms do as well.

Yes, I'd have thought that would work.  Define "didn't seem to work",
please.

            regards, tom lane

RE: Alternate database locations

From
"Tauren Mills"
Date:
Thanks for the feedback!

> >> 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?
>
> > No.  The risks are related to the fact that non-superusers can also be
> > allowed to create databases.
>
> If the user's DB area is in his home directory, then he can presumably
> rename it, leading to nasty problems when operations like CHECKPOINT try
> to write to files in it.  At the very least you'd have potential for
> denial of service to all the other users.

OK, this makes sense.  In the user's home directory, I was going to create a
directory called something like ".db".  It would be owned by postgres:root
and have 700 permissions.  Within it, would be the "base" directory as
postgres:postgres and 700.

This is very similar to the way that we deploy MySQL.  But this does still
allow the user to change the directory name.  With MySQL, it would just
cause their database to not work, it wouldn't cause problems with the
server.  It sounds like this is not a good idea with PostgreSQL.

Is there any way to keep each customer's database within their user area
without the chance of breaking the database server?

> >> 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.
>
> > It should, since that is what the "official" mechanisms do as well.
>
> Yes, I'd have thought that would work.  Define "didn't seem to work",
> please.

Based on what you said, I went back and tried it again.  It "seems to work"
correctly now.  I think I must not have had the proper permissions set the
first time I tried it.

Even though this is exactly the solution I was looking for, I'm now
reluctant to use it since a user could break things by simply changing a
directory name.  I guess I'll have to keep the databases separate from the
user's home area.

Thanks,
Tauren


Re: Alternate database locations

From
Tom Lane
Date:
"Tauren Mills" <tauren@servlets.net> writes:
> Is there any way to keep each customer's database within their user area
> without the chance of breaking the database server?

If you want completely decoupled service for each user, then give each
of them their own independent installation, running as their own process.
Anything short of that raises risks of cross-user denial of service.

            regards, tom lane