Thread: Fixing invalid owners on pg_toast tables in 8.3.5

Fixing invalid owners on pg_toast tables in 8.3.5

From
Cott Lang
Date:
We're running 8.3.5 on RHEL4 x86_64.

We removed a user yesterday and were greeted with warnings from pg_dump
this morning. :)

pg_dump: WARNING: owner of data type "pg_toast_80075" appears to be
invalid

The usual archives and google searches produced mainly 8.0 and earlier
incidents and suggested resolving this by re-creating a user with that
sysid.  Since you can no longer specify a SYSID when creating a user
(despite what \h in psql says), we gave ALTER type/table a shot with no
luck.

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...

Is there a recommended procedure for resolving this safely?

Since Postgres now prevents you from dropping users owning objects, is
this a bug, or does it fall into a gray area?

Thanks!




Re: Fixing invalid owners on pg_toast tables in 8.3.5

From
"Joshua D. Drake"
Date:
On Fri, 2009-02-20 at 12:01 -0700, Cott Lang wrote:
> We're running 8.3.5 on RHEL4 x86_64.
>
> We removed a user yesterday and were greeted with warnings from pg_dump
> this morning. :)
>
> pg_dump: WARNING: owner of data type "pg_toast_80075" appears to be
> invalid
>
> The usual archives and google searches produced mainly 8.0 and earlier
> incidents and suggested resolving this by re-creating a user with that
> sysid.  Since you can no longer specify a SYSID when creating a user
> (despite what \h in psql says), we gave ALTER type/table a shot with no
> luck.
>

Something isn't right:

postgres=# create user foobar superuser;
CREATE ROLE
postgres=# set role foobar;
SET
postgres=# create type typetext AS (bar text);
CREATE TYPE
postgres=# select current_user;
 current_user
--------------
 foobar
(1 row)

postgres=# set role postgres;
SET
postgres=# drop type typetest;
ERROR:  type "typetest" does not exist
postgres=# drop user foobar;
ERROR:  role "foobar" cannot be dropped because some objects depend on
it
DETAIL:  owner of type typetext


> 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...
>
> Is there a recommended procedure for resolving this safely?
>

You can use alter type to change the owner of the type to a valid user
but see above. Something is wrong.

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


Re: Fixing invalid owners on pg_toast tables in 8.3.5

From
Tom Lane
Date:
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

Re: Fixing invalid owners on pg_toast tables in 8.3.5

From
Cott Lang
Date:
On Fri, 2009-02-20 at 11:25 -0800, Joshua D. Drake wrote:
> >
>
> You can use alter type to change the owner of the type to a valid user
> but see above. Something is wrong.

That's what I thought too, but we tried that first with these results:

# alter type pg_toast.pg_toast_80075 OWNER TO postgres;
ERROR: pg_toast.pg_toast_80075 is a table's row type
HINT: Use ALTER TABLE instead.

# alter table pg_toast.pg_toast_80075 OWNER TO postgres;
ERROR: "pg_toast_80075" is not a table, view, or sequence

... that's when I decided this was post-worthy. :)






Re: Fixing invalid owners on pg_toast tables in 8.3.5

From
Cott Lang
Date:
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


Re: Fixing invalid owners on pg_toast tables in 8.3.5

From
Tom Lane
Date:
Cott Lang <cott@internetstaff.com> writes:
> 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.

The toast table would be created immediately.  ALTER TABLE OWNER is
supposed to update ownership of any attached toast table, too, and that
should propagate to the pg_type row as well.  There isn't supposed to be
any way for a toast table to have different ownership from its parent,
let alone a composite-type pg_type row have different ownership from the
associated pg_class row.

We have seen a small number of reports that sometimes toast tables fail
to track parent-table updates, fail to get dropped when the parent is,
etc.  Nobody knows how to reproduce that though :-(.  I was hoping you
might find some tidbit that would provide the missing link, but no luck
yet.

Can you see any pattern or common characteristic to the tables whose
toast pg_type rows failed to change owner?  I'm not sure what to look
for exactly, but similarities in the column contents might be a
possibility.  Also, can you tell if the problem tables were adjacent
in the dump that was restored?

            regards, tom lane

Re: Fixing invalid owners on pg_toast tables in 8.3.5

From
Cott Lang
Date:
On Fri, 2009-02-20 at 16:35 -0500, Tom Lane wrote:

> Can you see any pattern or common characteristic to the tables whose
> toast pg_type rows failed to change owner?  I'm not sure what to look
> for exactly, but similarities in the column contents might be a
> possibility.  Also, can you tell if the problem tables were adjacent
> in the dump that was restored?


I fiddled around a while, found the problem, and I can repeat it at
will. It's actually related to type changes we've made over the months
since the restore.

Scenario:

1. Create a table as user A.

create table toaster (
  bread varchar(8000)
);

2. Check typowner of toast type

# select typowner from pg_type join pg_class on (typname=relname) where
pg_class.oid in (select reltoastrelid from pg_class where
relname='toaster');
 typowner
----------
    16388

3. Alter the field type

alter table toaster ALTER bread type varchar(9000);

4. Check the typowner of toast type

# select typowner from pg_type join pg_class on (typname=relname) where
pg_class.oid in (select reltoastrelid from pg_class where
relname='toaster');
 typowner
----------
  3555301

The table and toast table owners are not affected:

# select relowner from pg_class where relname='toaster';
 relowner
----------
    16388

# select relowner from pg_class where oid in (select reltoastrelid from
pg_class where relname='toaster');
 relowner
----------
    16388


Hope this helps.

Cott





Re: Fixing invalid owners on pg_toast tables in 8.3.5

From
Tom Lane
Date:
Cott Lang <cott@internetstaff.com> writes:
> I fiddled around a while, found the problem, and I can repeat it at
> will. It's actually related to type changes we've made over the months
> since the restore.

[ scratches head... ]  That example fails to misbehave for me in 8.3.6,
and I'm pretty certain there have been no relevant bugfixes since 8.3.5.
Can anyone else duplicate a change of toast table owner?

Are you by any chance using a nondefault block_size?  It strikes me that
at block_size 32K, the example would involve enlarging the varchar's
width across the boundary where a toast table would be created.  However
that still doesn't explain the results, since in that case the first
"select typowner" shouldn't have found any row.

Are both instances of the select finding the same pg_type row?  You've
phrased the select to hide all the evidence, but what we'd need to
figure out is whether reltoastrelid changed or the pg_type row itself
got updated.

            regards, tom lane

Re: Fixing invalid owners on pg_toast tables in 8.3.5

From
Tom Lane
Date:
I wrote:
> [ scratches head... ]  That example fails to misbehave for me in 8.3.6,

Oh, wait, you omitted a step from the example: the ALTER has to be done
as somebody other than the table owner (eg a superuser, else you'd not
have the needed permissions).

regression=# create user a;
CREATE ROLE
regression=# \c - a
psql (8.4devel)
You are now connected to database "regression" as user "a".
regression=> create table toaster (
  bread varchar(7000));
CREATE TABLE
regression=> select typowner from pg_type join pg_class on (typname=relname) where
pg_class.oid in (select reltoastrelid from pg_class where
relname='toaster');
 typowner
----------
    63977
(1 row)

regression=> \c - postgres
psql (8.4devel)
You are now connected to database "regression" as user "postgres".
regression=# alter table toaster ALTER bread type varchar(9000);
ALTER TABLE
regression=# select typowner from pg_type join pg_class on (typname=relname) where
pg_class.oid in (select reltoastrelid from pg_class where
relname='toaster');
 typowner
----------
       10
(1 row)

Yeah, that's a bug.  It probably hasn't got anything to do with the
other toast-table weirdnesses we've heard about, but it's a bug.

            regards, tom lane