Postgresql: cannot create tablespace due to permissions

Question

I know there has been a dozen times a similar question, but I could not find an answer that worked for me.

I have a Debian 9 machine, fresh from install on which I have installed a Postgresql 9.6 server. I am trying to create a new tablespace but it fails. The command :

CREATE TABLESPACE newTableSPace LOCATION '/DATA/PostgreSQL/';

Returns the following error :

ERROR: could not set permissions on directory "/DATA/PostgreSQL": Permission denied
SQL state: 42501

So, after a few basis searches, I have already checked the following points :

The directory /DATA/PostgreSQL exists
Its owner/group is postgres:postgres
The rights on the directory are 770
SELinux is apparently not installed (in Synaptic, libselinux1 is installed, but all selinux commands that I have tried returned me "command not found". Examples : selinuxenabled, setenforce 0).

I am running out of ideas.

Anybody has a suggestion ?

Darth Kangooroo · Accepted Answer · 2018-02-28 11:16:49Z

5

And I just found the solution !

The problem was the permissions of the user postgres not on directory /DATA/PostgreSQL/ but on the directory /DATA, on which it had no permission at all (it belonged to the current user with rights 770).

I have changed the permissions on this folder to 775. I guess another way would be to make the user postgres belong to the group that owns the DATA folder.

answered Feb 28, 2018 at 11:16

Darth Kangooroo

4122 gold badges6 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

travs15 · Accepted Answer · 2020-06-02 16:42:20Z

1

Hey I just found a possible solution, the thing is that you´ll have to give everyone access to the directory you to write in.

Right click in the directory ".../targetDirectory".
Properties> Security> In Group /usernames click on "edit"
Add another group or username, type "Everyone" and click "OK"
If you need to add more permisson to this group you check the permissions and press Ok.

You could see it in this video: https://www.youtube.com/watch?v=FQzBgEFkdes

This worked for me creating tablespaces and copy files from one directory to another one.

Hope this helps.

answered Jun 2, 2020 at 16:42

travs15

4624 silver badges5 bronze badges

Comments

LiteApplication · Accepted Answer · 2024-04-01 09:09:48Z

0

I have Linux OpenSuse 15.4, I do not have SELinux.

"SQL state: 42501" error also occurred.

The reason is that the /run/media folders, where the external hard drive is mounted by default, have limited access for the user postgres.

I changed the mount point to /usr/local/folder_for_your_external_hard.

Configured the rights to the folder for tablespace on this disk.

chown postgres:postgres your_folder_for_tablespace/
chmod 770 your_folder_for_tablespace/ 
CREATE TABLESPACE postgres_db_ts LOCATION '/usr/local/folder_for_your_external_hard/your_folder_for_tablespace';

After this, the tablespace was created.

edited Apr 1, 2024 at 9:09

LiteApplication

8617 silver badges21 bronze badges

answered Mar 25, 2024 at 9:02

Anna Fadeeva

12 bronze badges

Comments

Miguel Cortés · Accepted Answer · 2025-03-30 03:14:50Z

0

I work in Debian, and I add "postgres" user to "myuser"'s group that own the folder:

usermod -a -G myuser postgres

then I use chmod for give access to the user of the group, and create the table space folder

chmod 775 /home/myuser

su postgres

mkdir /home/myuser/ . . . /TableSpaceFolder

and finally use the command in "psql"

postgres=# create tablespace ts_name owner role_name location '/home/mysuer/ . . . /TableSapaceFolder';

CREATE TABLESPACE

Work for me.

answered Mar 30 at 3:14

Miguel Cortés

1

Collectives™ on Stack Overflow

Postgresql: cannot create tablespace due to permissions

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related