Why do I get a PostgreSQL permission error when specifying a tablespace in the "create database" command?

Question

When I create a database in PostgreSQL without explicitly specifying a default tablespace the database is created without issue (I'm logged in as the pgsys user):

postgres=> create database rich1;
CREATE DATABASE
postgres=> \l+
 List of databases
 Name | Owner | Encoding | Collation | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-------------------------------------+-----------+------------+--------------------------------------------
 postgres | pgsys | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7455 kB | pg_default | default administrative connection database
 rdsadmin | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin | No Access | pg_default |
 rich1 | pgsys | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7233 kB | pg_default |
 template0 | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin | 7345 kB | pg_default | unmodifiable empty database
 : rdsadmin=CTc/rdsadmin
 template1 | pgsys | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/pgsys | 7345 kB | pg_default | default template for new databases
 : pgsys=CTc/pgsys
(5 rows)

As you can see, the database is put into the pg_default tablespace, but if I specify the default tablespace in the tablespace clause (also still logged in as pgsys) I get a permission error:

postgres=> create database rich2 tablespace pg_default;
ERROR: permission denied for tablespace pg_default

Here's the permissions for that user:

postgres=> \du pgsys
 List of roles
 Role name | Attributes | Member of
-----------+-------------+-----------------
 pgsys | Create role | {rds_superuser}
 : Create DB

This is PostgreSQL error, but I should mention that this an AWS Aurora instance in case that makes a difference.

Answer 1

Here's the trick, at least with pgAdmin v4: Leave the tablespace blank. It will default to "pg_default" when creating the database.

• ingernet: With the DBeaver client, leaving it blank wasn't an option (the control is a drop-down menu), so I just chose "Default" as the tablespace value and that worked for me.

Answer 2

I've resolved this issue by making the username that is getting the error owner of the pg_default tablespace. The other method of not including the tablespace in the CREATE works as well, but this is a solution for others where taking the tablespace out of the CREATE is not possible.

My environment this works in where the issue is face is an AWS RDS instance for clarification, where rdsadmin is the owner.

ALTER TABLESPACE pg_default
 OWNER TO myusername;

Answer 3

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.