WARNING: owner of type appears to be invalid?
WARNING: owner of type appears to be invalid?
От:
"Marc Sherman" <msherman@projectile.ca>
Дата:
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 <tgl@sss.pgh.pa.us>
Дата:
"Marc Sherman" 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" <msherman@projectile.ca>
Дата:
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > "Marc Sherman" 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" <msherman@projectile.ca>
Дата:
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 <tgl@sss.pgh.pa.us>
Дата:
"Marc Sherman" 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" <olly@lfix.co.uk>
Дата:
Tom Lane wrote:
>"Marc Sherman" 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" <msherman@projectile.ca>
Дата:
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 <tgl@sss.pgh.pa.us>
Дата:
"Marc Sherman" 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