7

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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