Обсуждение: WARNING: owner of type appears to be invalid?

Поиск
Список
Период
Сортировка

WARNING: owner of type appears to be invalid?

От
"Marc Sherman"
Дата:
I recently installed postgresql 7.1.1, and now when I run pg_dumpall
(which I do in a nightly script for backups), I get the following
messages, duplicated for each database in the system:

WARNING: owner of type 'pg_inherits' appears to be invalid
WARNING: owner of type 'pg_index' appears to be invalid
WARNING: owner of type 'pg_statistic' appears to be invalid
WARNING: owner of type 'pg_operator' appears to be invalid
WARNING: owner of type 'pg_opclass' appears to be invalid
WARNING: owner of type 'pg_am' appears to be invalid
WARNING: owner of type 'pg_amop' appears to be invalid
WARNING: owner of type 'pg_amproc' appears to be invalid
WARNING: owner of type 'pg_language' appears to be invalid
WARNING: owner of type 'pg_largeobject' appears to be invalid
WARNING: owner of type 'pg_aggregate' appears to be invalid
WARNING: owner of type 'pg_ipl' appears to be invalid
WARNING: owner of type 'pg_inheritproc' appears to be invalid
WARNING: owner of type 'pg_rewrite' appears to be invalid
WARNING: owner of type 'pg_listener' appears to be invalid
WARNING: owner of type 'pg_description' appears to be invalid

Does anyone know what's causing this, and how I can fix it?

- Marc


Re: WARNING: owner of type appears to be invalid?

От
Tom Lane
Дата:
"Marc Sherman" <msherman@projectile.ca> writes:
> I recently installed postgresql 7.1.1, and now when I run pg_dumpall
> (which I do in a nightly script for backups), I get the following
> messages, duplicated for each database in the system:

> WARNING: owner of type 'pg_inherits' appears to be invalid
> WARNING: owner of type 'pg_index' appears to be invalid

Sounds like you have no pg_shadow entry for the postgres user.

            regards, tom lane

RE: WARNING: owner of type appears to be invalid?

От
"Marc Sherman"
Дата:
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>
> "Marc Sherman" <msherman@projectile.ca> writes:
> > I recently installed postgresql 7.1.1, and now when I run pg_dumpall
> > (which I do in a nightly script for backups), I get the following
> > messages, duplicated for each database in the system:
>
> > WARNING: owner of type 'pg_inherits' appears to be invalid
> > WARNING: owner of type 'pg_index' appears to be invalid
>
> Sounds like you have no pg_shadow entry for the postgres user.

No, that's not it:

template1=# select * from pg_shadow;
 usename  | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
passwd | valuntil
----------+----------+-------------+----------+----------+-----------+------
--+----------
 postgres |       31 | t           | t        | t        | t         |
|
 msherman |     1000 | t           | f        | f        | f         |
|
 www-data |       33 | f           | f        | f        | f         |
|
(3 rows)

This is strange, though -- the problem types have typowner set
to 103 in pg_type, which is clearly invalid.  103 happens to be
the Linux user ID (in passwd) for the postgres user.  Strange.

I suspect this may be a problem with the Debian install scripts;
Debian is forcing postgres' user id to be 31 in the db, since it
used to use a reserved user id for postgres in the os.

Is it enough for me to log on to each of my databases as user
postgres, and execute the query "update pg_type set typowner=31
where typowner=103;" to fix this problem?  Are there any other
hidden databases I should fix besides template1?

Thanks for pointing me in the right direction, Tom.

- Marc


RE: WARNING: owner of type appears to be invalid?

От
"Marc Sherman"
Дата:
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>
> > Is it enough for me to log on to each of my databases as user
> > postgres, and execute the query "update pg_type set typowner=31
> > where typowner=103;" to fix this problem?  Are there any other
> > hidden databases I should fix besides template1?
>
> Aside from pg_type, you probably have bogus values in the owner columns
> for pg_class, pg_function, pg_operator, etc etc.  It'd be a lot easier
> to create another user with sysid = 103 ...

Yup, you were right.  In fact, about half of the the system tables
are owned by 103 (the other half are owned by 31):

template1=> \dS
             List of relations
      Name      |  Type   |     Owner
----------------+---------+----------------
 pg_aggregate   | table   | postgres-badid
 pg_am          | table   | postgres-badid
 pg_amop        | table   | postgres-badid
 pg_amproc      | table   | postgres-badid
 pg_attrdef     | table   | postgres
 pg_attribute   | table   | postgres
 pg_class       | table   | postgres
 pg_database    | table   | postgres
 pg_description | table   | postgres-badid
 pg_group       | table   | postgres
 pg_index       | table   | postgres-badid
 pg_indexes     | view    | postgres
 pg_inheritproc | table   | postgres-badid
 pg_inherits    | table   | postgres-badid
 pg_ipl         | table   | postgres-badid
 pg_language    | table   | postgres-badid
 pg_largeobject | table   | postgres-badid
 pg_listener    | table   | postgres-badid
 pg_log         | special | postgres
 pg_opclass     | table   | postgres-badid
 pg_operator    | table   | postgres-badid
 pg_proc        | table   | postgres
 pg_relcheck    | table   | postgres
 pg_rewrite     | table   | postgres-badid
 pg_rules       | view    | postgres
 pg_shadow      | table   | postgres
 pg_statistic   | table   | postgres-badid
 pg_tables      | view    | postgres
 pg_trigger     | table   | postgres
 pg_type        | table   | postgres
 pg_user        | view    | postgres
 pg_variable    | special | postgres
 pg_views       | view    | postgres
 pg_xactlock    | special | postgres
(34 rows)

Thanks for the help, Tom.

- Marc


Re: WARNING: owner of type appears to be invalid?

От
Tom Lane
Дата:
"Marc Sherman" <msherman@projectile.ca> writes:
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>> Sounds like you have no pg_shadow entry for the postgres user.

> No, that's not it:

> template1=# select * from pg_shadow;
>  usename  | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
> passwd | valuntil
> ----------+----------+-------------+----------+----------+-----------+------
> --+----------
>  postgres |       31 | t           | t        | t        | t         |
> |
>  msherman |     1000 | t           | f        | f        | f         |
> |
>  www-data |       33 | f           | f        | f        | f         |
> |
> (3 rows)

> This is strange, though -- the problem types have typowner set
> to 103 in pg_type, which is clearly invalid.  103 happens to be
> the Linux user ID (in passwd) for the postgres user.  Strange.

> I suspect this may be a problem with the Debian install scripts;
> Debian is forcing postgres' user id to be 31 in the db, since it
> used to use a reserved user id for postgres in the os.

Either that or a bug in initdb: it *should* substitute the same ID into
postgres' pg_shadow entry as it does into the owner columns of the
template1 system catalogs.  Maybe it's getting confused.  Oliver,
any thoughts?

> Is it enough for me to log on to each of my databases as user
> postgres, and execute the query "update pg_type set typowner=31
> where typowner=103;" to fix this problem?  Are there any other
> hidden databases I should fix besides template1?

Aside from pg_type, you probably have bogus values in the owner columns
for pg_class, pg_function, pg_operator, etc etc.  It'd be a lot easier
to create another user with sysid = 103 ...

            regards, tom lane

Re: WARNING: owner of type appears to be invalid?

От
"Oliver Elphick"
Дата:
Tom Lane wrote:
  >"Marc Sherman" <msherman@projectile.ca> writes:
  >> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
  >>> Sounds like you have no pg_shadow entry for the postgres user.
  >
  >> No, that's not it:
 ...
  >> This is strange, though -- the problem types have typowner set
  >> to 103 in pg_type, which is clearly invalid.  103 happens to be
  >> the Linux user ID (in passwd) for the postgres user.  Strange.
  >
  >> I suspect this may be a problem with the Debian install scripts;
  >> Debian is forcing postgres' user id to be 31 in the db, since it
  >> used to use a reserved user id for postgres in the os.

Yes: this is a hangover from the old system which I can remove, since there
is no longer any point in it.

  >Either that or a bug in initdb: it *should* substitute the same ID into
  >postgres' pg_shadow entry as it does into the owner columns of the
  >template1 system catalogs.  Maybe it's getting confused.  Oliver,
  >any thoughts?

I can't see any bug in the initdb script.  It looks straightforward.

Did the user perhaps delete and recreate the postgres user at some stage?
Then the hard-coded sysid in the installation would orphan user-created
functions when the database was upgraded.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "But where shall wisdom be found? And where is the
      place of understanding? It cannot be gotten for gold,
      neither shall silver be weighed for the price thereof.
      Whence then cometh wisdom? and where is the place of
      understanding? ...Behold the fear of the Lord, that is
      wisdom; and to depart from evil is understanding."
                                         Job 12,15,20,28



RE: WARNING: owner of type appears to be invalid?

От
"Marc Sherman"
Дата:
From: Oliver Elphick [mailto:olly@lfix.co.uk]
>
> I can't see any bug in the initdb script.  It looks straightforward.
>
> Did the user perhaps delete and recreate the postgres user at some stage?
> Then the hard-coded sysid in the installation would orphan user-created
> functions when the database was upgraded.

No, that never happened.  Also, there are no user-created functions
(or types) in the database to begin with, just tables, indexes, and
sequences.

This is a brand new installation; I had 6.5 (potato's version)
installed previously, but I did a pg_dumpall and purged the old
install before installing 7.1.1-3 and importing the dumped data
using psql.

If you'd like to see any other queries on my template1 database,
to see if you can figure out where the line is drawn between user
ids 103 and 31, I'd be happy to oblige.

One thing to note, I did have to manually execute the adduser call
from the install script, before I could succesfully install 7.1.1-3,
due to the shell=/bin/false bug I reported last week.  Could that be
part of the problem?

Should we move this thread to BTS?

- Marc


Re: WARNING: owner of type appears to be invalid?

От
Tom Lane
Дата:
"Marc Sherman" <msherman@projectile.ca> writes:
> Yup, you were right.  In fact, about half of the the system tables
> are owned by 103 (the other half are owned by 31):

Now that's *really* odd.  I wonder how things got that way?

            regards, tom lane