postgresqltablespacechown

Creating a tablespace in postgresql

I'm trying to create a tablespace in postgres, but I'm getting ownership problems. The command I'm using is:

CREATE TABLESPACE magdat OWNER maggie LOCATION '/home/john/BSTablespace'

I get the error:

ERROR:  could not set permissions on directory "/home/john/BSTablespace": Operation not permitted

The folder belongs to postgres:postgres, I've tried changing it to maggie, but if I go :

chown maggie:postgres /home/john/BSTablespace

I get:

chown: invalid user: `maggie:postgres'

How come the user does not exist? If I list the users inside of postgres it does come up. Any ideas what I could be doing wrong?

Solution

  • I would hazard a guess that the problem lies in the permissions of the parent directory "/home/john". Your home directory is probably setup so that only your user has access (i.e chmod 700) to it (it's a good thing for your home directory to be chmod 700, don't change it).

    Doing something like:

    mkdir /BSTablespace
chown postgres:postgres /BSTablespace

    and then

    CREATE TABLESPACE magdat OWNER maggie LOCATION '/BSTablespace';

    should work fine.

    Regarding the user maggie: database users are not the same as OS users. That isn't to say that you couldn't have a user in both places named maggie-- but you would need to create the user in both the database and the OS for that to happen.