Re: BUG #16025: pg_default tablespace is removable but not creatable

PG Bug reporting form <noreply(at)postgresql(dot)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