Tom,
Thanks for the inspiration - I've fixed them manually.
I spent a few minutes trying to recreate the obvious test case, and it
all works as designed. I reviewed our logs from the user removal
yesterday, and the tables linked to these toast tables did not have
ownership changed yesterday, so I did more digging.
I found a handful of other pg_types with an unusual owner and in every
case, the toast type is owned by the user that created the database via
full pg_restore some months ago.
pg_restore should have created the table as the user running it, and
immediately done an ALTER TABLE .. OWNER TO. So does the pg_toast type
take ownership from the owner of the originating table, or the user id
inserting data that forces a toast table creation ?
Either way, it's inconsistent - it's only a very small percentage of
tables that would have had toast tables created at restore time that are
affected.
Seems like two possible issues -
1. Postgres isn't checking type owners before allowing a user drop.
2. Toast type ownership doesn't always change on table ownership change.
I can recreate #1 by manually updating pg_type and dropping the user,
but I don't know if that's a valid test. If #2 should never happen, I
can see why #1 wouldn't be much of an issue.
I can't recreate #2.
On Fri, 2009-02-20 at 14:32 -0500, Tom Lane wrote:
> Cott Lang <cott@internetstaff.com> writes:
> > The owner of the actual table and index is correct, only the type has an
> > invalid owner. I have thus far avoided the temptation to try a manual
> > update...
>
> That's probably your best bet.
>
> > Since Postgres now prevents you from dropping users owning objects, is
> > this a bug, or does it fall into a gray area?
>
> Can you show us how to reproduce it? We've seen occasional reports of
> similar things but no one ever managed to produce a test case.
>
> regards, tom lane