Обсуждение: BUG #16025: pg_default tablespace is removable but not creatable

The following bug has been logged on the website:

Bug reference:      16025
Logged by:          Matthew Kingsbury
Email address:      kingsbury.matthew@gmail.com
PostgreSQL version: 11.5
Operating system:   Ubuntu 18.04 LTS
Description:

I was able to rename pg_default to something else, accidentally. However,
there appears to be no mechanism to replace pg_default nor can one replace
the pg_default after performing this action. E.g. if one runs the command:

ALTER TABLESPACE pg_default
  RENAME TO not_pg_default;

ALTER TABLESPACE not_pg_default
  RENAME TO pg_default;

One encounters this error:
ERROR: unacceptable tablespace name "pg_default"
DETAIL: The prefix "pg_" is reserved for system tablespaces.

Leaving one without the pg_default tablespace.

PG Bug reporting form <noreply@postgresql.org> writes:
> I was able to rename pg_default to something else, accidentally.

If you're superuser, yeah.  Don't do that.

> However,
> there appears to be no mechanism to replace pg_default nor can one replace
> the pg_default after performing this action.

[ shrug... ]  There are innumerable unrecoverable things you can do
as superuser.  "DELETE FROM pg_database" is a fun example (*don't*
try this at work).  This isn't a bug, any more than it's a bug that
root can destroy the whole filesystem.  Cautious DBAs don't do more
than they absolutely must as superuser.  We've made efforts lately
to reduce the number of tasks that require superuser as opposed to
some more-restricted role, eg you can do a lot from a role with
CREATEUSER privilege.

FWIW, you can in fact get out of the described situation:

regression=# ALTER TABLESPACE pg_default RENAME TO not_pg_default;
ALTER TABLESPACE
regression=# table pg_tablespace;
 oid  |    spcname     | spcowner | spcacl | spcoptions
------+----------------+----------+--------+------------
 1664 | pg_global      |       10 |        |
 1663 | not_pg_default |       10 |        |
(2 rows)

regression=# update pg_tablespace set spcname = 'pg_default' where spcname = 'not_pg_default';
UPDATE 1
regression=# table pg_tablespace;
 oid  |  spcname   | spcowner | spcacl | spcoptions
------+------------+----------+--------+------------
 1664 | pg_global  |       10 |        |
 1663 | pg_default |       10 |        |
(2 rows)

Nonetheless, the bottom line is that PG superusers do not have
training wheels.

            regards, tom lane