CODEWITHSUNDEEP
postgresqlpermissionspostgresql-9.6tablespace
Darth Kangooroo
Darth Kangooroo

Reputation: 402

Postgresql: cannot create tablespace due to permissions

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 :

I am running out of ideas.

Anybody has a suggestion ?

Upvotes: 3

Views: 12834

Answers (3)

Anna Fadeeva
Anna Fadeeva

Reputation: 1

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.

Upvotes: 0

travs15
travs15

Reputation: 462

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.

  1. Right click in the directory ".../targetDirectory".
  2. Properties> Security> In Group /usernames click on "edit"
  3. Add another group or username, type "Everyone" and click "OK"
  4. 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.

Upvotes: 1

Darth Kangooroo
Darth Kangooroo

Reputation: 402

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.

Upvotes: 5

Related Questions