Обсуждение: WARNING: owner of type appears to be invalid?
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
"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
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
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
"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
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
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
"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