33

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.

3 Answers 3

46

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

1
  • 6
    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.
    – ingernet
    Commented Sep 30, 2021 at 16:38
1

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;
-4

grant all on tablespace <tablespace_name> to ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.