Right now, you can't directly create a relation (table, index,
composite type) in the pg_catalog schema, but you can create a
non-relation (function, domain, etc.) in the pg_catalog schema.
Furthermore, you can create a table in some other schema and then move
it into the pg_catalog schema using ALTER TABLE .. SET SCHEMA. After
you do that, you can't move it back out again, nor can you drop it; or
at least not without setting allow_system_table_mods.
This all seems pretty wonky and inconsistent to me. It strikes me
that we ought to either (1) allow users to place SQL objects in
pg_catalog or (2) not. Having a weird special case that disallows it
only for relations, but then lets you do it anyway via the back door,
seems pretty pointless.
Tabula raza, I'd argue for getting tough on this, and error out on any
attempt to get a user-created SQL object into pg_catalog by any means,
unless allow_system_table_mods is set. However, considering that we
have two extensions whose extension install scripts do this --
adminpack and sepgsql -- and one of those (adminpack) is extremely
widely used, that seems like it might be asking for trouble. So maybe
we should just go the opposite direction and just remove the rather
toothless prohibition that currently exists. Or, as a middle way, we
could tighten up the prohibition, but also provide a GUC other than
allow_system_table_mods that can be changed via SET LOCAL by extension
scripts that need to do this. allow_system_table_mods requires a
server restart and is purposefully undocumented, so it's not a good
thing to rely on for prohibitions that people might need to work
around on a production system.
Thoughts?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company