Creating postgres tablespace permission denied even though owned by postgres

Question

I have been trying to create a tablespace in postgresql 9.5 and I have been consistently getting issues with permission denied. I have finally decided I'm going to create a directory in / just to see if I could get anything to work but with no luck so far.

For reference:

[root@server ~]# cd /
[root@server /]# mkdir test
[root@server /]# chown postgres:postgres test
[root@server /]# su postgres
bash-4.3$ psql
psql (9.5.5)
Type "help" for help.

postgres=# CREATE TABLESPACE test LOCATION '/test';
ERROR:  could not set permissions on directory "/test": Permission denied
postgres=# \q
bash-4.3$ cd test
bash-4.3$ chmod 777 .
bash-4.3$ ls -alh
total 8.0K
drwsrwsrwx.  2 postgres postgres 4.0K Jan 27 20:15 .
dr-xr-xr-x. 19 root     root     4.0K Jan 27 20:15 ..
bash-4.3$ pwd -P
/test
bash-4.3$ psql
psql (9.5.5)
Type "help" for help.

postgres=# CREATE TABLESPACE test LOCATION '/test';
ERROR:  could not set permissions on directory "/test": Permission denied

I've even given the directory 777 and it is quite obviously owned by postgres. At this point I'm not sure what to do to get this to work. I'm running fedora-25. I would ideally like to not put this in / and actually put it on a separate disk but I can't even get this basic case to work. Am I missing something obvious here?

Answer 1

You have SELinux enabled, and have missed that it is blocking this.

You can see the log entries in the audit log /var/log/audit/audit.log.

The easiest way to resolve the problem is to use the default data directory location, /var/lib/pgsql and to not try to override it. Rather, mount your storage at this location.

Answer 2

I was faced similar problem at Fedora. Even /var/log/audit/audit.log did not provided any clue the reason is SElinux.

Tablespace dir is ok.

[me@my /]$ ls -alr /psql
drwx------.  2 postgres postgres 4096 14.Mar 22.56 a
dr-xr-xr-x. 19 root     root     4096 14.Mar 15.13 ..
drwxr-xr-x.  3 postgres postgres 4096 14.Mar 15.21 .

But CREATE TABLESPACE failed.

[me@my /]# psql -U postgres 
postgres=# create tablespace test_index location '/psql/a';
ERROR:  could not set permissions on directory "/psql/a": Permission denied
postgres=# \quit

Stracing confirmed an issue:

[me@my /]$ ps ax |grep postgres
18719 ?        S      0:00 /usr/bin/postgres -D /var/lib/pgsql/data
18720 ?        Ss     0:00 postgres: logger process
18722 ?        Ss     0:00 postgres: checkpointer process
18723 ?        Ss     0:00 postgres: writer process
18724 ?        Ss     0:00 postgres: wal writer process
18725 ?        Ss     0:00 postgres: autovacuum launcher process
18726 ?        Ss     0:00 postgres: stats collector process
20446 ?        Ss     0:00 postgres: postgres postgres [local] idle

strace failed:

[me@my /]$ sudo strace -p 20446
strace: Process 20446 attached
epoll_pwait(3, [{EPOLLIN, {u32=2672404064, u64=94302974355040}}], 1, -1, NULL, 8) = 1
recvfrom(11, "Q\0\0\0005create tablespace test_inde"..., 8192, 0, NULL, NULL) = 54
lseek(5, 0, SEEK_END)                   = 8192
chmod("/psql/a", 0700)                  = -1 EACCES (Permission denied)

Switching off SElinux finally solved the problem

[me@my /]# setenforce 0
[me@my /]# sestatus
SELinux status:                 enabled
SELinuxfs mount:                /sys/fs/selinux
SELinux root directory:         /etc/selinux
Loaded policy name:             targeted
Current mode:                   permissive
Mode from config file:          enforcing
Policy MLS status:              enabled
Policy deny_unknown status:     allowed
Memory protection checking:     actual (secure)
Max kernel policy version:      31
[me@my /]# psql -U postgres 
psql (9.6.7)

postgres=# create tablespace test_index location '/psql/a';
CREATE TABLESPACE

strace success:

epoll_pwait(3, [{EPOLLIN, {u32=3479758432, u64=94226472298080}}], 1, -1, NULL, 8) = 1
recvfrom(11, "Q\0\0\0005create tablespace test_inde"..., 8192, 0, NULL, NULL) = 54
lseek(5, 0, SEEK_END)                   = 8192
chmod("/psql/a", 0700)                  = 0
mkdir("/psql/a/PG_9.6_201608131", 0700) = 0
symlink("/psql/a", "pg_tblspc/16454")   = 0

Note: adding postgres user to root group did not help.