Re: Tablespace permissions issue

BTW, it occurs to me that there's a bug in the current implementation of
CREATE DATABASE when you change the database's default tablespace.
The CREATE DATABASE code assumes that it can physically copy all content
of the old-database's-default-tablespace into the
new-database's-default-tablespace, rather than keeping it in the same
tablespace as it does for all non-default tablespaces.

This fails in just one case: where a table inside the old database has
explicitly specified use of a tablespace that happens to be the same as
the old database's default tablespace. Now, when looking at the new
database's reltablespace column, it will appear that that table is in
the old database's default tablespace ... but that ain't where CREATE
DATABASE put it. Ooops.

I don't see any reasonable way for CREATE DATABASE to avoid this
problem, since it can't necessarily look inside the source database.
My thought is that the cleanest fix is to never allow reltablespace
(or nsptablespace) to explicitly specify the database's default
tablespace; that is, if you write
CREATE TABLE ... TABLESPACE x
and "x" is the database-level default, we should silently store zero
instead of x's OID into reltablespace. The table would get created
in the same place either way, but the implications for future cloning
or dump/reload of the database would be different. The table would go
to the new database default tablespace, whatever that is, instead of
staying in "x".

Thoughts?

regards, tom lane