CODEWITHSUNDEEP
databasepostgresql
9ban
9ban

Reputation: 23

PostgreSQL create tablespace no permission

I've got a problem when I create tablespace for PostgreSQL. The following are the steps:

  1. mkdir /postgres
  2. chown postgres.postgres /postgres
  3. su - postgres
  4. psql
  5. create tablespace p1 location '/postgres'

In this step I got a error:

could not set permissions on directory "/postgres": Permission denied

The directory ownership is correct:

[root@dev ~]# ls -la /postgres  
总用量 8  
drwxr-xr-x.  2 postgres postgres 4096 12月  2 13:17 . 
dr-xr-xr-x. 28 root     root     4096 12月  3 06:57 ..

the user is postgres 

[root@dev contrib]# ps -ef|grep postgres  
postgres  1971     1  0 08:21 ?        00:00:01 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data

I'm running on CentOS.

fix: setenforce 0

Upvotes: 2

Views: 6160

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324475

At a wild guess I'd say you're on Mac OS X and your PostgreSQL is running as the user postgres_ (note the underscore), as is used by some PostgreSQL packages.

ps -ef | grep postgres or ps aux|grep postgres should show you what user the server is running as. Make sure the directory is owned by that user.

Update based on extra info in comments:

You're on CentOS, not Mac OS X. Your PostgreSQL is running as user postgres, which is the same owner as the directory. It thus seems likely that you are having issues with SELinux. If, for testing purposes only, you run:

setenforce 0

are you then able to run the CREATE TABLESPACE command? (DROP the tablespace after creating it with SELinux temporarily off; if you don't, and restart, PostgreSQL will fail to start up).

If creation fails with SELinux temporarily disabled, you must either exempt PostgreSQL from your SELinux policy, create the tablespace at a location that the SELinux policy permits, or set appropriate SELinux attributes on the tablespace directory so that PostgreSQL can manipulate it. Or you can turn SELinux off entirely, but that's not really preferable.

There might be hints in dmesg, or in CentOS's SELinux helper tool, to tell you specific SELinux booleans you can turn on or off to control this. See the help for the setsebool command, the Fedora Security Guide, the CentOS SELinux howto, etc.

Perhaps the best option is to just change the SELinux context of the file. See the documentation. You can use chcon, but then the change will be lost after a file system relabel. It's better to use semanage as discussed in the next page of the linked manual.

Upvotes: 5

Related Questions