0

I am currently setting up an Ubuntu 18.04 server, where many different users are given SSH access - including access to the server's Postgres SQL and the ability to create their own Postgres databases.

What I would like to accomplish is the following. When users create their own Postgres databases, I want these new databases to be automatically physically stored in a subfolder of the user's home directory.

Notice that I am aware that there are ways to explicitely choose alternate physical drive locations for databases to be created in, other than the Postgres' default location. However, I can't seem to find a way to configure Postgres to make it enforce all the databases created by a given user to be each automatically stored in that Ubuntu user' home directory. That is, without requiring the users to chose their own home directory subfolders as the destination of the new database.

Sure, I could automatically create, for each Ubuntu user, a Postgres alternate location within the user's home directory (following the link mentioned above). But still does not enforce user-created databases to be physically stored there instead of at the standard Postgres installation location.

Any hints would be very much appreciated.

Bennton
  • 111
  • 1
    It's *probably* time to upgrade your PostgreSQL database from version 6.3. – womble Jun 18 '19 at 23:14
  • It would be interesting to expand on the benefits you expect from this, since they're very likely to be illusory. In particular, a user deleting "their" space at the FS level will corrupt the entire cluster from the POV of the server, so instead of creating a clean separation, this setup leans to the opposite. – Daniel Vérité Jun 21 '19 at 13:51

2 Answers2

2

Assuming that you already have a PostgreSQL user bob, who corresponds to the system user bob with a home directory of /home/bob, and you wish to store PostgreSQL data under /home/bob/pg, which already exists and has permissions appropriate for access by the PostgreSQL database server, the following procedure may work, if you're running a relatively recent version of PostgreSQL.

  1. Create a new tablespace for the user:

    CREATE TABLESPACE bob OWNER bob LOCATION '/home/bob/pg`;

  2. Set the default tablespace for the user:

    ALTER USER bob SET default_tablespace = bob;

However, this won't prevent bob from creating objects in another (specifically, the default) tablespace, so you'll need to REVOKE bob's privileges from the default tablespace:

REVOKE ALL ON TABLESPACE pg_default FROM bob;
womble
  • 96,255
  • 29
  • 175
  • 230
1

I think that you are going to find that users cannot simply create their own Postgress database and have it properly integrated into the server function. I believe that several permissions greater than the simple user will be required.

Furthermore, another problem you are going to run into is SELinux labeling. The new area would have to be properly labeled for postgres to access files in a user directory as well as a number of SELinux changes. I assume as a good systems type you would be running SELinux in enforcing mode.

Summary...you can do it but it will take a lot of work and analysis to ensure system security and integrity.

mdpc
  • 11,856
  • 28
  • 53
  • 67