Thread: Alternate database locations
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
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
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
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
"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