Postgres not being able to create a tablespace

Question

I just mounted an external HD for store my new tablespace.

postgres@vostro ~ $ df -k
Filesystem     1K-blocks     Used Available Use% Mounted on
/dev/sdb1      480589520    71580 456082280   1% /mnt/DB

postgres@vostro ~ $ ls -l /mnt/DB/
total 4
drwxrwxrwx 2 postgres postgres 4096 May 25 17:02 postgres

demisc@vostro /mnt $ mount | grep -i sdb1
/dev/sdb1 on /mnt/DB type ext4 (rw,nosuid,nodev,relatime,data=ordered)

I gave all the necessary permissions so that posgres could create the new ts in /mnt/DB/postgres. Here is the user groups:

postgres@vostro ~ $ id
uid=123(postgres) gid=131(postgres) groups=131(postgres),112(ssl-cert),1000(demisc)

Happens that when running the create tablespace command I get the following:

postgres=# create tablespace ts_project location '/mnt/DB/postgres';
ERROR:  could not set permissions on directory "/mnt/DB/postgres": 
Permission denied

What I am missing?

ls -ln /mnt/DB/postgres

returns:

demisc@vostro /mnt/DB $ ls -ln /mnt/DB/postgres/
total 0

Everything looks good. There is no reason why postgres user can not create the tablespace. If i switch to postgres and try to create file or dir in the path it works fine.

Answer 1

I just found a solution. I changed the permission of /mnt/DB/postgres to 700:

postgres@vostro /mnt/DB $ ls -l
total 4
drwx------ 3 postgres postgres 4096 May 25 17:46 postgres

Now it worked the creation of the ts

postgres@vostro /mnt/DB/postgres $ ls -l
total 4
drwx------ 2 postgres postgres 4096 May 25 17:46 PG_9.5_201510051

---

_{Community Wiki answer created from a comment left by the question author. Community wiki posts have been donated to the community in hopes that others will edit them to keep them up to date, to add useful information, and generally improve their quality.}

Answer 2

While creating a tablespace, postgres internally performs a chmod 0700 call to check that directory you specified exists and has correct owner.

Apparently, in your case, postgres OS user is not able to perform this chmod call. This is also confirmed by the fact that you were able to work around the issue by setting correct permissions manually (see comments to the question itself).

Unfortunately, it is quite hard for me to tell what went wrong with permissions assignment, maybe it was due to SELinux at work.

Link to the part of source code running chmod. https://doxygen.postgresql.org/backend_2commands_2tablespace_8c.html#af268dfe1eb53bf570eceec80b993e66a

Related discussion in postgres mailing list. It was deemed not a bug back in 2008. https://www.postgresql.org/message-id/200804191238.m3JCcfcJ076060%40wwwmaster.postgresql.org

Answer 3

I'm on Xubuntu with the same issue.

Came across an answer on AskUbuntu: https://askubuntu.com/questions/464550/user-postgres-cannot-access-drive-even-after-permissions-and-group-membership-ar?rq=1

So it seems there was an access control list on one of the parent folders. So I ran the following on all parent folders:

setfacl -m u:postgres:rwx /media/