Re: Fixing invalid owners on pg_toast tables in 8.3.5 - Mailing list pgsql-general

From Cott Lang
Subject Re: Fixing invalid owners on pg_toast tables in 8.3.5
Date
Msg-id 1235165098.3537.74.camel@duo.internetstaff.com
Whole thread Raw
In response to Re: Fixing invalid owners on pg_toast tables in 8.3.5  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Fixing invalid owners on pg_toast tables in 8.3.5
List pgsql-general
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


pgsql-general by date:

Previous
From: Torsten Bronger
Date:
Subject: Re: Getting time-dependent load statistics
Next
From: Tom Lane
Date:
Subject: Re: Fixing invalid owners on pg_toast tables in 8.3.5