CODEWITHSUNDEEP
databasepostgresqlpermissionsubuntu-16.04
RP-3
RP-3

Reputation: 724

Permission denied when creating tablespace in postres

I'm trying to set up a postgres tablespace on a secondary volume on a fresh installation of Ubuntu 16.04. My primary volume has only 60GB on it and I need a restore a ~55GB database. I'm using a fresh install of postgresql-9.5.

I made the user postgres a super admin so that it would be able to chmod whatever it wants (I know this is not recommended, but I'm getting a little desperate).

sudo usermod -aG sudo postgres

As user postgres, I did the following.

I've created a folder on my secondary drive (named postgres_data) and set owner to postgres.

postgres@Eli:/media/rp3/ExtraDrive1$ ls -lisa
total 28
       2  4 drwxrwxrwx+ 4 root     root      4096 Nov  9 07:46 .
  262146  4 drwxr-x---+ 3 root     root      4096 Nov  9 05:39 ..
      11 16 drwx------  2 root     root     16384 Nov  2 08:14 lost+found
10485761  4 drwxrwxr-x  3 postgres postgres  4096 Nov  9 07:46 postgres_data

I then created a nested folder (named data), also owned by postgres. I did this because I read that the user postgres must own not just the folder I want the tablespace in, but the folder containing that folder.

postgres@Eli:/media/rp3/ExtraDrive1/postgres_data$ ls -lisa
total 12
10485761 4 drwxrwxr-x  3 postgres postgres 4096 Nov  9 07:46 .
       2 4 drwxrwxrwx+ 4 root     root     4096 Nov  9 07:46 ..
10485762 4 drwxrwxr-x  2 postgres postgres 4096 Nov  9 07:46 data

I connected to postgres as user postgres and attempted to create a tablespace:

create tablespace mappify_data location '/media/rp3/ExtraDrive1/postgres_data/data';

But I got a permissions error:

create tablespace mappify_data location '/media/rp3/ExtraDrive1/postgres_data/data';

I've tried changing permissions with chmod 700, changing ownership to postgres:postgres with chown, and creating the folders as the user postgres, but all yield the same result.

I'd appreciate any advice I could get. I'm at my wits' end :(

Upvotes: 1

Views: 1628

Answers (2)

p699
p699

Reputation: 155

I was trying to set up a tablespace on a USB drive but somehow postgres was not getting the permission. I always used to get "permission denied" error message in psql. The problem was with directory permissions and traversing thru parent folders for user=postgres. Finally this answer here helped me..... permission denied in a folder for a user after chown and chmod

root@G41:~# chmod a+x /media/revoltman
root@G41:~# chmod a+x /media/revoltman/PRASHANTH2
root@G41:~# chmod a+x /media/revoltman/PRASHANTH2/dir1

testdb2=# CREATE TABLESPACE tspace2 OWNER postgres LOCATION 
'/media/revoltman/PRASHANTH2/dir1';
 CREATE TABLESPACE

Upvotes: 0

Darth Kangooroo
Darth Kangooroo

Reputation: 402

Does your linux run with SELinux ? I have read threads where the problem was SELinux.

I just had a similar problem and eventually found that another possible cause of error is of the user postgres does not have the rights to enter the directories above the one for the tablespace.

So in your case, make sure that the user postgres can browse in the hierarchy of directories /media/rp3/ExtraDrive1/postgres_data/.

Upvotes: 1

Related Questions